查询表空间使用率:
SQL> 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;
TABLESPACE_NAME SIZE_G FREE_G USED_PCT
--------------------------------------------------------------
CUXD 31.25 0.11 99.66%
APPS_TS_TX_IDX 25.39 0.79 96.89%
APPS_TS_TX_DATA 48.83 1.57 96.79%
APPS_TS_MEDIA 29.30 2.68 90.86%
CUXX 18.55 3.56 80.79%
APPS_TS_QUEUES 17.58 4.04 77.01%
SYSTEM 17.12 4.97 70.97%
APPS_UNDOTS1 18.00 6.04 66.46%
CUXRPTX 3.91 1.83 53.24%
APPS_TS_SEED 5.86 2.84 51.57%
查看相应的数据文件:
SQL> desc dba_data_files
Name Type Nullable Default Comments
--------------- ------------- -------- ------- ---------------------------------------------------
FILE_NAME VARCHAR2(513) Y Name of the database data file
FILE_ID NUMBER Y ID of the database data file
TABLESPACE_NAME VARCHAR2(30) Y Name of the tablespace to which the file belongs
BYTES NUMBER Y Size of the file in bytes
BLOCKS NUMBER Y Size of the file in ORACLE blocks
STATUS VARCHAR2(9) Y File status: "INVALID" or "AVAILABLE"
RELATIVE_FNO NUMBER Y Tablespace-relative file number
AUTOEXTENSIBLE VARCHAR2(3) Y Autoextensible indicator: "YES" or "NO"
MAXBYTES NUMBER Y Maximum autoextensible file size in bytes
MAXBLOCKS NUMBER Y Maximum autoextensible file size in blocks
INCREMENT_BY NUMBER Y Default increment for autoextension
USER_BYTES NUMBER Y Size of the useful portion of file in bytes
USER_BLOCKS NUMBER Y Size of the useful portion of file in ORACLE blocks
ONLINE_STATUS VARCHAR2(7) Y Online status of the file
SQL> select * from dba_data_files where tablespace_name='CUXD';
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
+DATA_ERP/prod/datafile/cuxd.329.835913035 28 CUXD 5242880000 640000 AVAILABLE 28 NO 0 0 0 5232394240 638720 ONLINE
+DATA_ERP/prod/datafile/cuxd.485.837096555 36 CUXD 5242880000 640000 AVAILABLE 36 NO 0 0 0 5232394240 638720 ONLINE
+DATA_ERP/prod/datafile/cuxd.488.839960275 39 CUXD 5242880000 640000 AVAILABLE 39 NO 0 0 0 5232394240 638720 ONLINE
+DATA_ERP/prod/datafile/cuxd.489.845226969 40 CUXD 5242880000 640000 AVAILABLE 40 NO 0 0 0 5232394240 638720 ONLINE
+DATA_ERP/prod/datafile/cuxd.528.880186969 70 CUXD 5242880000 640000 AVAILABLE 70 NO 0 0 0 5232394240 638720 ONLINE
+DATA_ERP/prod/datafile/cuxd.530.884802681 72 CUXD 5242880000 640000 AVAILABLE 72 NO 0 0 0 5232394240 638720 ONLINE
+DATA_ERP/prod/datafile/cuxd.533.894392179 73 CUXD 2097152000 256000 AVAILABLE 73 NO 0 0 0 2086666240 254720 ONLINE
7 rows selected
SQL>
SQL> select file_name, bytes/1024/1024/1024 gb , tablespace_name ,AUTOEXTENSIBLE from dba_data_files where tablespace_name='CISTS_01';
FILE_NAME GB TABLESPACE_NAME AUT
-------------------------------------------------- ---------- ------------------------------ ---
+DATA_CIS/prod/datafile/cists_01.260.833127359 28.3203125 CISTS_01 YES
+DATA_CIS/prod/datafile/cists_01b.dbf 6.8359375 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01c.dbf 6.8359375 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01d.dbf 6.8359375 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01e.dbf 6.8359375 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01f.dbf 30 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.290.840709645 1.171875 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.291.840709671 1.171875 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.292.840709917 1.171875 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.293.840709921 1.171875 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.294.840709925 1.171875 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.295.840709931 1.171875 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.296.840709935 1.171875 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.297.840709941 1.171875 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.298.840709945 1.171875 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.299.840709949 1.171875 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.300.840710391 1.171875 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.301.840710401 1.171875 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.302.840710407 1.171875 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.303.840710411 4.8828125 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.304.840710415 4.8828125 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.305.840710421 4.8828125 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.306.840710429 4.8828125 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.307.840710435 4.8828125 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.308.840710441 4.8828125 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.309.840710455 4.8828125 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.345.870341291 30 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.276.870341373 30 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.346.870341413 30 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.348.870341613 30 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.349.870341643 30 CISTS_01 NO
+DATA_CIS/prod/datafile/cists_01.350.870341675 30 CISTS_01 NO
32 rows selected.
SQL>
修改数据文件大小(注:单个数据文件不要超过32G)
SQL> alter database datafile '+DATA_ERP/prod/datafile/cuxd.533.894392179' resize 7168M;
SQL>
Database altered
SQL> alter database datafile '+DATA_ERP/prod/datafile/cuxd.329.835913035' resize 7168M;
Database altered
SQL> alter database datafile '+DATA_ERP/prod/datafile/cuxd.485.837096555' resize 7168M;
Database altered
SQL> alter database datafile '+DATA_ERP/prod/datafile/cuxd.488.839960275' resize 7168M;
Database altered
SQL> alter database datafile '+DATA_ERP/prod/datafile/cuxd.489.845226969' resize 7168M;
Database altered
SQL> alter database datafile '+DATA_ERP/prod/datafile/cuxd.528.880186969' resize 7168M;
Database altered
SQL> alter database datafile '+DATA_ERP/prod/datafile/cuxd.530.884802681';
alter database datafile '+DATA_ERP/prod/datafile/cuxd.530.884802681'
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected
SQL> alter database datafile '+DATA_ERP/prod/datafile/cuxd.530.884802681' resize 7168M;
Database altered
验证修改后的数据文件大小:
SQL> select * from dba_data_files where tablespace_name='CUXD';
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
+DATA_ERP/prod/datafile/cuxd.329.835913035 28 CUXD 7516192768 917504 AVAILABLE 28 NO 0 0 0 7507804160 916480 ONLINE
+DATA_ERP/prod/datafile/cuxd.485.837096555 36 CUXD 7516192768 917504 AVAILABLE 36 NO 0 0 0 7507804160 916480 ONLINE
+DATA_ERP/prod/datafile/cuxd.488.839960275 39 CUXD 7516192768 917504 AVAILABLE 39 NO 0 0 0 7507804160 916480 ONLINE
+DATA_ERP/prod/datafile/cuxd.489.845226969 40 CUXD 7516192768 917504 AVAILABLE 40 NO 0 0 0 7507804160 916480 ONLINE
+DATA_ERP/prod/datafile/cuxd.528.880186969 70 CUXD 7516192768 917504 AVAILABLE 70 NO 0 0 0 7507804160 916480 ONLINE
+DATA_ERP/prod/datafile/cuxd.530.884802681 72 CUXD 7516192768 917504 AVAILABLE 72 NO 0 0 0 7507804160 916480 ONLINE
+DATA_ERP/prod/datafile/cuxd.533.894392179 73 CUXD 7516192768 917504 AVAILABLE 73 NO 0 0 0 7507804160 916480 ONLINE
7 rows selected
SQL>
第二个表空间修改:
SQL> select file_name, file_id,TABLESPACE_NAME,bytes from dba_data_files where tablespace_name='APPS_TS_TX_IDX';
FILE_NAME FILE_ID TABLESPACE_NAME BYTES
-------------------------------------------------------------------------------- ---------- ------------------------------ ----------
+DATA_ERP/prod/datafile/apps_ts_tx_idx.261.835913083 406 APPS_TS_TX_IDX 2097152000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.262.835913083 405 APPS_TS_TX_IDX 2097152000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.265.835913075 404 APPS_TS_TX_IDX 2097152000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.266.835913075 403 APPS_TS_TX_IDX 2097152000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.280.835913065 393 APPS_TS_TX_IDX 2097152000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.289.835913035 26 APPS_TS_TX_IDX 2097152000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.322.835913035 25 APPS_TS_TX_IDX 4194304000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.323.835913035 24 APPS_TS_TX_IDX 4194304000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.318.835912995 15 APPS_TS_TX_IDX 4194304000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.526.880186865 68 APPS_TS_TX_IDX 2097152000
10 rows selected
SQL> alter database datafile '+DATA_ERP/prod/datafile/apps_ts_tx_idx.526.880186865' resize 4000M;
Database altered
SQL> alter database datafile '+DATA_ERP/prod/datafile/apps_ts_tx_idx.261.835913083' resize 4000M;
Database altered
SQL>
SQL> alter database datafile '+DATA_ERP/prod/datafile/apps_ts_tx_idx.262.835913083' resize 4000M;
Database altered
SQL> alter database datafile '+DATA_ERP/prod/datafile/apps_ts_tx_idx.265.835913075' resize 4000M;
Database altered
SQL> alter database datafile '+DATA_ERP/prod/datafile/apps_ts_tx_idx.266.835913075' resize 4000M;
Database altered
SQL> alter database datafile '+DATA_ERP/prod/datafile/apps_ts_tx_idx.280.835913065' resize 4000M;
Database altered
SQL> alter database datafile '+DATA_ERP/prod/datafile/apps_ts_tx_idx.289.835913035' resize 4000M;
Database altered
SQL> select file_name, file_id,TABLESPACE_NAME,bytes from dba_data_files where tablespace_name='APPS_TS_TX_IDX';
FILE_NAME FILE_ID TABLESPACE_NAME BYTES
-------------------------------------------------------------------------------- ---------- ------------------------------ ----------
+DATA_ERP/prod/datafile/apps_ts_tx_idx.261.835913083 406 APPS_TS_TX_IDX 4194304000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.262.835913083 405 APPS_TS_TX_IDX 4194304000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.265.835913075 404 APPS_TS_TX_IDX 4194304000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.266.835913075 403 APPS_TS_TX_IDX 4194304000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.280.835913065 393 APPS_TS_TX_IDX 4194304000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.289.835913035 26 APPS_TS_TX_IDX 4194304000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.322.835913035 25 APPS_TS_TX_IDX 4194304000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.323.835913035 24 APPS_TS_TX_IDX 4194304000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.318.835912995 15 APPS_TS_TX_IDX 4194304000
+DATA_ERP/prod/datafile/apps_ts_tx_idx.526.880186865 68 APPS_TS_TX_IDX 4194304000
10 rows selected
注:也可以通过增加数据文件的方式
SQL> alter tablespace xxxxx add datafile 'xxxxxxxxxxxxxxxx/xxx.dbf' size 4000M;
修改数据文件为自动扩展:
SQL> alter database datafie 'xxxxxxxxxxxxxxxxxx/xxx.dbf' autoextend on next 50M;