表空间优化

SQL> select file_name, bytes/1024/1024/1024 gb , tablespace_name ,AUTOEXTENSIBLE from dba_data_files;

FILE_NAME							     GB TABLESPACE_NAME 	       AUT
------------------------------------------------------------ ---------- ------------------------------ ---
/u01/oracle/oradata/appserv/users01.dbf 		     .004882813 USERS			       YES
/u01/oracle/oradata/appserv/undotbs01.dbf		     31.6601563 UNDOTBS1		       YES
/u01/oracle/oradata/appserv/sysaux01.dbf		     11.4648438 SYSAUX			       YES
/u01/oracle/oradata/appserv/system01.dbf		     .771484375 SYSTEM			       YES
/u01/oracle/oradata/appserv/szgas_cc.dbf		     6.77874756 SZGAS_CC_DATA		       YES
/u01/oracle/oradata/appserv/szgas_km.dbf			      1 SZGAS_KM_DATA		       YES
/u01/oracle/oradata/appserv/szgas_et.dbf			      1 SZGAS_ET_DATA		       YES
/u01/oracle/oradata/appserv/c6_et.dbf				      2 C6_ET			       YES
/u01/oracle/oradata/appserv/szgas_stat.dbf		     29.7836914 SZGAS_STAT_DATA 	       YES
/u01/oracle/oradata/appserv/szgas_cc_test.dbf			      1 SZGAS_CC_TEST_DATA	       YES
/u01/oracle/oradata/appserv/test_data.dbf			    .75 TEST_DATA		       YES

11 rows selected.

SQL>

SQL> set pagesize 999
SQL> COL SIZE_G FOR A15
COL FREE_G FOR A15
COL USED_PCT FOR A10
COL TABLESPACE_NAME FOR A30
SELECT d.tablespace_name,
       to_char(nvl(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.00') size_g,
       to_char(nvl(f.bytes, 0) / 1024 / 1024 / 1024, '99,999,990.00') free_g,
       to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0), '990.00') || '%' used_pct
FROM   dba_tablespaces d,
       (SELECT tablespace_name, SUM(bytes) bytes
        FROM   dba_data_files
        GROUP  BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes
        FROM   dba_free_space
        GROUP  BY tablespace_name) f
WHERE  d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = f.tablespace_name(+)
       AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
ORDER  BY 4 DESC;
SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15  

TABLESPACE_NAME 	               SIZE_G	       FREE_G  USED_PCT
------------------------------ --------------- --------------- ----------
SYSTEM					 0.77		 0.02	 97.90%
SYSAUX					11.46		 0.55	 95.22%
SZGAS_CC_DATA				 6.78		 0.37	 94.52%
SZGAS_STAT_DATA 			29.78		 1.92	 93.57%
TEST_DATA				 0.75		 0.06	 92.40%
USERS					 0.00		 0.00	 27.50%
UNDOTBS1				31.66		25.80	 18.52%
SZGAS_KM_DATA				 1.00		 0.95	  5.13%
SZGAS_ET_DATA				 1.00		 0.96	  3.55%
C6_ET					 2.00		 1.94	  3.02%
SZGAS_CC_TEST_DATA			 1.00		 1.00	  0.10%

11 rows selected.

SQL> 

SQL> alter tablespace SZGAS_STAT_DATA add datafile  '/u01/oracle/oradata/appserv/szgas_stat1.dbf' size 5120M;

Tablespace altered.

SQL> 

SQL> alter database datafile '/u01/oracle/oradata/appserv/szgas_stat.dbf' autoextend off;


Database altered.


SQL> 

SQL> alter database datafile '/u01/oracle/oradata/appserv/undotbs01.dbf'  autoextend off;


Database altered.


SQL> 

SQL>  select file_name, bytes/1024/1024/1024 gb , tablespace_name ,AUTOEXTENSIBLE from dba_data_files;

FILE_NAME						   GB TABLESPACE_NAME		     AUT
-------------------------------------------------- ---------- ------------------------------ ---
/u01/oracle/oradata/appserv/users01.dbf 	   .004882813 USERS			     YES
/u01/oracle/oradata/appserv/undotbs01.dbf	   31.6601563 UNDOTBS1			     NO
/u01/oracle/oradata/appserv/sysaux01.dbf	   11.6210938 SYSAUX			     YES
/u01/oracle/oradata/appserv/system01.dbf	   .771484375 SYSTEM			     YES
/u01/oracle/oradata/appserv/szgas_cc.dbf	   6.77874756 SZGAS_CC_DATA		     YES
/u01/oracle/oradata/appserv/szgas_km.dbf		    1 SZGAS_KM_DATA		     YES
/u01/oracle/oradata/appserv/szgas_et.dbf		    1 SZGAS_ET_DATA		     YES
/u01/oracle/oradata/appserv/c6_et.dbf			    2 C6_ET			     YES
/u01/oracle/oradata/appserv/szgas_stat.dbf	   30.2836914 SZGAS_STAT_DATA		     NO
/u01/oracle/oradata/appserv/szgas_cc_test.dbf		    1 SZGAS_CC_TEST_DATA	     YES
/u01/oracle/oradata/appserv/test_data.dbf		.8125 TEST_DATA 		     YES
/u01/oracle/oradata/appserv/szgas_stat1.dbf		    5 SZGAS_STAT_DATA		     NO

12 rows selected.

SQL>