现象描述
有很多时候,比如在重新迁移或者导入大量数据的时候,我们往往会先删除表上所有索引和主键,完成导入后,在重建索引和主键。
但是,当前环境主键和索引已经有了,这有一批表,我们怎么快速删除和备份,避免加载完,很难找到对应ddl
呢?
处理方法
通过后面的脚本,既可以备份出主键和索引信息了,通过查询备份信息表里的内容,即可知道如何完成删除和重建。
--
删除存储备份信息的表(如果有的话)
drop tbale SYSDBA."PACK#INDEX_BAK";
--
新建存储备份信息的表
CREATE TABLE SYSDBA."PACK#INDEX_BAK"
(
"NAME" VARCHAR(128),
"ID" INTEGER,
"SCHNAME" VARCHAR(128),
"TABNAME" VARCHAR(128),
"FLAG" INTEGER,
"IDX_BAK" VARCHAR(8188),
"IDX_DROP" VARCHAR(8188)) ;
SELECT * FROM PACK#INDEX_BAK
--
初始化表之索引备份
begin
for rs in (
SELECT b.*,a.flag FROM "SYSINDEXES" a,(
select idx.name,IDX.ID,sch.name SCHNAME,tab.name TABNAME from sysobjects SCH,SYSOBJECTS TAB,SYSOBJECTS IDX WHERE
TAB.SCHID=SCH.ID AND SCH.NAME='HNSIA_STAT'
AND IDX.SUBtype$='INDEX' AND IDX.PID=TAB.ID
AND
TAB.NAME IN
( --
行拆分
--
注意,这里是整个sql
的核心:要备份哪些表,注意where
条件就可以。
select
TABLE_NAME from all_tables where
owner in (...) AND TABLE_NAME IN (…
)
)
)b where a.id=b.id and a.flag=0) loop
INSERT INTO PACK#INDEX_BAK VALUES(RS.NAME,RS.ID,RS.SCHNAME,RS.TABNAME,RS.FLAG,indexdef(rs.id,1),'DROP INDEX '||RS.SCHNAME||'.'||RS.NAME||';');
end loop;
COMMIT;
end;
--
生成主键表
--
备份主键和新增主键DDL
--
拼接删除主键的语句
SELECT 'ALTER TABLE '||OWNER1||'.'||TABLE_NAME1||' DROP CONSTRAINT '||CONSTRAINT_NAME1||';' FROM PACK#NEW_PRIMARY_DDL
--
拼接补齐主键的语句
SELECT 'ALTER TABLE '||OWNER1||'.'||TABLE_NAME1||' '||newidx_text||' ' FROM PACK#NEW_PRIMARY_DDL
drop table PACK#NEW_PRIMARY_DDL;
create table PACK#NEW_PRIMARY_DDL AS
--
获取主键sql
with a as( SELECT O_USR.NAME AS OWNER1 ,O_CONS.NAME AS CONSTRAINT_NAME1 ,CAST(CASE WHEN S_CONS.TYPE$ = 'F' THEN 'R' ELSE S_CONS.TYPE$ END AS VARCHAR(1)) AS type_ ,O_TAB.NAME AS TABLE_NAME1 ,COLS.NAME col FROM SYS.SYSCONS S_CONS ,SYS.SYSOBJECTS O_CONS ,SYS.SYSOBJECTS O_TAB ,SYS.SYSOBJECTS O_USR ,SYS.SYSCONS CON LEFT JOIN SYS.SYSINDEXES I ON I.ID = CON.INDEXID ,SYS.SYSCOLUMNS COLS WHERE O_CONS.SUBTYPE$ = 'CONS' AND CON.ID = S_CONS.ID AND COLS.ID = O_TAB.ID AND O_CONS.ID = S_CONS.ID AND O_TAB.SUBTYPE$ = 'UTAB' AND O_TAB.ID = S_CONS.TABLEID AND O_USR.ID = O_TAB.SCHID AND O_USR.TYPE$ = 'SCH' AND
O_USR.NAME = trim(upper('HNSIA_STAT')) AND
O_TAB.NAME IN
( --
行拆分
--
注意,这里是整个sql
的核心:要备份哪些表,注意where
条件就可以。
select TABLE_NAME from all_tables
where owner in (…) AND TABLE_NAME IN (…
)
)
AND ( SF_COL_IS_IDX_KEY(I.KEYNUM, I.KEYINFO, COLS.COLID) = 1 OR CON.CHECKINFO IS NOT NULL ) )
select owner1,table_name1,constraint_name1,' add constraint new_'||constraint_name1||' primary key ('||listagg(col,',')within group (order by rownum)||');' newidx_text
from a group by owner1,table_name1,constraint_name1;