表分区处理语句记录

--表分区处理
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%';