I am new at working with oracle, my only experience is in sql server. In SQL server you can right click a table and say export create script. I need that for Oracle, specifically 10g. Is there a way via GUI or sql command that can accomplish this?
1
-
How are you accessing the Oracle instance - with PLSQL Developer, Toad, SQLPlus, etc?OMG Ponies– OMG Ponies2010-08-16 22:19:21 +00:00Commented Aug 16, 2010 at 22:19
Add a comment
|
2 Answers
Calling dbms_metadata.get_ddl is one option.
e.g.
SET LONG 10000
SELECT dbms_metadata.get_ddl('TABLE', 'MY-TABLE-NAME')
FROM dual;
Alternatively a number of GUI tools including TOAD and Enterprise Manager have DDL generators.
Comments
More general solution would be to dump filtered list of tables, but also other types of objects by using all_objects and all_users views. Example that works for me:
select dbms_metadata.GET_DDL(u.object_type,u.object_name, u.owner)
from all_objects u
where 1=1
and u.object_type in ('TABLE', 'INDEX', 'FUNCTION', 'PROCEDURE', 'VIEW',
'TYPE', 'TRIGGER', 'SEQUENCE')
and u.object_name not like 'SYS_%'
and owner in (
select username from dba_USERS where DEFAULT_TABLESPACE not like 'SYS%'
and username not in ('ORACLE_OCM')
and username not like '%$%'
)
;
References:
- oracle dbms_metadata.GET_DDL function
- oracle all_objects view
Comments
