SQL> CONN /@ORCL AS SYSDBA Connected. SQL> SELECT COMP_NAME,VERSION,STATUS 2 FROM DBA_REGISTRY; Oracle9i Catalog Views 9.2.0.6.0 VALID Oracle9i Packages and Types 9.2.0.6.0 VALID Oracle Workspace Manager 9.2.0.1.0 VALID JServer JAVA Virtual Machine 9.2.0.6.0 VALID Oracle XDK for Java 9.2.0.8.0 VALID Oracle9i Java Packages 9.2.0.6.0 VALID Oracle interMedia 9.2.0.6.0 VALID Spatial 9.2.0.6.0 VALID Oracle Text 9.2.0.6.0 VALID Oracle Ultra Search 9.2.0.6.0 INVALID 10 rows selected. SQL> @CREATEUSER Enter User to Create: MLADEN Enter Password: MLADEN Enter Default Tablespace: USERS Enter Temporary Tablespace: TEMP drop user MLADEN cascade * ERROR at line 1: ORA-01918: user 'MLADEN' does not exist User created. Grant succeeded. SQL> CONN MLADEN/MLADEN@ORCL Connected. SQL> CREATE TABLE T1(C1 NUMBER,C2 CLOB); Table created. SQL> ALTER TABLE T1 ADD CONSTRAINT 2 PK_T1 PRIMARY KEY(C1); Table altered. SQL> CONN /@ORCL AS SYSDBA Connected. SQL> @EXTRACT_SCHEMA Enter password for SYSTEM user: PASSWORD Enter tnsnames entry for database: ORCL Enter User name to script: MLADEN Enter Create Script name to generate: CREATE_MLADEN spool c:\scripts\utils\CREATE_MLADEN.sql col ddl_col form a80 word_wrapped exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE); exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE); exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', TRUE); exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE); exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true ); prompt 'Creating User.... ' select dbms_metadata.get_ddl('USER',u.username) ddl_col FROM DBA_users u where username = 'MLADEN'; /* Grants Come next - expect unhelpful errors */ prompt 'Creating Role Grants.... ' SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','MLADEN') FROM DUAL; prompt 'Creating System Grants.... ' SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','MLADEN') FROM DUAL; prompt 'Creating Object Grants.... ' SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','MLADEN') FROM DUAL; prompt 'Creating Tables.... ' select dbms_metadata.get_ddl('TABLE',t.table_name,'MLADEN') ddl_col FROM DBA_TABLES t WHERE OWNER = 'MLADEN'; prompt 'Creating Indexes.... ' select dbms_metadata.get_ddl('INDEX',i.index_name,'MLADEN') ddl_col FROM DBA_INDEXES i WHERE OWNER = 'MLADEN'; prompt 'Creating Sequences.... ' select dbms_metadata.get_ddl('SEQUENCE',s.sequence_name,'MLADEN') ddl_col FROM DBA_SEQUENCES s WHERE SEQUENCE_OWNER = 'M LADEN'; prompt 'Creating Views.... ' select dbms_metadata.get_ddl('VIEW',v.view_name,'MLADEN') ddl_col FROM DBA_VIEWS v WHERE OWNER = 'MLADEN'; prompt 'Creating Materialized Views.... ' select dbms_metadata.get_ddl('MATERIALIZED_VIEW',mv.mview_name,'MLADEN') ddl_col FROM DBA_MVIEWS mv WHERE OWNER = 'MLADE N'; prompt 'Creating Types.... ' select dbms_metadata.get_ddl('TYPE',o.object_name,'MLADEN') ddl_col FROM DBA_OBJECTS o WHERE OWNER = 'MLADEN' and object _type='TYPE'; prompt 'Creating Procedures.... ' select dbms_metadata.get_ddl('PROCEDURE',o.object_name,'MLADEN') ddl_col FROM DBA_OBJECTS o WHERE OWNER = 'MLADEN' and o bject_type='PROCEDURE'; prompt 'Creating Functions.... ' select dbms_metadata.get_ddl('FUNCTION',o.object_name,'MLADEN') ddl_col FROM DBA_OBJECTS o WHERE OWNER = 'MLADEN' and ob ject_type='FUNCTION'; prompt 'Creating Packages.... ' select dbms_metadata.get_ddl('PACKAGE',o.object_name,'MLADEN') ddl_col FROM DBA_OBJECTS o WHERE OWNER = 'MLADEN' and obj ect_type='PACKAGE'; prompt 'Creating Triggers.... ' select dbms_metadata.get_ddl('TRIGGER',o.object_name,'MLADEN') ddl_col FROM DBA_OBJECTS o WHERE OWNER = 'MLADEN' and obj ect_type='TRIGGER'; connect system/PASSWORD@ORCL spool off 'Creating User.... ' CREATE USER "MLADEN" IDENTIFIED BY VALUES 'E457C986DE0CEE76' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"; 'Creating Role Grants.... ' GRANT "APP_USER" TO "MLADEN"; 'Creating System Grants.... ' ERROR: ORA-31608: specified object of type SYSTEM_GRANT not found ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_METADATA", line 631 ORA-06512: at "SYS.DBMS_METADATA", line 1339 ORA-06512: at line 1 'Creating Object Grants.... ' ERROR: ORA-31608: specified object of type OBJECT_GRANT not found ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_METADATA", line 631 ORA-06512: at "SYS.DBMS_METADATA", line 1339 ORA-06512: at line 1 'Creating Tables.... ' CREATE TABLE "MLADEN"."T1" ( "C1" NUMBER, "C2" CLOB ) ; CREATE UNIQUE INDEX "MLADEN"."PK_T1" ON "MLADEN"."T1" ("C1") ; ALTER TABLE "MLADEN"."T1" ADD CONSTRAINT "PK_T1" PRIMARY KEY ("C1") ENABLE; 'Creating Indexes.... ' CREATE UNIQUE INDEX "MLADEN"."PK_T1" ON "MLADEN"."T1" ("C1") ; CREATE UNIQUE INDEX "MLADEN"."SYS_IL0001686010C00002$$" ON "MLADEN"."T1" ( ; 'Creating Sequences.... ' 'Creating Views.... ' 'Creating Materialized Views.... ' 'Creating Types.... ' 'Creating Procedures.... ' 'Creating Functions.... ' 'Creating Packages.... ' 'Creating Triggers.... ' Connected. SQL> SHOW USER USER is "SYSTEM" SQL> DROP USER MLADEN; DROP USER MLADEN * ERROR at line 1: ORA-01922: CASCADE must be specified to drop 'MLADEN' SQL> DROP USER MLADEN CASCADE; User dropped. SQL> The above shows that it apparently works for me on 9206 on windows. On 8/31/05, Gogala, Mladen <MGogala@xxxxxxxxxxxxxxxxxxxx> wrote: > > > Mark, can you do me a favor and try to extract a definition of a table with > at least one LOB column from 9.2.0.6 <http://9.2.0.6> database using DBMS_METADATA.GET_DDL? > > It doesn't work on Solaris and it doesn't work on Linux. I wonder whether > the bug was local to those two platforms or global? > > > -- > Mladen Gogala > Ext. 121 > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > Sent: Wednesday, August 31, 2005 9:07 AM > To: Oracle Discussion List > Subject: RE: 10gR2 for solaris (64bit), AIX and HP released > > > >> Special mention of my black list: > 9.2.0.6 <http://9.2.0.6> with RAC (first time I had to roleback a patchset!).<< > [rollback] > > Interesting we have found 9.2.0.6 <http://9.2.0.6> RAC on AIX to be much more stable than > 9.2.0.4 <http://9.2.0.4> or 9.2.0.5 <http://9.2.0.5>. So far (couple months) we have not ran into the > system hang and crash problems that we suffered from on the lower > versions. However, two one-off patches were required prior to going > production. The first fixed a slow long on and very slow first run of > any query problem. I cannot remember what problem the second patch > fixed but both patches were AIX specific with the second being required > only for RAC environments. > > HTH -- Mark D Powell -- -- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com