Question My SYSTEM tablespace looks too large and I want to see if I have non system objects in by SYSTEM tablespace. What is the script to find objects that do not belong in the SYSTEM tablespace?
Answer: This script will display all tables and indexes that do not belong in the SYSTEM tablespace. Table can wind up in the SYSTEM tablespace when the user ID has SYSTEM specified for the default_tablespace_name and the tablespace_name is not specified at table/index creation time,
select
owner,
object_type,
table_name
from
dba_tables
where
owner not in ('SYS','SYSTEM')
and
tablespace_name = 'SYSTEM';
select
owner,
object_type,
index_name
from
dba_indexes
where
owner not in ('SYS','SYSTEM')
and
tablespace_name = 'SYSTEM';