查询，创建，扩充表空间&&impdp----表空间大全

SELECT A.TABLESPACE_NAME,

       A.FILENUMBER,

       ROUND(A.TBSCURRENTSIZEINMB/1024, 2) TOTAL_SIZE_G,

       ROUND(B.SEGMENTSIZEINMB/1024, 2) USED_SIZE_G,

       ROUND((A.TBSCURRENTSIZEINMB-B.SEGMENTSIZEINMB)/1024, 2) FREE_SIZE_G,

       ROUND(NVL(B.SEGMENTSIZEINMB, 0) / A.TBSCURRENTSIZEINMB, 4) * 100 CAPACITYUSED

  FROM (SELECT TABLESPACE_NAME,

               COUNT(1) FILENUMBER,

               SUM(BYTES) / 1024 / 1024 TBSCURRENTSIZEINMB

          FROM DBA_DATA_FILES

         GROUP BY TABLESPACE_NAME) A,

       (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 SEGMENTSIZEINMB

          FROM DBA_SEGMENTS

         GROUP BY TABLESPACE_NAME) B

WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)

    --AND (A.TABLESPACE_NAME LIKE &＃39;%ODSREP%&＃39; OR  A.TABLESPACE_NAME LIKE &＃39;%DMREP%&＃39; OR A.TABLESPACE_NAME LIKE &＃39;%BLREP%&＃39;)

   --AND B.SEGMENTSIZEINMB / A.TBSCURRENTSIZEINMB <0.5

   --AND A.TBSCURRENTSIZEINMB > 10 * 1024

   --AND a.tablespace_name LIKE &＃39;BLAP%&＃39;

  AND A.TABLESPACE_NAME NOT LIKE &＃39;%UNDO%&＃39;

ORDER BY CAPACITYUSED

;

CREATE  TABLESPACE test_data
LOGGING
DATAFILE  &＃39; /opt/oracle/OraBase/oradata/bean/TEST_DATA.dbf &＃39;  
SIZE 20480M 
AUTOEXTEND  OFF;

select file_name,file_id,tablespace_name,bytes/1024/1024 from dba_data_files order by file_name

　　SQL> alter database datafile &＃39;/home/oracle/oradata/gldb/users01.dbf&＃39; resize 32000m;

　　Database altered.

　　SQL> alter tablespace users add datafile &＃39;/home/oracle/oradata/gldb/users02.dbf&＃39; size 10000m;

　　Tablespace altered.

alter tablespace test drop datafile &＃39;E:\oracle\product\10.2.0\oradata\orcl\test02.dbf&＃39; ;

impdp &＃39;"/ as sysdba"&＃39; directory=dump_dir dumpfile=wtadmin_tcfg_20121123.dmp JOB_NAME=asu1 logfile=imp_wtadmin.log schemas=wtadmin parallel=1;