表分區處理語句記錄

--表分區處理
SELECT TABLE_NAME,TABLESPACE_NAME ,'CREATE TABLESPACE ' || V.TABLESPACE_NAME ||'
DATAFILE ''E:\ORADATA\GISAP\' || V.TABLESPACE_NAME || '.DBF'' SIZE 8M
AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;' AS 建立資料表空間,
'CREATE TABLESPACE ' || V.TABLESPACE_NAME ||'INDEX
DATAFILE ''E:\ORADATA\GISAP\' || V.TABLESPACE_NAME ||'INDEX.DBF'' SIZE 8M
AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;' AS 建立索引資料表空間 ,
'DROP TABLESPACE ' || V.TABLESPACE_NAME ||' INCLUDING CONTENTS AND DATAFILES;' AS 刪除資料表空間,
'DROP TABLESPACE ' || V.TABLESPACE_NAME ||'INDEX INCLUDING CONTENTS AND DATAFILES;' AS 刪除索引資料表空間,
'ALTER USER GISAP QUOTA UNLIMITED ON  ' || V.TABLESPACE_NAME || ';' 給資料表空間授權,
'ALTER USER GISAP QUOTA UNLIMITED ON  ' || V.TABLESPACE_NAME ||'INDEX;' 給索引資料表空間授權
FROM (SELECT DISTINCT T.TABLE_NAME, T.TABLESPACE_NAME
FROM ALL_TAB_PARTITIONS T
WHERE T.TABLE_OWNER = 'AGGPS'
AND T.TABLE_NAME NOT LIKE '%BIN%') V
ORDER BY TABLE_NAME

--產生處理約束語句
SELECT'ALTER TABLE '||T.TABLE_NAME||' DISABLE CONSTRAINT '||T.CONSTRAINT_NAME||';'  FROM ALL_CONSTRAINTS T WHERE T.OWNER = 'AGGPS' AND T.CONSTRAINT_NAME NOT LIKE 'SYS_%';
SELECT'ALTER TABLE '||T.TABLE_NAME||' ENABLE CONSTRAINT '||T.CONSTRAINT_NAME||';'  FROM ALL_CONSTRAINTS T WHERE T.OWNER = 'AGGPS' AND T.CONSTRAINT_NAME NOT LIKE 'SYS_%';

--查詢使用了獨立資料表空間的表，即有表分區的表
SELECT * FROM ALL_TABLES T WHERE T.OWNER = 'AGGPS' AND T.TABLESPACE_NAME IS NULL;
--查詢沒使用了獨立資料表空間的表，即有沒有表分區的表
SELECT * FROM ALL_TABLES T WHERE T.OWNER = 'AGGPS' AND T.TABLESPACE_NAME IS NOT NULL;
--查詢指定使用者下的所有表
SELECT * FROM ALL_TABLES T WHERE T.OWNER = 'AGGPS';
--查詢指定使用者下的所有油耗相關的表
SELECT * FROM ALL_TABLES T WHERE T.OWNER = 'AGGPS' AND T.TABLE_NAME LIKE '%OIL%';