oracle dbms_metadata.get_ddl方法总结

最新推荐文章于 2024-04-09 09:37:52 发布

hbhe0316

最新推荐文章于 2024-04-09 09:37:52 发布

阅读量394

点赞数

文章标签： python 数据库 mysql sql java

本文链接：https://blog.youkuaiyun.com/hbhe0316/article/details/122701096

版权

本文介绍如何使用Oracle的DBMS_METADATA包获取不同数据库对象的DDL语句，包括表、索引、视图、存储过程、函数及表空间等，并提供了一个脚本用于批量导出指定模式下的所有DDL。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

set line 200
set pagesize 0
set long 99999
set feedback off
set echo off

1.查看SYS.T1表的建表语句

SQL> select dbms_metadata.get_ddl('TABLE','T1','SYS') from dual;

DBMS_METADATA.GET_DDL('TABLE','T1','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."T1"
   (    "ID" NUMBER(*,0),
        "NAME" VARCHAR2(10)
   )

2.查看索引https://www.cndba.cn/hbhe0316/article/22192

SQL> create INDEX T1_INDEX ON SYS.T1(ID);

Index created.

SQL> select dbms_metadata.get_ddl('INDEX','T1_INDEX','SYS') from dual;

DBMS_METADATA.GET_DDL('INDEX','T1_INDEX','SYS')
--------------------------------------------------------------------------------

  CREATE INDEX "SYS"."T1_INDEX" ON "SYS"."T1" ("ID")
  PCTFREE 10 INITRANS 2 M

3.查看视图https://www.cndba.cn/hbhe0316/article/22192

SQL> create view T1_VIEW as select id from SYS.T1;

View created.

SQL> select dbms_metadata.get_ddl('VIEW','T1_VIEW','SYS') from dual; 

DBMS_METADATA.GET_DDL('VIEW','T1_VIEW','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."T1_VIEW" ("ID") AS
  sele

4.查看PROCEDUREhttps://www.cndba.cn/hbhe0316/article/22192https://www.cndba.cn/hbhe0316/article/22192

select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','PROCEDURE_OWNER') from dual;

5.查看FUNCTION

https://www.cndba.cn/hbhe0316/article/22192

select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual;

6.下面这个脚本用于获得某个schema下所有的表、索引、视图、存储过程、函数的DDL

https://www.cndba.cn/hbhe0316/article/22192

set pagesize 0
set long 90000
set feedback off
set echo off
spool schema_ddl.sql
SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME,U.OWNER) FROM DBA_TABLES u;
SELECT DBMS_METADATA.GET_DDL('VIEW',U.VIEW_NAME,U.OWNER) FROM DBA_VIEWS u;
SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME,U.OWNER) FROM DBA_INDEXES u;
select dbms_metadata.get_ddl('PROCEDURE',U.OJECT_NAME, U.OWNER,) from dba_objects u where u.object_type = 'PROCEDURE';
select dbms_metadata.get_ddl('FUNCTION',U.OJECT_NAME, U.OWNER,) from dba_objects u where u.object_type = 'FUNCTION';
spool off;

7.获得表空间的DDLhttps://www.cndba.cn/hbhe0316/article/22192

select dbms_metadata.get_ddl('TABLESPACE','TBS_NAME') from dual;

8.获得所有表空间的DDL:

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;

9.获得用户的DDLhttps://www.cndba.cn/hbhe0316/article/22192

https://www.cndba.cn/hbhe0316/article/22192

select dbms_metadata.get_ddl('USER','EPAY_USER') from dual;

10.获得所有用户的DDLhttps://www.cndba.cn/hbhe0316/article/22192

SELECT DBMS_METADATA.GET_DDL('USER',U.username) FROM DBA_USERS U;

Linux,oracle