发布一个自定义获取表/索引ddl语句的工具包

显示全部楼层 · 2008-2-13 12:43:03

在日常工作中，我们经常需要获取表/索引的创建语句，当然了，通过很多工具可以实现，比如
PL/SQL，TOAD，EXP/IMP以及系统自带的dbms_metadata包都可以达到目前。
但美中不足的是，一是需要依托第三方工具，比如PL/SQL DEV、TOAD，二是获取的DDL语句中很不
清晰，有很多我们认为“没用的”script 无法剔除，需要再编辑才可以实现或达到我们的目的。
本维护工具包toms_dbms是toms_zhang通过对Oracle内部数据字典分析获取得到的相关DDL语句。在
实际工作中帮了不忙，呵呵。
前段时间在看见网上有人讨论获取对象[表/索引]的DDL语句有点不爽，稍微整理了一下，今天先发布
get_meta,get_ddl两个子功能，供大家测试使用。

如果大家在使用的过程中，发现任何BUG或有任何意见，欢迎随时联系。谢谢
下载地址：http://tomszrp.itpub.net/post/11835/96716
下面是使用的简单说明信息
[php]
一：创建基础表
SQL> drop table toms_sql;
表已丢弃。
SQL> create table toms_sql (
2ownervarchar2(32),
3name varchar2(32),
4type varchar2(6) default 'TABLE',--INDEX/TABLE
5sqlvarchar2(1000),
6seqnumber
7);
表已创建。
SQL> comment on table toms_sql is '存放调用toms_dbms工具包导出的建表、索引脚本';
注释已创建。
二：下载toms_dbms.plb并安装[和我联系吧]
SQL> start e:\toms_dbms.plb
程序包已创建。

程序包主体已创建。
三：查看toms_dbms并获取帮助信息
SQL> desc toms_dbms
PROCEDURE GET_DDL
参数名称
类型
输入/输出默认值？
------------------------------ ----------------------- ------ --------
V_OWNER
VARCHAR2
IN
V_TABLENAME
VARCHAR2
IN
V_ARGFLAG
VARCHAR2
IN DEFAULT
PROCEDURE GET_META
参数名称
类型
输入/输出默认值？
------------------------------ ----------------------- ------ --------
V_OWNER
VARCHAR2
IN
V_SEGNAME
VARCHAR2
IN
V_SEGTYPE
VARCHAR2
IN DEFAULT
V_ARGFLAG
VARCHAR2
IN DEFAULT
PROCEDURE HELP
SQL> set serveroutput on
SQL> exec toms_dbms.help
TOMS_DBMS 工具包1.0 版本[pub 部分]使用帮助说明信息
-----------------------------过程 get_ddl 说明 -------------------------------
功能：获取表及表上索引的DDL语句
参数1：v_owner in varchar2 表的属主(不区分大小写)
参数2：v_tablename in varchar2 表名(不区分大小写)
参数3：v_argflag in varchar2 default Y 是否导出存储参数不区分大小写),缺省Y
备注：生成的数据存放在toms_sql表中,取DDL语句:
select sql from toms_sql
where owner=v_owner and name=v_tablename
order by seq
-----------------------------过程 get_meta 说明 -------------------------------
功能：获取表/分区表/索引/分区索引的DDL语句
参数1：v_owner in varchar2 表的属主(不区分大小写)
参数2：v_segname in varchar2 对象名(不区分大小写)
参数3：v_segtype in varchar2 default TABLE 对象类型(不区分大小写)
参数4：v_argflag in varchar2 default Y 是否导出存储参数不区分大小写),缺省Y
备注：生成的数据存放在toms_sql表中,取DDL语句:
select sql from toms_sql
where owner=v_owner and name=v_tablename
order by seq
PL/SQL 过程已成功完成。
SQL>

四：测试一个看看，呵呵
SQL> exec toms_dbms.get_ddl('study','toms');
PL/SQL 过程已成功完成。
SQL> select sql from toms_sql order by type,name,seq;
prompt create index inx_toms
create index inx_toms on toms(
no
) tablespace STUDY
pctfree 10
initrans 2
maxtrans 255
storage
(
initial .5M
next .5M
minextents 1
maxextents unlimited
pctincrease 0
)
/
prompt create table toms
create table toms
(
no number
) tablespace STUDY
pctfree 10
initrans 1
maxtrans 255
storage
(
initial .5M
next .5M
minextents 1
maxextents unlimited
pctincrease 0
)
/
已选择33行。
SQL> exec toms_dbms.get_ddl('study','toms','N');
PL/SQL 过程已成功完成。
SQL> select sql from toms_sql order by type,name,seq;
prompt create index inx_toms
create index inx_toms on toms(
no
) tablespace STUDY
/
prompt create table toms
create table toms
(
no number
) tablespace STUDY
/
已选择11行。
SQL>
再测试一个分区表的情况.
SQL> exec toms_dbms.get_ddl('study','toms_part','N');
PL/SQL 过程已成功完成。
SQL> select sql from toms_sql order by seq;
prompt create table toms_part
create table toms_part
(
region number(3,0) ,
name varchar2(32)
) partition by RANGE(region)
(
partition part_530 values less than (531) tablespace study,
partition part_532 values less than (532) tablespace study,
partition part_max values less than (999) tablespace study
)
/
已选择12行。
SQL>

[/php]

千问 · 2008-2-13 12:43:03

继续测试带subpartition, 带lob的分区等功能.

千问 · 2008-2-13 12:43:03

不错，支持。

有一本书叫《Oracle9i 脚本工具》，不错，各个方面的sql都有。

千问 · 2008-2-13 12:43:03

最初由 d.c.b.a 发布
[B]继续测试带subpartition, 带lob的分区等功能. [/B]
subpartition目前也支持，不过可能不是很完美啊！对LOB类型的支持可能有问题，有空测试并改进一下
[php]
SQL> CREATE TABLE toms_sub_part (
2 customer_idNUMBER(6),
3 cust_first_nameVARCHAR2(20),
4 cust_last_name VARCHAR2(20),
5 nls_territoryVARCHAR2(30),
6 credit_limit NUMBER(9,2))
7 PARTITION BY RANGE (credit_limit)
8 SUBPARTITION BY LIST (nls_territory)
9SUBPARTITION TEMPLATE
10 (SUBPARTITION eastVALUES
11
('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
12
SUBPARTITION west VALUES
13
('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
14
SUBPARTITION other VALUES (DEFAULT))
15(PARTITION p1 VALUES LESS THAN (1000),
16 PARTITION p2 VALUES LESS THAN (2500),
17 PARTITION p3 VALUES LESS THAN (MAXVALUE));
表已创建。
SQL> desc toms_sub_part
名称
是否为空? 类型
----------------------------------------- -------- ------------------
CUSTOMER_ID
NUMBER(6)
CUST_FIRST_NAME
VARCHAR2(20)
CUST_LAST_NAME
VARCHAR2(20)
NLS_TERRITORY
VARCHAR2(30)
CREDIT_LIMIT
NUMBER(9,2)
SQL>

Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as study
SQL> desc toms_dbms.get_ddl
Parameter Type Mode Default?
----------- -------- ---- --------
V_OWNER VARCHAR2 IN

V_TABLENAME VARCHAR2 IN

V_ARGFLAG VARCHAR2 IN Y
SQL> exec toms_dbms.get_ddl('STUDY','TOMS_SUB_PART','N');
PL/SQL procedure successfully completed
SQL> select sql from toms_sql order by type,name,seq;
SQL
--------------------------------------------------------------------------------
prompt create table toms_sub_part
create table toms_sub_part
(
customer_id number(6,0) ,
cust_first_name varchar2(20) ,
cust_last_name varchar2(20) ,
nls_territory varchar2(30) ,
credit_limit number(9,2)
) partition by RANGE(credit_limit)
subpartition by LIST(nls_territory)
subpartition template(
subpartition east values ('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
subpartition west values ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
subpartition other values (DEFAULT) )
(
partition p1 values less than (1000) tablespace study
(
subpartition p1_east values ('CHINA', 'JAPAN', 'INDIA', 'THAILAND') tablespace study,
subpartition p1_west values ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND') tablespace study,
subpartition p1_other values (DEFAULT) tablespace study
),
partition p2 values less than (2500) tablespace study
(
subpartition p2_east values ('CHINA', 'JAPAN', 'INDIA', 'THAILAND') tablespace study,
subpartition p2_west values ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND') tablespace study,
subpartition p2_other values (DEFAULT) tablespace study
),
partition p3 values less than (MAXVALUE) tablespace study
(
subpartition p3_east values ('CHINA', 'JAPAN', 'INDIA', 'THAILAND') tablespace study,
subpartition p3_west values ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND') tablespace study,
subpartition p3_other values (DEFAULT) tablespace study
)
)
/

[/php]

千问 · 2008-2-13 12:43:03

最初由 hrb_qiuyb 发布
[B]不错，支持。

有一本书叫《Oracle9i 脚本工具》，不错，各个方面的sql都有。 [/B]
这本书的电子版在那有下载或书店有其图书？

千问 · 2008-2-13 12:43:03

不错，测试一下函数索引

千问 · 2008-2-13 12:43:03

支持小写和关键字吗？

千问 · 2008-2-13 12:43:03

测试下

千问 · 2008-2-13 12:43:03

最初由 yangtingkun 发布
[B]支持小写和关键字吗？ [/B]
对象名小写？，老杨说的是这种情况吗：
[php]
SQL> create table "test" (no int);
Table created
SQL> select table_name from tabs;
test
SQL>
[/php]
这样的不支持，我内部做了强制转换为大写了．对函数索引需要加强．
你说的关键字是指哪方面，只要存在系统中［包括系统SYS用户］的表/索引[类型上有些不支持]都支持
[php]
SQL> exec toms_dbms.get_ddl('sys','access$');
PL/SQL procedure successfully completed
SQL>
SQL> select sql from toms_sql order by name,type,seq;
prompt create table access$
create table access$
(
d_obj# numbernot null ,
order# numbernot null ,
columns raw ,
types numbernot null
) tablespace SYSTEM
pctfree 10
initrans 1
maxtrans 255
storage
(
initial .015625M
next M
minextents 1
maxextents unlimited
pctincrease
)
/
prompt create index i_access1
create index i_access1 on access$(
d_obj# ,
order#
) tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial .015625M
next M
minextents 1
maxextents unlimited
pctincrease
)
/
[/php]
访问toms_dbms时，是不区分大小写的．

千问 · 2008-2-13 12:43:03

其实我说的关键字和小写是一个意思。
你既然没有在输出中加上双引号应该是屏蔽了小写和关键字的问题