set head off
set pages 0
set long 9999999 longchunksize 20000 pagesize 0 lin 5000 feedback off verify off trimspool on
begin
DBMS_METADATA.SET_TRANSFORM_PARAM
( DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', true );
DBMS_METADATA.SET_TRANSFORM_PARAM
( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
DBMS_METADATA.SET_TRANSFORM_PARAM
( DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', true );
DBMS_METADATA.SET_TRANSFORM_PARAM
( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true );
DBMS_METADATA.SET_TRANSFORM_PARAM
( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true );
DBMS_METADATA.SET_TRANSFORM_PARAM
( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );
end;
/
prompt DDL TABLESPACES
prompt ******************************
SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name)
FROM dba_tablespaces
/
prompt DDL PROFILES
prompt ******************************
select dbms_metadata.get_ddl('PROFILE', profile) profile
from dba_users
where profile <> 'DEFAULT'
/
prompt DDL USERS
prompt ******************************
select dbms_metadata.get_ddl('USER', username) usercreate
from dba_users
where username ='&USERNAME'
/
prompt DDL TBS-QUOTAS
prompt ******************************
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', username) tablespacequotas
from dba_ts_quotas
where username ='&USERNAME'
/
prompt DDL ROLE-GRANTS
prompt ******************************
select dbms_metadata.get_granted_ddl('ROLE_GRANT', grantee) rolegrant
from dba_role_privs
where grantee = '&GRANTEE'
/
prompt DDL SYSTEM-GRANTS
prompt ******************************
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', grantee) systemgrants
from dba_sys_privs
where grantee = '&GRANTEE'
/
prompt DDL OBJECTS-GRANTS
prompt ******************************
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', username) objectgrants
from dba_users
where username = '&USERNAME'
/
prompt DDL DEFAULT-ROLES
prompt ******************************
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', grantee) defaultroles
from dba_role_privs rp
where default_role = 'YES'
and grantee ='&GRANTEE'
/
prompt DDL DATABASE-LINKS
prompt ******************************
SELECT DBMS_METADATA.get_ddl ('DB_LINK', db_link, owner)
FROM dba_db_links
/
prompt DDL DIRECTORIES
prompt ******************************
SELECT DBMS_METADATA.get_ddl ('DIRECTORY', directory_name)
FROM dba_directories
/
prompt DDL TABLES
prompt ******************************
select dbms_metadata.get_ddl('TABLE',TABLE_NAME,OWNER) TABLES from DBA_TABLES
WHERE OWNER = '&OWNER'
/
prompt DDL TB_CONSTRAINTS
prompt ******************************
select dbms_metadata.get_dependent_ddl('CONSTRAINT',TABLE_NAME,OWNER) TBCONSTRAINTS
from dba_constraints
WHERE OWNER ='&OWNER'
/
prompt DDL REF_CONSTRAINTS
prompt ******************************
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT',TABLE_NAME,OWNER) REFCONSTRAINTS
from dba_constraints
WHERE OWNER ='&OWNER'
/
prompt INDEXES
prompt ******************************
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',UPPER(TABLE_NAME),UPPER(OWNER))
from DBA_TABLES
WHERE OWNER = '&OWNER'
/
prompt DDL TRIGGERS
prompt ******************************
select dbms_metadata.get_dependent_ddl('TRIGGER',TABLE_NAME,OWNER)
from dba_triggers
WHERE OWNER ='&OWNER'
/
prompt DDL VIEWS
prompt ******************************
select dbms_metadata.get_ddl('VIEW',view_name,OWNER)
from dba_views
WHERE OWNER ='&OWNER'
/
prompt DDL SEQUENCES
prompt ******************************
SELECT DBMS_METADATA.get_ddl ('SEQUENCE', sequence_name, sequence_owner)
FROM all_sequences
WHERE sequence_owner = '&SEQOWNER'
/
**************************
** examples:
**************************
SQL> select dbms_metadata.get_ddl('USER', username) usercreate
2 from dba_users
3 where username ='&USERNAME'
4 /
Informe o valor para username: SCOTT
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:2933FA75328E45F4512B34C2EA6025E253C401B52F8B8051C1088FD97DCA;F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE
ACCOUNT LOCK;
SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', grantee) systemgrants
2 from dba_sys_privs
3 where grantee = '&GRANTEE'
4 /
Informe o valor para grantee: SCOTT
GRANT UNLIMITED TABLESPACE TO "SCOTT";
SQL> select dbms_metadata.get_ddl('TABLE',TABLE_NAME,OWNER) TABLES from DBA_TABLES
2 WHERE OWNER = '&OWNER'
3 /
Informe o valor para owner: SCOTT
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE;
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;
CREATE TABLE "SCOTT"."BONUS"
( "ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"SAL" NUMBER,
"COMM" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
CREATE TABLE "SCOTT"."SALGRADE"
( "GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
SQL>
Nenhum comentário:
Postar um comentário