用dbms_metadata.get_ddl()提取DDL指针[讨论]_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 5000 | 回复: 2

主题： 用dbms_metadata.get_ddl()提取DDL指针[讨论]

yapingtian

注册用户

等级：上等兵
经验：127
发帖：14
精华：0
注册：2012-3-23
状态：离线
发送短消息息给yapingtian

加好友发送短消息息给yapingtian

发消息

发表于：

2012-3-23 11:42:46 | [全部帖] [楼主帖]

楼主

用dbms_metadata.get_ddl()提取DDL指针

适用于：

Oracle数据库企业版——Version: 9.0 to 10.2

这篇文档里的信息适用于任何平台

假设db证明或正常运行抛出许多的ORA-12700's or ORA-01578's or ORA-01499's在开发总体指标表明警报日志表坏了。

如果很简单的退出和创建表空间，获得再创建指针的语法就更复杂了

下面这个例子是为了任何的新的9i 或 10g DB

sqlplus /nolog
conn system/oracle
select index_name from dba_indexes
where tablespace_name ='EXAMPLE' and index_name like 'E%'
order by 1 asc;
EMP_EMAIL_UK
EMP_EMP_ID_PK
EMP_DEPARTMENT_IX
EMP_JOB_IX
EMP_MANAGER_IX
EMP_NAME_IX
Method
DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
to get the DDL.
Example
select dbms_metadata.get_ddl('INDEX', 'EMP_EMAIL_UK', 'HR') from dual;
Run
set heading off
set echo off
set flush off
set pagesize 9999
set linesize 9999
set long 9999
spool rcindscr.sql
select 'select dbms_metadata.get_ddl("INDEX", "'||index_name||'", "'||owner||'") from dual;'
from dba_indexes where tablespace_name='EXAMPLE' and index_name like 'E%';
spool off
result: (after converting double quotes to single quotes in a text editor):
select dbms_metadata.get_ddl('INDEX', 'EMP_EMAIL_UK', 'HR') from dual;
select dbms_metadata.get_ddl('INDEX', 'EMP_EMP_ID_PK', 'HR') from dual;
select dbms_metadata.get_ddl('INDEX', 'EMP_DEPARTMENT_IX', 'HR') from dual;
select dbms_metadata.get_ddl('INDEX', 'EMP_JOB_IX', 'HR') from dual;
select dbms_metadata.get_ddl('INDEX', 'EMP_MANAGER_IX', 'HR') from dual;
select dbms_metadata.get_ddl('INDEX', 'EMP_NAME_IX', 'HR') from dual;

现在，让我们看一下得到了什么：

set heading off
set echo off
set flush off
set pagesize 3999
set linesize 3999
set long 3999
set longc 3999
spool indsyntax.sql
select dbms_metadata.get_ddl('INDEX', 'EMP_EMAIL_UK', 'HR') from dual;
select dbms_metadata.get_ddl('INDEX', 'EMP_EMP_ID_PK', 'HR') from dual;
select dbms_metadata.get_ddl('INDEX', 'EMP_DEPARTMENT_IX', 'HR') from dual;
select dbms_metadata.get_ddl('INDEX', 'EMP_JOB_IX', 'HR') from dual;
select dbms_metadata.get_ddl('INDEX', 'EMP_MANAGER_IX', 'HR') from dual;
select dbms_metadata.get_ddl('INDEX', 'EMP_NAME_IX', 'HR') from dual;
spool off

（注: 上面甩用的“SET”命令是非常重要的)

结果会用SQL语句运行

CREATE UNIQUE INDEX "HR"."EMP_EMAIL_UK" ON "HR"."EMPLOYEES" ("EMAIL")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE"
/
CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE"
/
CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES" ("DEPARTMENT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE"
/
CREATE INDEX "HR"."EMP_JOB_IX" ON "HR"."EMPLOYEES" ("JOB_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE"
/
CREATE INDEX "HR"."EMP_MANAGER_IX" ON "HR"."EMPLOYEES" ("MANAGER_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE"
/
CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME", "FIRST_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE"
/