Generate DDL for Tables using PLSQL Package.

Share via:

Post Views: 0

Generate DDL for Tables.

In this article , we will see Generating DDL for tables in various methods.

Generating DDL is much easier using Plsql Package

Plsql Package :

DBMS_METADATA.GET_DDL

Method 1 :

The below script will help you to take DDL only for a single table

set pagesize 0
set long 90000
set feedback off
set echo off 
spool scott.sql
connect scott/scott
SELECT DBMS_METADATA.GET_DDL('TABLE','APP','scott') from dual;
spool off;

set pagesize 0

set long 90000

set feedback off

set echo off

spool scott.sql

connect scott/scott

SELECT DBMS_METADATA.GET_DDL('TABLE','APP','scott') from dual;

spool off;

Check Logfile at os level

[oracle@dba2 ~]$ cat SCOTT.sql 
SCOTT>>
SCOTT>>connect SCOTT/SCOTT
Connected.
SCOTT>>
SCOTT>>SELECT DBMS_METADATA.GET_DDL('TABLE','APP','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','APP','SCOTT')                                    
--------------------------------------------------------------------------------
                                                                                
  CREATE TABLE "SCOTT"."APP"                                                    
   (    "ID" VARCHAR2(10 CHAR) NOT NULL ENABLE,                               
        "LOC" VARCHAR2(20 CHAR),                                                                                       
        "TYPE" VARCHAR2(4 CHAR) NOT NULL ENABLE,                                   
        "CODE" VARCHAR2(4 CHAR) NOT NULL ENABLE,                                   
        "FLG" NUMBER(1,0),                                           
        "TAG_ID" DATE                                                           
   ) PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255                               
 NOCOMPRESS  LOGGING                                                            
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 MAXSIZE UNLIMITED                                               
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)             
  TABLESPACE "SCOTT_DATA"                                                         
  PARTITION BY RANGE ("TAG_ID") INTERVAL (NUMTODSINTERVAL(1, 'DAY'))     
 (PARTITION "SYS_P594"  VALUES LESS THAN (TO_DATE(' 2019-05-01 00:00:00', 'SYYYY
-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED        
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255                                  
 NOCOMPRESS LOGGING                                                             
  STORAGE( INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645      
  PCTINCREASE 0 MAXSIZE UNLIMITED                                               
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)             
  TABLESPACE "SCOTT_DATA" )                                                       
                                                                                
SCOTT>>
SCOTT>>spool off;exit

[oracle@dba2 ~]$ cat SCOTT.sql

SCOTT>>

SCOTT>>connect SCOTT/SCOTT

Connected.

SCOTT>>

SCOTT>>SELECT DBMS_METADATA.GET_DDL('TABLE','APP','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','APP','SCOTT')

--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."APP"

( "ID" VARCHAR2(10 CHAR) NOT NULL ENABLE,

"LOC" VARCHAR2(20 CHAR),

"TYPE" VARCHAR2(4 CHAR) NOT NULL ENABLE,

"CODE" VARCHAR2(4 CHAR) NOT NULL ENABLE,

"FLG" NUMBER(1,0),

"TAG_ID" DATE

) PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 MAXSIZE UNLIMITED

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SCOTT_DATA"

PARTITION BY RANGE ("TAG_ID") INTERVAL (NUMTODSINTERVAL(1, 'DAY'))

(PARTITION "SYS_P594" VALUES LESS THAN (TO_DATE(' 2019-05-01 00:00:00', 'SYYYY

-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED

PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE( INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 MAXSIZE UNLIMITED

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SCOTT_DATA" )

SCOTT>>

SCOTT>>spool off;exit

Method 2 :

This below example will help you to take DDL for all the tables in schema.

set pagesize 0
set long 90000
set feedback off
set echo off 
spool scott.sql
connect scott/scott
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;
spool off;

set pagesize 0

set long 90000

set feedback off

set echo off

spool scott.sql

connect scott/scott

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;

spool off;

Check logfile it will show all the tables under schema

Method 3 :

The below script will help you to take DDL for a table along with INDEXES.

set pagesize 0
set long 90000
set feedback off
set echo off 
spool scott.sql
connect scott/scott

SELECT DBMS_METADATA.GET_DDL('TABLE','APP','scott') from dual;

SELECT DBMS_METADATA.GET_DDL('INDEX','APP_INDEX','scott') from dual;

spool off;

set pagesize 0

set long 90000

set feedback off

set echo off

spool scott.sql

connect scott/scott

SELECT DBMS_METADATA.GET_DDL('TABLE','APP','scott') from dual;

SELECT DBMS_METADATA.GET_DDL('INDEX','APP_INDEX','scott') from dual;

spool off;

Here is the output in logfile

cat scott.sql
DBMS_METADATA.GET_DDL('TABLE','APP','scott')
--------------------------------------------------------------------------------

  CREATE TABLE "scott"."APP"
   (    "ID" VARCHAR2(10 CHAR) NOT NULL ENABLE,
        "LOC" VARCHAR2(20 CHAR),
        "TYPE" VARCHAR2(4 CHAR) NOT NULL ENABLE,
        "CODE" VARCHAR2(4 CHAR) NOT NULL ENABLE,
        "TAG_ID" DATE
   ) PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
 NOCOMPRESS  LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 MAXSIZE UNLIMITED
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SCOTT_DATA"
  PARTITION BY RANGE ("TAG_ID") INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
 (PARTITION "SYS_P594"  VALUES LESS THAN (TO_DATE(' 2018-08-12 00:00:00', 'SYYYY
-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE( INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 MAXSIZE UNLIMITED
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SCOTT_DATA" )


CREATE UNIQUE INDEX "SCOTT"."DEPT_IDX" ON "SCOTT"."DEPT" ("DNAME")
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"

cat scott.sql

DBMS_METADATA.GET_DDL('TABLE','APP','scott')

--------------------------------------------------------------------------------

CREATE TABLE "scott"."APP"

( "ID" VARCHAR2(10 CHAR) NOT NULL ENABLE,

"LOC" VARCHAR2(20 CHAR),

"TYPE" VARCHAR2(4 CHAR) NOT NULL ENABLE,

"CODE" VARCHAR2(4 CHAR) NOT NULL ENABLE,

"TAG_ID" DATE

) PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 MAXSIZE UNLIMITED

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SCOTT_DATA"

PARTITION BY RANGE ("TAG_ID") INTERVAL (NUMTODSINTERVAL(1, 'DAY'))

(PARTITION "SYS_P594" VALUES LESS THAN (TO_DATE(' 2018-08-12 00:00:00', 'SYYYY

-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED

PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE( INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 MAXSIZE UNLIMITED

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SCOTT_DATA" )

CREATE UNIQUE INDEX "SCOTT"."DEPT_IDX" ON "SCOTT"."DEPT" ("DNAME")

FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"

Method 4 :

The below script will help you get DDL for multiple users.

SYS>>SELECT to_char(DBMS_METADATA.GET_DDL ('TABLE', table_name, owner))
     FROM dba_tables
     WHERE owner in ('&1','&2');
Enter value for 1: U1
Enter value for 2: U2
old   3:     WHERE owner in ('&1','&2')
new   3:     WHERE owner in ('U1','U2')

TO_CHAR(DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,OWNER))
--------------------------------------------------------------------------------

  CREATE TABLE "U1"."T1"
   (    "SNO" NUMBER,
        "NAME" VARCHAR2(10)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"


  CREATE TABLE "U2"."T3"
   (    "NO" NUMBER,
        "NAME" VARCHAR2(10)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

SYS>>SELECT to_char(DBMS_METADATA.GET_DDL ('TABLE', table_name, owner))

FROM dba_tables

WHERE owner in ('&1','&2');

Enter value for 1: U1

Enter value for 2: U2

old 3: WHERE owner in ('&1','&2')

new 3: WHERE owner in ('U1','U2')

TO_CHAR(DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,OWNER))

--------------------------------------------------------------------------------

CREATE TABLE "U1"."T1"

( "SNO" NUMBER,

"NAME" VARCHAR2(10)

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS"

CREATE TABLE "U2"."T3"

( "NO" NUMBER,

"NAME" VARCHAR2(10)

) SEGMENT CREATION DEFERRED

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

TABLESPACE "USERS"

Method 5 :

The below script is used to generate DDL for a schema using DATAPUMP utility

[oracle@dba2 ~]$ expdp u1/u1  directory=dpump dumpfile=u1_metadata.dmp logfile=u1_metadata.log content=metadata_only 

Export: Release 12.1.0.2.0 - Production on Wed May 1 18:27:19 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "U1"."SYS_EXPORT_SCHEMA_01":  u1/******** directory=dpump dumpfile=u1_metadata.dmp logfile=u1_metadata.log content=metadata_only 
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Master table "U1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for U1.SYS_EXPORT_SCHEMA_01 is:
  /u01/oradata/orcl/datapump/u1_metadata.dmp
Job "U1"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed May 1 18:27:49 2019 elapsed 0 00:00:27

[oracle@dba2 ~]$ expdp u1/u1 directory=dpump dumpfile=u1_metadata.dmp logfile=u1_metadata.log content=metadata_only

Export: Release 12.1.0.2.0 - Production on Wed May 1 18:27:19 2019

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "U1"."SYS_EXPORT_SCHEMA_01": u1/******** directory=dpump dumpfile=u1_metadata.dmp logfile=u1_metadata.log content=metadata_only

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Master table "U1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for U1.SYS_EXPORT_SCHEMA_01 is:

/u01/oradata/orcl/datapump/u1_metadata.dmp

Job "U1"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed May 1 18:27:49 2019 elapsed 0 00:00:27

Now import Metadata into another user using remap_schema

[oracle@dba2 ~]$ impdp  directory=dpump  dumpfile=u1_metadata.dmp remap_schema=U1:U2
Import: Release 12.1.0.2.0 - Production on Wed May 1 18:30:10 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=dpump dumpfile=u1_metadata.dmp remap_schema=U1:U2 
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Wed May 1 18:30:23 2019 elapsed 0 00:00:09

[oracle@dba2 ~]$ impdp directory=dpump dumpfile=u1_metadata.dmp remap_schema=U1:U2

Import: Release 12.1.0.2.0 - Production on Wed May 1 18:30:10 2019

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=dpump dumpfile=u1_metadata.dmp remap_schema=U1:U2

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Wed May 1 18:30:23 2019 elapsed 0 00:00:09

Here the dump file was imported into U2 user using remap_schema option.

Method 6:

The below script will help you to take SCHEMA DDL.

SYS>>set pagesize 0 
SYS>>set long 90000
SYS>>SELECT DBMS_METADATA.GET_DDL('USER','SCOTT') FROM dual;

SYS>>set pagesize 0

SYS>>set long 90000

SYS>>SELECT DBMS_METADATA.GET_DDL('USER','SCOTT') FROM dual;

Here is the output

 CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:1DBBD27097102F140C8C8AA7CDBC8FCAEE
AE15029FBB128F013FB88361CA;H:D379B4ABED14C8BE52BCEE3FDD806E84;T:EFDD1EBB8C1D7E8C
5DA4F37360AA5B78AB641672666609DFC0583DB04B60738B9105BF5A44671B937507F0D6022F1B20
1833B900F869FB156662D86C84D83AC801CEF81F13C94F447ADC6AD71084424C;57EE09317D889A5
B'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:1DBBD27097102F140C8C8AA7CDBC8FCAEE

AE15029FBB128F013FB88361CA;H:D379B4ABED14C8BE52BCEE3FDD806E84;T:EFDD1EBB8C1D7E8C

5DA4F37360AA5B78AB641672666609DFC0583DB04B60738B9105BF5A44671B937507F0D6022F1B20

1833B900F869FB156662D86C84D83AC801CEF81F13C94F447ADC6AD71084424C;57EE09317D889A5

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

Method 7 :

The below script will help you to take Tablespace DDL.

SYS>>set pagesize 0                                                                      
SYS>>set long 90000                                                                      
SYS>>select dbms_metadata.get_ddl('TABLESPACE','SCOTT_DATA') FROM DUAL;

SYS>>set pagesize 0

SYS>>set long 90000

SYS>>select dbms_metadata.get_ddl('TABLESPACE','SCOTT_DATA') FROM DUAL;

Here is the output

CREATE TABLESPACE "SCOTT_DATA" DATAFILE
  '/u01/app/oracle/oradata/orcl/scott_data.dbf' SIZE 52428800
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO

CREATE TABLESPACE "SCOTT_DATA" DATAFILE

'/u01/app/oracle/oradata/orcl/scott_data.dbf' SIZE 52428800

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO

Method 8 :

The below script will hep to take which Roles Granted to a schema .

SYS>>set pagesize 0   
SYS>>set long 90000  
SYS>>select dbms_metadata.get_granted_ddl('ROLE_GRANT','WMS1') from dual;

SYS>>set pagesize 0

SYS>>set long 90000

SYS>>select dbms_metadata.get_granted_ddl('ROLE_GRANT','WMS1') from dual;

Here is the output

   GRANT "CONNECT" TO "WMS1"
   GRANT "RESOURCE" TO "WMS1"
   GRANT "DBA" TO "WMS1"

GRANT "CONNECT" TO "WMS1"

GRANT "RESOURCE" TO "WMS1"

GRANT "DBA" TO "WMS1"

Method 9 :

The below script will help you to take all users creation DDL

select dbms_metadata.get_ddl('USER',username) AS ddl from   dba_users;

1	select dbms_metadata.get_ddl('USER',username) AS ddl from dba_users;

Here is the few lines of output

CREATE USER "BCO" IDENTIFIED BY VALUES 'S:415947A5A26FC3D8289924861B6DD419BD7
5AEE472DB22D2787B65B3635C;H:010CC5ABDA402DC2FDC8317DB74C5D95;T:60E6CE4216138807E
BFC45193C3895ABAECFFE8FD503B85B91090BE62B91323E6C36106F3E82C5D266BA670DA8A218B5F
B1DB16D63DAF13C82CD564E7B09D03BA98E4156FD2F1B95E353FEB5E2573819;EB4DFA04181D7007
'
      DEFAULT TABLESPACE "TS_RMAN"
      TEMPORARY TABLESPACE "TEMP"


   CREATE USER "OE" IDENTIFIED BY VALUES 'S:55C5AA59F3B16AC30E6658CC9AF331731A7C
0E06FE478879A58B21CC2947;H:95C8A8735D2D02F516801B94561FD748;T:B315C6DACA84D143C3
F2244E0C19E37AE399E3C80AF8E49CDC41BAB7395DF31DC14DA8D752C5BBB3928D43B84363D0F653
B1456F7C591D1240C5B679128AB76657FE0E1F50BB23022C87D8F14F721F5D;9C30855E7E0CB02D'

      DEFAULT TABLESPACE "EXAMPLE"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK


   CREATE USER "ORACLE_OCM" IDENTIFIED BY VALUES 'S:8FB790A65325CA6858454A857A4F
E43D14E09D2FE5AFFD505315366FCCDD;H:E996810BF4EA0012FDF5CC7AB81C9146;T:DBA1465474
E3F9A86331D26371E49331E69A21B706EE37372E83136F47631967DB97C8A673FB2A6A9DFCC41ADB
8F5B97DB2F09AC5A1214974B232D8EFF6BA51D96CDC8D21F303A750400150F9A1B7781;5A2E026A9
157958C'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK


   CREATE USER "OJVMSYS" IDENTIFIED BY VALUES 'S:6FD8F70ABA8C082862C8B2094B813A5
0082956420F2D59C0D9044EBD79C4;H:22BD75631413243D4B30A89F8DE43B03;T:C2206A08A5FAE
8E1CEC368FDCFA4AF1C82B08B0F0BBEB27052245492F16EE8E6F4BD7D4E8655FD240A6085AE32EBD
BFA768378242BC79775C773CCBFC98BAB32B56B2933AA0D040C7D0765B07CBFEF91;A16E716A4E58
4324'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK


   CREATE USER "SWP" IDENTIFIED BY VALUES 'S:F0AF28CB996DFB307F6745B7361350636F3
AE36109E134E9B05D8CDEF427;H:4A6F405ED254C6171FE7CFAF3E562F18;T:1475DE719B6F6438C
B9627B291D3F07B4E33D11E5799E661B8F97F371FD68C280C7A0C534CF626E584CCB1D1629EAEA80
0D07EF77F805604C80D4C2647D6FC0F7DCFE20109A189C1449D586894AD55F2;98C61948404F1B8D
'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


   CREATE USER "SYSKM" IDENTIFIED BY VALUES 'S:D316AAB36203C7D8D5BA061E950160120
D39579B8957E7A51BF9E25430EB;H:91E5E8890F935D7A4393C974DDAB32A6;T:DB51787A7933955
F05329E2FD2EE17ECE057D30FCE143109C599E53699E7D82BFA681797601FF6194A83448866DA94D
F577FFDCB1E4DD1EC66D8648F0F353F1BC406EA848FEA1122D83691A9F3F0F4FC;F2DCB573DBFEDD
9E'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK


   CREATE USER "XS$NULL" IDENTIFIED BY VALUES 'S:0000000000000000000000000000000
00000000000000000000000000000'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK


   CREATE USER "AJAY" IDENTIFIED BY VALUES 'S:AEC7F815ECA8F290352AC9F1D2D8987D22
855012A8DDD6D847CE93C08292;H:3867F794F922E40F11C0A5AAE3459052;T:89AD7F54FB70D982
A0123FB380F6A55D697DF3D2456BCFB96D332BAB57949E2575240F08D56A835903190287CC9AB1E7
ACA88F1A854D99EBDFB63512F50CCAFE538C1CA66DA3F988BB3B764B13839329;DFBBB5C000AC286
8'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


   CREATE USER "U1" IDENTIFIED BY VALUES 'S:4F021DB16F1C03840F3F23AFDF60EBBFCFBB
3554E6B8490ED878641A1306;H:AC21ABA5169A753700F0249FF13A2213;T:3B00837765A836E272
046BF7CF4AC3A4075FC4A36A3BBC0D5E72A37146CB449CEE87DD1BBB707424DF9FF847353EF68CFA
B720DBF4A944526075487B06F6911CFFFBFF5AFFB563AC266D3267791DFC43;3E81B724A296E296'

      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


   CREATE USER "BI" IDENTIFIED BY VALUES 'S:08E87AC72254C6ED94A5BE6D4D2D323F8FEF
00E5B8B673AF6A8A5D162AE8;H:82E54F3A3653EB97043BE0F1FAE777F0;T:835355C59CF824F050
3C13DBA71F053CAFFBFE2CE4F21B8F8AE3692515A6D999C0EC39433C1111129216E714434D29F56A
F9F0CC4CC867E66D9C10C3EA5E9D55921C12C84A33F60F23354536BAA384B0;FA1D2B85B70213F3'

      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK


   CREATE USER "PM" IDENTIFIED BY VALUES 'S:BC6755A572B6024AB44DB0DE7CD45ACDD73E
2B2E730D7D2A321829A97305;H:E00851694DCBE03D0FE66EEC81484E05;T:4637F05774FEFFBA42
58319E624E12BD1DAEFFF94872F718F0B183A041A72B2EEF480CC63BF063346701A45103D3754982
6A63467467DF731C009CA9AFEA3FF38D7B2EC11A247787168B146B11F43C2E;72E382A52E89575A'

      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK


   CREATE USER "GSMCATUSER" IDENTIFIED BY VALUES 'S:56CAE182D766AA3A6CB3A3B5E0B0
F61D1A16ACEAD1A8566DD8CB5E367E61;H:D953908E78F3F936A78179D1CBA11052;T:CE8B5D7B94
6697313271844A813DF887DCC16B3026E9BCAD4012507DBFBB9FCE4E9B1D4C6E6EF739196C64AE61
0B25D3950D8C197B3FD3AD0AD5EC807C5A7CD3F89815576DD4AEDC32E3C9FAD8AD95E6;0F6108F5B
BC60C1F'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK


   CREATE USER "MDDATA" IDENTIFIED BY VALUES 'S:8FC2F658FBE9030E77A9E913543B44F9
B8DC7086CB4FF255254F72F2F27E;H:F931815EF30E351ECE4D3A0400DCDCBF;T:003122D7B3266E
389FC70D486426CC1AE8A0E1D846D3705DE0BADDA2780940A4F160F50042AF1E0B0AC57E430F2A7D
CB3830D4CC343BC8E49FF09087A52FE5C4E51E5212508E035B3DD1B72BE2E4EA9B;DF02A496267DE
E66'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK


   CREATE USER "SYSBACKUP" IDENTIFIED BY VALUES 'S:73307A8BDFD55FD82D5CF629F68A3
83278E691ECD9F0479F727A920132A8;H:D533EB901D2AAB251FCA0C005AF2C6FC;T:9BB5B94AC7C
4A775CB2071411273CC9AF3A62CAF982C805B8563A51260033FE5AFFC707E207CC46A526CD8D691D
3D214B840E4E9AEDFD5412D276FEB4346ED76C490C0D4F2661D7A8AFE44ECE103C758;23AA48ACB4
2ADCF9'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

CREATE USER "BCO" IDENTIFIED BY VALUES 'S:415947A5A26FC3D8289924861B6DD419BD7

5AEE472DB22D2787B65B3635C;H:010CC5ABDA402DC2FDC8317DB74C5D95;T:60E6CE4216138807E

BFC45193C3895ABAECFFE8FD503B85B91090BE62B91323E6C36106F3E82C5D266BA670DA8A218B5F

B1DB16D63DAF13C82CD564E7B09D03BA98E4156FD2F1B95E353FEB5E2573819;EB4DFA04181D7007

DEFAULT TABLESPACE "TS_RMAN"

TEMPORARY TABLESPACE "TEMP"

CREATE USER "OE" IDENTIFIED BY VALUES 'S:55C5AA59F3B16AC30E6658CC9AF331731A7C

0E06FE478879A58B21CC2947;H:95C8A8735D2D02F516801B94561FD748;T:B315C6DACA84D143C3

F2244E0C19E37AE399E3C80AF8E49CDC41BAB7395DF31DC14DA8D752C5BBB3928D43B84363D0F653

B1456F7C591D1240C5B679128AB76657FE0E1F50BB23022C87D8F14F721F5D;9C30855E7E0CB02D'

DEFAULT TABLESPACE "EXAMPLE"

TEMPORARY TABLESPACE "TEMP"

PASSWORD EXPIRE

ACCOUNT LOCK

CREATE USER "ORACLE_OCM" IDENTIFIED BY VALUES 'S:8FB790A65325CA6858454A857A4F

E43D14E09D2FE5AFFD505315366FCCDD;H:E996810BF4EA0012FDF5CC7AB81C9146;T:DBA1465474

E3F9A86331D26371E49331E69A21B706EE37372E83136F47631967DB97C8A673FB2A6A9DFCC41ADB

8F5B97DB2F09AC5A1214974B232D8EFF6BA51D96CDC8D21F303A750400150F9A1B7781;5A2E026A9

157958C'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

PASSWORD EXPIRE

ACCOUNT LOCK

CREATE USER "OJVMSYS" IDENTIFIED BY VALUES 'S:6FD8F70ABA8C082862C8B2094B813A5

0082956420F2D59C0D9044EBD79C4;H:22BD75631413243D4B30A89F8DE43B03;T:C2206A08A5FAE

8E1CEC368FDCFA4AF1C82B08B0F0BBEB27052245492F16EE8E6F4BD7D4E8655FD240A6085AE32EBD

BFA768378242BC79775C773CCBFC98BAB32B56B2933AA0D040C7D0765B07CBFEF91;A16E716A4E58

4324'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

PASSWORD EXPIRE

ACCOUNT LOCK

CREATE USER "SWP" IDENTIFIED BY VALUES 'S:F0AF28CB996DFB307F6745B7361350636F3

AE36109E134E9B05D8CDEF427;H:4A6F405ED254C6171FE7CFAF3E562F18;T:1475DE719B6F6438C

B9627B291D3F07B4E33D11E5799E661B8F97F371FD68C280C7A0C534CF626E584CCB1D1629EAEA80

0D07EF77F805604C80D4C2647D6FC0F7DCFE20109A189C1449D586894AD55F2;98C61948404F1B8D

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

CREATE USER "SYSKM" IDENTIFIED BY VALUES 'S:D316AAB36203C7D8D5BA061E950160120

D39579B8957E7A51BF9E25430EB;H:91E5E8890F935D7A4393C974DDAB32A6;T:DB51787A7933955

F05329E2FD2EE17ECE057D30FCE143109C599E53699E7D82BFA681797601FF6194A83448866DA94D

F577FFDCB1E4DD1EC66D8648F0F353F1BC406EA848FEA1122D83691A9F3F0F4FC;F2DCB573DBFEDD

9E'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

PASSWORD EXPIRE

ACCOUNT LOCK

CREATE USER "XS$NULL" IDENTIFIED BY VALUES 'S:0000000000000000000000000000000

00000000000000000000000000000'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

PASSWORD EXPIRE

ACCOUNT LOCK

CREATE USER "AJAY" IDENTIFIED BY VALUES 'S:AEC7F815ECA8F290352AC9F1D2D8987D22

855012A8DDD6D847CE93C08292;H:3867F794F922E40F11C0A5AAE3459052;T:89AD7F54FB70D982

A0123FB380F6A55D697DF3D2456BCFB96D332BAB57949E2575240F08D56A835903190287CC9AB1E7

ACA88F1A854D99EBDFB63512F50CCAFE538C1CA66DA3F988BB3B764B13839329;DFBBB5C000AC286

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

CREATE USER "U1" IDENTIFIED BY VALUES 'S:4F021DB16F1C03840F3F23AFDF60EBBFCFBB

3554E6B8490ED878641A1306;H:AC21ABA5169A753700F0249FF13A2213;T:3B00837765A836E272

046BF7CF4AC3A4075FC4A36A3BBC0D5E72A37146CB449CEE87DD1BBB707424DF9FF847353EF68CFA

B720DBF4A944526075487B06F6911CFFFBFF5AFFB563AC266D3267791DFC43;3E81B724A296E296'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

CREATE USER "BI" IDENTIFIED BY VALUES 'S:08E87AC72254C6ED94A5BE6D4D2D323F8FEF

00E5B8B673AF6A8A5D162AE8;H:82E54F3A3653EB97043BE0F1FAE777F0;T:835355C59CF824F050

3C13DBA71F053CAFFBFE2CE4F21B8F8AE3692515A6D999C0EC39433C1111129216E714434D29F56A

F9F0CC4CC867E66D9C10C3EA5E9D55921C12C84A33F60F23354536BAA384B0;FA1D2B85B70213F3'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

PASSWORD EXPIRE

ACCOUNT LOCK

CREATE USER "PM" IDENTIFIED BY VALUES 'S:BC6755A572B6024AB44DB0DE7CD45ACDD73E

2B2E730D7D2A321829A97305;H:E00851694DCBE03D0FE66EEC81484E05;T:4637F05774FEFFBA42

58319E624E12BD1DAEFFF94872F718F0B183A041A72B2EEF480CC63BF063346701A45103D3754982

6A63467467DF731C009CA9AFEA3FF38D7B2EC11A247787168B146B11F43C2E;72E382A52E89575A'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

PASSWORD EXPIRE

ACCOUNT LOCK

CREATE USER "GSMCATUSER" IDENTIFIED BY VALUES 'S:56CAE182D766AA3A6CB3A3B5E0B0

F61D1A16ACEAD1A8566DD8CB5E367E61;H:D953908E78F3F936A78179D1CBA11052;T:CE8B5D7B94

6697313271844A813DF887DCC16B3026E9BCAD4012507DBFBB9FCE4E9B1D4C6E6EF739196C64AE61

0B25D3950D8C197B3FD3AD0AD5EC807C5A7CD3F89815576DD4AEDC32E3C9FAD8AD95E6;0F6108F5B

BC60C1F'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

PASSWORD EXPIRE

ACCOUNT LOCK

CREATE USER "MDDATA" IDENTIFIED BY VALUES 'S:8FC2F658FBE9030E77A9E913543B44F9

B8DC7086CB4FF255254F72F2F27E;H:F931815EF30E351ECE4D3A0400DCDCBF;T:003122D7B3266E

389FC70D486426CC1AE8A0E1D846D3705DE0BADDA2780940A4F160F50042AF1E0B0AC57E430F2A7D

CB3830D4CC343BC8E49FF09087A52FE5C4E51E5212508E035B3DD1B72BE2E4EA9B;DF02A496267DE

E66'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

PASSWORD EXPIRE

ACCOUNT LOCK

CREATE USER "SYSBACKUP" IDENTIFIED BY VALUES 'S:73307A8BDFD55FD82D5CF629F68A3

83278E691ECD9F0479F727A920132A8;H:D533EB901D2AAB251FCA0C005AF2C6FC;T:9BB5B94AC7C

4A775CB2071411273CC9AF3A62CAF982C805B8563A51260033FE5AFFC707E207CC46A526CD8D691D

3D214B840E4E9AEDFD5412D276FEB4346ED76C490C0D4F2661D7A8AFE44ECE103C758;23AA48ACB4

2ADCF9'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

PASSWORD EXPIRE

ACCOUNT LOCK

Method 10 :

The below script will help you to take System Grants of a Schema

SYS>>set pagesize 0   
SYS>>set long 90000  
SYS>>select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&USERNAME') from dual; 
Enter value for username: WMS1
old   1: select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&USERNAME') from dual
new   1: select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','WMS1') from dual

  GRANT UNLIMITED TABLESPACE TO "WMS1"