表空间优化

来源：互联网发布：嵌入式软件测试工具编辑：程序博客网时间：2024/05/21 09:04

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       YES11 rows selected.SQL>

SQL> set pagesize 999SQL> COL SIZE_G FOR A15COL FREE_G FOR A15COL USED_PCT FOR A10COL TABLESPACE_NAME FOR A30SELECT 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_pctFROM   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) fWHERE  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%SYSAUX11.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%UNDOTBS131.6625.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     NO12 rows selected.SQL>

0 0