dbms_metadata的使用

2024-02-10 09:18

文章标签 使用 metadata dbms

本文主要是介绍dbms_metadata的使用，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

给出两个检查表结构的方法:

1.desc

2.dbms_metadata.get_ddl包

在oracle9i之后提供一个dbma_metadata包用于获得对象的创建脚本。

在$ORACLE_HOME/rdbms/demo下有两个相关的demo：mddemo.sql、mddemo2.sql

Concept

The DBMS_METADATA package provides interfaces for extracting complete definitions of database objects. The definitions can be expressed either as XML or as SQL DDL. Two styles of interface are provided:

l A flexible, sophisticated interface for programmatic control

l A simplified interface for ad hoc querying

创建表：

create table test as select * from scott.dept;

create view test_v as select * from test;

测试：

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

DBMS_METADATA.GET_DDL('TABLE',

--------------------------------------------------------------------------------

CREATE TABLE "APPS"."TEST"

( "DEPTNO" NUMBER(2,0),

"DNAME" VARCHAR2(14),

"LOC" VARCHAR2(13)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "APPS_TS_TX_DATA"

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

DBMS_METADATA.GET_DDL('VIEW','

--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "APPS"."TEST_V" ("DEPTNO", "DNAME", "LOC") AS

select "DEPTNO","DNAME","LOC" from test

1.得到一个表的ddl语句：

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999             ------显示不完整
SET PAGESIZE 1000    ----分页

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);  ---去除storage等多余参数

SELECT DBMS_METADATA.GET_DDL('TABLE','TCC_NE_FRAME') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLE','TCC_NE_SNAP') FROM DUAL;

2.得到一个用户下的所有表，索引，存储过程的ddl

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999  ------显示不完整
SET PAGESIZE 1000  ----分页
---去除storage等多余参数
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
  FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');

3.得到所有表空间的ddl语句

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999------显示不完整
SET PAGESIZE 1000----分页
---去除storage等多余参数

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

4.得到所有创建用户的ddl

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999------显示不完整
SET PAGESIZE 1000----分页
---去除storage等多余参数

SELECT DBMS_METADATA.GET_DDL('USER',U.username)
FROM DBA_USERS U;
另外，若执行不了该包，则需要正确安装好相应的包。