dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
The following are then various options available
| Object Type | Name | Datatype | Meaning |
|---|---|---|---|
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If Note: This object type is being deprecated. |
|
|
|
|
Calling |
|
|
|
|
If |
|
|
|
|
The name of a user from whom the role must be revoked. If this is a non-null string and if the Note: When you issue a Defaults to null string. |
|
|
|
|
If Defaults to |
|
|
|
|
A number representing the percentage by which space allocation for the object type is to be modified. If the object type is - in file specifications, the value of - - For other object types, |
|
The following PL/SQL code can be used to generate schema creation scripts Only tables, indexes and constraints are included here -- For setting the default transformation BEGIN dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE ); dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true ); dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false ); dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE ); dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false ); END; / -- Output DATA to CLOB It will be better to output the data to another table so that it can be queried later.
create table ddl_scripts(
object_name varchar2(50),
object_type varchar2(30),
text varchar2(4000));
-- Format the DDL text using trigger
create or replace trigger ddl_scripts_format_trg before insert on ddl_scripts
for each row
declare
n1 number;
n2 number;
BEGIN
n1 := instr(:new.text, '"."',1,1);
n2 := instr(:new.text, '"', 1,1);
if n1<50 then
:new.text := replace(substr(:new.text,1,n2-1)||substr(:new.text,n1+3), '"', null);
:new.text := ltrim(rtrim(:new.text, chr(10)), chr(10));
:new.text := ltrim(rtrim(:new.text, chr(32)), chr(32));
:new.text := ltrim(rtrim(:new.text, chr(10)), chr(10));
:new.text := ltrim(rtrim(:new.text, chr(32)), chr(32));
end if;
END;
/
declare
a1 varchar2(32767);
str varchar2(32767);
n1 number;
cur_user varchar2(30);
begin
select '"'||upper(username)||'"."' into cur_user from user_users;
for c1 in(select a.table_name from user_tables a where a.table_name
not in(select object_name from user_recyclebin) order by 1)
LOOP
select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('TABLE', c1.table_name), 32767,1) INTO a1 from dual;
insert into ddl_scripts values(c1.table_name, 'TABLE', a1);
for c2 in(select index_name from user_indexes where table_name=c1.table_name and index_type in('NORMAL', 'BITMAP') order by 1)
LOOP
select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('INDEX', c2.index_name), 32767,1) INTO a1 from dual;
insert into ddl_scripts values(c1.table_name, 'INDEX', a1);
END LOOP;
END LOOP;
-- Trigger scripts
for c1 in(select table_name, trigger_name from user_triggers where trigger_name
not in(select object_name from user_recyclebin) order by 1,2) LOOP
select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('TRIGGER', c1.trigger_name), 32767,1) INTO a1 from dual;
insert into ddl_scripts values(c1.table_name, 'TRIGGER', a1);
END LOOP;
-- Views
for c1 IN(select view_name from user_views order by 1) LOOP
select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('VIEW', c1.view_name), 32767,1) INTO a1 from dual;
insert into ddl_scripts values(c1.view_name, 'VIEW', a1);
END LOOP;
-- Sequence Scripts
for c1 IN(select sequence_name from user_sequences order by 1) LOOP
select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('SEQUENCE', c1.sequence_name), 32767,1) INTO a1 from dual;
insert into ddl_scripts values(c1.sequence_name, 'SEQUENCE', a1);
END LOOP;
-- Directoryies
for c1 in(select directory_name from all_directories order by 1) LOOP
select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('DIRECTORY', c1.directory_name), 32767,1) INTO a1 from dual;
insert into ddl_scripts values(c1.directory_name, 'DIRECTORY', a1);
END LOOP;
-- Materialized Views
for c1 in(select mview_name from user_mviews order by 1) LOOP
select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('MATERIALIZED_VIEW', c1.mview_name), 32767,1) INTO a1 from dual;
insert into ddl_scripts values(c1.mview_name, 'MATERIALIZED VIEW', a1);
END LOOP;
-- Materialized View Logs
for c1 in(select log_table from user_mview_logs order by 1) LOOP
select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG', c1.log_table), 32767,1) INTO a1 from dual;
insert into ddl_scripts values(c1.log_table, 'MATERIALIZED_VIEW_LOG', a1);
END LOOP;
-- Synonyms
for c1 in(select synonym_name from user_synonyms order by 1) LOOP
select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('SYNONYM', c1.synonym_name), 32767,1) INTO a1 from dual;
insert into ddl_scripts values(c1.synonym_name, 'SYNONYM', a1);
END LOOP;
-- Types
for c1 in(select type_name from user_types order by 1) LOOP
select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('TYPE', c1.type_name), 32767,1) INTO a1 from dual;
insert into ddl_scripts values(c1.type_name, 'TYPE', a1);
END LOOP;
END;
/
-- Query the table ddl_scripts
SQL> set pagesize 0
SQL> set linesize 32767
SQL> set trimspool ON
SQL spool c:\temp\ddl.txt
SQL> select text from ddl_scripts order by
case when object_type in('TABLE', 'INDEX', 'TRIGGER') then 0 else 1 end,
object_name, decode(object_type, 'TABLE',1,'TRIGGER',3,'INDEX',2);
SQL> spool off
|
Manage Locks in Oracle
Scripts to generate schema objects
Using Index Fast Full Scan
Using Returning clause
Managing Tablespaces and Datafiles
Script to reorganize tables
Hidden Parameters in Oracle
Using Multiple block sizes for tablespaces
Index Optimization Scripts
Managing Constraints
Oracle Installation
Oracle Tips
Oracle links
Oracle Books
Indexing strategies
Segment usage statistics