生成表空间处理语句

select '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 = 'GISAP'
           AND T.TABLE_NAME NOT LIKE '%BIN%') V

此语句用于查出那些表使用了表分区。