[cspadola]

I have just finished installing Oracle 10g Enterprise Edition (10.2.0.3) and would like to run the Tablespace creation scripts that were provided with the OTM install.

I have logged into SQLPLUS as SYSTEM and am within the Script8 directory. I am able to start off with the create_gc3_tablespaces script and answer all the prompted questions but when I tell it to create the tablespaces now, it immediately gives me these errors:

================================================== ====
*
ERROR at line 1:
ORA-04063: package body "SYSTEM.CREATE_TABLESPACE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.CREATE_TABLESPACE"
ORA-06512: at line 1


BEGIN create_tablespace.set_partition_db('n'); END;

*
ERROR at line 1:
ORA-04063: package body "SYSTEM.CREATE_TABLESPACE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.CREATE_TABLESPACE"
ORA-06512: at line 1


BEGIN create_tablespace.set_dbtype('n'); END;

*
ERROR at line 1:
ORA-04063: package body "SYSTEM.CREATE_TABLESPACE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.CREATE_TABLESPACE"
ORA-06512: at line 1


BEGIN create_tablespace.set_auto_extend('y'); END;

*
ERROR at line 1:
ORA-04063: package body "SYSTEM.CREATE_TABLESPACE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.CREATE_TABLESPACE"
ORA-06512: at line 1


BEGIN create_tablespace.set_execute('y'); END;

*
ERROR at line 1:
ORA-04063: package body "SYSTEM.CREATE_TABLESPACE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.CREATE_TABLESPACE"
ORA-06512: at line 1


BEGIN create_tablespace.create_all_ts('D:\oracle\oradata ', '1024000K','2048000K'
); END;

*
ERROR at line 1:
ORA-04063: package body "SYSTEM.CREATE_TABLESPACE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.CREATE_TABLESPACE"
ORA-06512: at line 1

================================================== ====

I am definitely not a DBA and I see the different sql script files the original create_gc3_tablespaces script calls on within the same directory.

Can somebody give me some sugestions? Just to note, none of the scripts in this Script8 directory have been modified in any way.

Thanks

[acuartero]

Hi Carmelo,

I think your problem is that you need to login as user SYS not as SYSTEM.

Thanks,
Alan

[cspadola]

Thanks Alan...it has been too long since I did a full install (DB + OTM), especially one in a Windows Environment.

I logged in as SYS and it looks to be doing it now.

[cspadola]

I am able to get past the tablespace creation step now.

However, when I get to the step to create_public_synonyms, I receive the following error:

SQL> @create_public_synonyms
SELECT owner, table_owner FROM DBA_SYNONYMS WHERE SYNONYM_NAME = p_synonym_name;
*
ERROR at line 8:
ORA-06550: line 8, column 35:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 8, column 4:
PL/SQL: SQL Statement ignored

I am logged in as glogowner as per the admin guide while running this script. the previous 2 scripts in this section ran without error.

[acuartero]

Carmelo,

I think you're missing some grants to glogowner. Did you encounter any errors with create_glog_roles.sql and create_glog_users.sql?

Thanks,
Alan

[cspadola]

I re-ran all the scripts again and it seems to have worked this time. I didn't have any errors when running the create roles and users scripts however I did run into errors when I ran the create synonyms script.

I ran them all again and after the recompile, there are o invalid objects.

Thanks.

[cspadola]

I spoke too soon. I see in the logs that when I was creating the tablespaces using the create_gc3_tablespaces script, it was able to create all the tablespaces but at the end of the script, I see the following error:

BEGIN create_tablespace.create_all_ts('d:\oracle\oradata ', '1024000K','2048000K'
); END;

*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes
ORA-06512: at "SYS.CREATE_TABLESPACE", line 136
ORA-06512: at "SYS.CREATE_TABLESPACE", line 497
ORA-06512: at "SYS.CREATE_TABLESPACE", line 187
ORA-06512: at "SYS.CREATE_TABLESPACE", line 159
ORA-06512: at line 1

[acuartero]

Sounds like your database was created with a different block size. I believe the default under windows is 8K. You can do the following instead but for performance reasons it would be better to rebuild the DB with a 16k block size in the first place. I guess if its not going to be a production instance you can get away with this.

from the Admin guide... pg 3-19

This script creates LOB tablespaces with 16 KB block size. This is the recommended block size for
optimal performance. In order to create a tablespace with 16 KB block size, you should have the
following init.ora parameter set if your database standard block size is not 16 KB. Change the cache
size as needed for your database.
db_16k_cache_size = 104857600 # 100MB for 16k block buffers

[cspadola]

Thanks Alan, I think for now I will leave it as is seeing how this will not be a PROD server. Next time I re-install OTM on this sandbox server I will go for the bigger block size.

Looks like OTM is up and running now. And in case I didn't say it today, I hate installing and running OTM on Windows!

Thanks for your help.

Carmelo

[nick.polites]

You could also just put that parameter in and bounce the database. The next time you create the tablespace it should work.