Re: 10gR2 for solaris (64bit), AIX and HP released

From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
To: MGogala@xxxxxxxxxxxxxxxxxxxx
Date: Wed, 31 Aug 2005 16:06:15 +0100
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
References:
- RE: 10gR2 for solaris (64bit), AIX and HP released
  - From: Gogala, Mladen
Re: 10gR2 for solaris (64bit), AIX and HP released

Other related posts:
