用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"
/