select
fs.tablespace_name "Tablespace",
SQL> SQL> SQL> SQL> SQL> SQL> 2 3 (df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
4 5 6 7 8 9 10 11 12 dba_data_files
group by
tablespace_name
) df,
(select
13 14 15 16 17 tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name;
Tablespace Used MB Free MB Total MB Pct. Free
------------------------------ ----------- ----------- ----------- ----------
SYSAUX 120,569 1,287 121,856 1
set lines 200
set lines 200
col OWNER for a30
col SEGMENT_NAME for a40
SQL> select * from (select owner, segment_name, bytes/(1024*1024*1024) from dba_segments where owner='SYS' order by bytes desc) where rownum <10;
SQL> set lines 200
SQL> col OWNER for a30
SQL> col SEGMENT_NAME for a40
GB
OWNER SEGMENT_NAME BYTES/(1024*1024*1024)
------------------------------ ---------------------------------------- ----------------------
SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 22.9033203
SYS I_WRI$_OPTSTAT_HH_ST 16.0976563
SYS SOURCE$ 5.75445557
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY 3.67773438
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY 3.66894531
SYS I_SOURCE1 3.44091797
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY 3.44042969
SYS WRI$_OPTSTAT_OPR 3.1171875
SYS IDL_UB1$ 3.0408783
9 rows selected.
SQL> SQL>
select nvl(df.tablespace_name, nvl(fs.tablespace_name,'UNKOWN')) name,
to_char(kbytes_alloc,'999,999,990') kbytes,
to_char(kbytes_alloc-nvl(kbytes_free,0),'999,999,990') used,
to_char(nvl(kbytes_free,0),'999,999,990') free,
to_char(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100,'990.9999') pct_used,
to_char(nvl(largest,0),'999,999,990') largest
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from dba_free_space
where tablespace_name='SYSAUX'
group by tablespace_name ) df,
( select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from dba_data_files
where tablespace_name='SYSAUX'
group by tablespace_name ) fs;
NAME KBYTES USED FREE PCT_USED LARGEST
------------------------------ ------------ ------------ ------------ --------- ------------
SYSAUX 119,537,664 77,692,928 41,844,736 64.9945 4,063,232
SQL> exec DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);
PL/SQL procedure successfully completed.
select
fs.tablespace_name "Tablespace",
SQL> SQL> SQL> SQL> SQL> SQL> 2 3 (df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
4 5 6 7 8 9 10 11 12 dba_data_files
group by
tablespace_name
) df,
(select
13 14 15 16 17 tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name;
Tablespace Used MB Free MB Total MB Pct. Free
-----------------------------------------------------------------------------
SYSAUX 2,707 119,149 121,856 98
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
7
SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
03-SEP-22 06.14.16.534394000 PM +05:30
SQL> select * from (select owner, segment_name, bytes/(1024*1024) from dba_segments where owner='SYS' order by bytes desc) where rownum <10;
OWNER SEGMENT_NAME BYTES/(1024*1024)
------------------------------ ---------------------------------------- -----------------
SYS SOURCE$ 5892.5625
SYS I_SOURCE1 3523.5
SYS IDL_UB1$ 3113.85938
SYS _SYSSMU23_1232623801$ 1999.125
SYS _SYSSMU17_1232623801$ 1976.125
SYS _SYSSMU7_3781448970$ 1722.1875
SYS C_OBJ# 1604.99219
SYS _SYSSMU65_3877629875$ 1409.125
SYS I_COL1 1219.30469
9 rows selected.
SQL>
SQL> select nvl(df.tablespace_name, nvl(fs.tablespace_name,'UNKOWN')) name,
2 to_char(kbytes_alloc,'999,999,990') kbytes,
to_char(kbytes_alloc-nvl(kbytes_free,0),'999,999,990') used,
to_char(nvl(kbytes_free,0),'999,999,990') free,
to_char(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100,'990.9999') pct_used,
to_char(nvl(largest,0),'999,999,990') largest
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from dba_free_space
where tablespace_name='SYSAUX'
group by tablespace_name ) df,
( select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from dba_data_files
where tablespace_name='SYSAUX'
group by tablespace_name ) fs;
3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
NAME KBYTES USED FREE PCT_USED LARGEST
------------------------------ ------------ ------------ ------------ --------- ------------
SYSAUX 119,537,664 2,699,648 116,838,016 2.2584 4,063,232
SQL> exec dbms_stats.alter_stats_history_retention(1);
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
1
SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%';
OCCUPANT_DESC SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
Server Manageability - Optimizer Statistics History 9792
SQL>
SQL> /
We reorg tables and indexes but we are unable to reclaim space to filesystem
SQL> select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE' 2 ;
'ALTERTABLE'||SEGMENT_NAME||'MOVETABLESPACESYSAUX;'
----------------------------------------------------------------------------------------------------------------
alter table WRI$_OPTSTAT_OPR_TASKS move tablespace SYSAUX;
alter table OPT_DIRECTIVE_OWN$ move tablespace SYSAUX;
alter table WRI$_OPTSTAT_SYNOPSIS_HEAD$ move tablespace SYSAUX;
alter table OPT_FINDING_OBJ$ move tablespace SYSAUX;
alter table OPT_FINDING$ move tablespace SYSAUX;
alter table WRH$_OPTIMIZER_ENV move tablespace SYSAUX;
alter table WRI$_OPTSTAT_SYNOPSIS_PARTGRP move tablespace SYSAUX;
alter table WRI$_OPTSTAT_OPR move tablespace SYSAUX;
alter table WRI$_OPTSTAT_AUX_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_IND_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_TAB_HISTORY move tablespace SYSAUX;
alter table OPT_DIRECTIVE$ move tablespace SYSAUX;
alter table WRH$_PLAN_OPTION_NAME move tablespace SYSAUX;
13 rows selected.
SQL> alter table WRI$_OPTSTAT_OPR_TASKS move tablespace SYSAUX;
Table altered.
SQL> alter table OPT_DIRECTIVE_OWN$ move tablespace SYSAUX;
alter table WRI$_OPTSTAT_SYNOPSIS_HEAD$ move tablespace SYSAUX;
alter table OPT_FINDING_OBJ$ move tablespace SYSAUX;
alter table OPT_FINDING$ move tablespace SYSAUX;
alter table WRH$_OPTIMIZER_ENV move tablespace SYSAUX;
alter table WRI$_OPTSTAT_SYNOPSIS_PARTGRP move tablespace SYSAUX;
alter table WRI$_OPTSTAT_OPR move tablespace SYSAUX;
alter table WRI$_OPTSTAT_AUX_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_IND_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_TAB_HISTORY move tablespace SYSAUX;
alter table OPT_DIRECTIVE$ move tablespace SYSAUX;
alter table WRH$_PLAN_OPTION_NAME move tablespace SYSAUX;
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
SQL>
SQL> alter index I_WRI$_OPTSTAT_H_ST rebuild online parallel (degree 14);
Index altered.
SQL> alter index I_WRI$_OPTSTAT_HH_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_OPOBJ rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_STIME rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_OPID rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_TGST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_ID rebuild online parallel (degree 14);
alter index I_OPT_DIRECTIVE_DIRID rebuild online parallel (degree 14);
alter index WRH$_PLAN_OPTION_NAME_PK rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_SYNOPHEAD rebuild online parallel (degree 14);
alter index WRH$_OPTIMIZER_ENV_PK rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_SYNOPPARTGRP rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_STIME rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_AUX_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_IND_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_TAB_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild online parallel (degree 14);
alter index I_OPT_DIRECTIVE_OWN# rebuild online parallel (degree 14);
alter index I_OPT_DIRECTIVE_DIROWN# rebuild online parallel (degree 14);
alter index I_OPT_FINDING_OBJ_ID_OBJ_TYPE rebuild online parallel (degree 14);
alter index I_OPT_FINDING_F_ID rebuild online parallel (degree 14);
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select di.index_name,di.index_type,di.status from dba_indexes di , dba_tables dt
where di.tablespace_name = 'SYSAUX'
and dt.table_name = di.table_name
and di.table_name like '%OPT%'
order by 1 asc 2 3 4 5
6 /
INDEX_NAME
----------------------------------------------------------------------------------------------------------------
INDEX_TYPE STATUS
--------------------------- --------
I_OPT_DIRECTIVE_DIRID
NORMAL VALID
I_OPT_DIRECTIVE_DIROWN#
NORMAL VALID
I_OPT_DIRECTIVE_OWN#
NORMAL VALID
I_OPT_FINDING_F_ID
NORMAL VALID
I_OPT_FINDING_OBJ_ID_OBJ_TYPE
NORMAL VALID
I_WRI$_OPTSTAT_AUX_ST
FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST
FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_HH_ST
FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST
FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_H_ST
FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_IND_OBJ#_ST
FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_IND_ST
FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_OPR_ID
NORMAL VALID
I_WRI$_OPTSTAT_OPR_STIME
FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_OPR_TASKS_OPID
NORMAL VALID
I_WRI$_OPTSTAT_OPR_TASKS_OPOBJ
NORMAL VALID
I_WRI$_OPTSTAT_OPR_TASKS_STIME
FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_OPR_TASKS_TGST
NORMAL VALID
I_WRI$_OPTSTAT_SYNOPHEAD
NORMAL VALID
I_WRI$_OPTSTAT_SYNOPPARTGRP
NORMAL VALID
I_WRI$_OPTSTAT_TAB_OBJ#_ST
FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_TAB_ST
FUNCTION-BASED NORMAL VALID
SYS_IL0000551866C00008$$
LOB VALID
SYS_IL0000552501C00016$$
LOB VALID
WRH$_OPTIMIZER_ENV_PK
NORMAL VALID
WRH$_PLAN_OPTION_NAME_PK
NORMAL VALID
26 rows selected.
SQL>
SQL> select nvl(df.tablespace_name, nvl(fs.tablespace_name,'UNKOWN')) name,
to_char(kbytes_alloc,'999,999,990') kbytes,
2 to_char(kbytes_alloc-nvl(kbytes_free,0),'999,999,990') used,
to_char(nvl(kbytes_free,0),'999,999,990') free,
to_char(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100,'990.9999') pct_used,
to_char(nvl(largest,0),'999,999,990') largest
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from dba_free_space
where tablespace_name='SYSAUX'
group by tablespace_name ) df,
( select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from dba_data_files
where tablespace_name='SYSAUX'
group by tablespace_name ) fs;
3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
NAME KBYTES USED FREE PCT_USED LARGEST
------------------------------ ------------ ------------ ------------ --------- ------------
SYSAUX 119,537,664 2,672,448 116,865,216 2.2357 4,063,232
SQL> set lines 113
set pages 10000
col TSname heading 'TSpace|Name|'
col TSname format a6
col FileID heading 'Data|File|ID'
col FileID format 9999
col FileName heading 'Data|File|'
col FileName format a100
SELECT
tablespace_name as "TSname",
file_id as "FileID",
file_name as "FileName"
FROM dba_data_files
WHERE tablespace_name = 'SYSAUX'
;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7
TSpace Data Data
Name File File
ID
------ ----- ----------------------------------------------------------------------------------------------------
SYSAUX 11 /u02/SUPPDB_DATA/datafiles/SUPPDB/datafile/o1_mf_sysaux_kbjjdo7m_.dbf
SYSAUX 18 /u02/SUPPDB_DATA/datafiles/SUPPDB/datafile/o1_mf_sysaux_kbjjgysw_.dbf
SYSAUX 45 /u02/SUPPDB_DATA/datafiles/SUPPDB/datafile/o1_mf_sysaux_kbjkpx8h_.dbf
SYSAUX 50 /u02/SUPPDB_DATA/datafiles/SUPPDB/datafile/o1_mf_sysaux_kbjlg0l3_.dbf
SYSAUX 51 /u02/SUPPDB_DATA/datafiles/SUPPDB/datafile/o1_mf_sysaux_kbjlp05h_.dbf
SQL>
SQL> alter database datafile '/u02/SUPPDB_DATA/datafiles/SUPPDB/datafile/o1_mf_sysaux_kbjkpx8h_.dbf' resize 10G;
alter database datafile '/u02/SUPPDB_DATA/datafiles/SUPPDB/datafile/o1_mf_sysaux_kbjkpx8h_.dbf' resize 10G
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value