Find tables that do not belong in the SYSTEM tablespace

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';

posted @   耀阳居士  阅读(164)  评论(0编辑  收藏  举报
编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示