Oracle dbms_metadata package to extract the schema ddl ....
SET LONG 10000 TRIMSPOOL ON LINES 180 HEADING OFF FEEDBACK OFF PAGES 0 VERIFY OFF
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SPECIFICATION',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'BODY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',TRUE);
SPOOL ANUJ_DDL.SQL
CONNECT SCOTT/TIGER;
SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, USER) FROM USER_OBJECTS ;
from sys account
SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, owner) FROM dba_OBJECTS
where owner='SCOTT';
set pages 800 lines 300 long 99999
select DBMS_METADATA.GET_DDL('USER','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&USER') ddl from dual ;
set autoprint on long 100000
variable Y clob
variable x clob
declare
no_grant exception;
pragma exception_init( no_grant, -31608 );
begin
begin
:Y := dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', '&&USER' );
exception
when no_grant then :Y := '-- no system grants';
end ;
end;
/
SELECT DBMS_METADATA.GET_DDL('ROLE','RESOURCE') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL( 'SYSTEM_GRANT','RESOURCE') from dual;
No comments:
Post a Comment