Defining Script Export Data from an Oracle Database

Question

I am new to working with oracle, my only experience with SQL Server. In the SQL server, you can right-click on the table and say export create script. I need this for Oracle, namely 10g. Is there a way using a GUI or sql command that can do this?

Answer 1

Calling dbms_metadata.get_ddl is one option.

eg.

SET LONG 10000 

SELECT dbms_metadata.get_ddl('TABLE', 'MY-TABLE-NAME')
FROM dual;

Alternatively, some GUI tools, including TOAD and Enterprise Manager, have DDL generators.

Answer 2

, , all_objects all_users. : :

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 '%$%'
  )
;

:

• oracle dbms_metadata.GET_DDL
• oracle all_objects