Oracle 获取DDL语句入门小记

1
2

sqlplus / as sysdba #这是典型的操作系统认证，不需要listener进程
sqlplus system/password as sysdba #这种连接方式只能连接本机数据库，同样不需要listener进程

1
2

sqlplus /nolog
CONNECT username/password@host[:port][/service_name]

1

select user from dual;

1

select name from v$database;

1
2
3

select owner, table_name from dba_tables;
select owner, table_name from all_tables; # do not have access dba_tables;
select table_name from user_tables; # you own

1
2
3
4
5
6
7
8
9

set pagesize 0
set long 90000
set feedback off
set echo off
spool schema.sql
select dbms_metadata.get_ddl('TABLE', u.table_name) from user_tables u;
select dbms_metadata.get_ddl('VIEW', u.VIEW_name) from user_views u;
select dbms_metadata.get_ddl('INDEX', u.index_name) from user_indexes u;
spool off;

1
2
3
4
5
6
7
8
9

set pagesize 0
set long 90000
set feedback off
set echo off
spool <table_name>.sql
select dbms_metadata.get_ddl('TABLE', '<table_name>', '<SCHEMA>') from dual;
select dbms_metadata.get_ddl('VIEW', '<view_name>', '<SCHEMA>') from dual;
select dbms_metadata.get_ddl('INDEX', '<index_name>', '<SCHEMA>') from dual;
spool off;

1
2
3
4
5
6
7
8
9

set pagesize 0
set long 90000
set feedback off
set echo off
spool procedures.sql
select dbms_metadata.get_ddl('PROCEDURE', u.object_name)
from user_objects u
where object_type = 'PROCEDURE';
spool off;

1
2
3
4
5
6
7
8
9

set pagesize 0
set long 90000
set feedback off
set echo off
spool <Procedure_Name>.sql
select text
from all_source
where name= '<procedure_name>';
spool off;