-- WITH FUNCTION
with function #sf_page_mb()return dec
as begin return1.0*page()/1024/1024; end;
select
#t.owner,-- 表的所属者(即模式)00
#t.table_name as tname,-- 表名
#t.tablespace_name as ts_name,-- 表空间名称
#sf_page_mb *table_used_space(#t.owner, #t.table_name) as total_mb,-- 表占用空间大小(已分配空间,可以理解成类似虚拟机的厚置备置零)
#sf_page_mb *table_used_pages(#t.owner, #t.table_name) as use_mb,-- 表实际使用空间大小
table_rowcount(#t.owner, #t.table_name) rowcnt -- 表的数据行记录数
from dba_tables #t
where #t.owner in('cqsoft','CQSOFT')-- 根据自己数据库环境填写实际的模式名
order by 6 desc,5 desc;
002. 三个维度汇总查询用户和模式空间大小的占用
TIPS:速查数据库中用户空间的具体占用情况,分三个维度(所有用户、单个用户、用户单模式)。
select
ur.name as user_name,-- 用户名
sch.name as schema_name,-- 模式名
sum(table_used_space(sch.name, t.name))*1.0*page()/1024/1024 as total_mb -- 空间占用总大小
from sysobjects ur
join sysobjects sch on ur.id = sch.pid and sch.type$='SCH'
join sysobjects t on t.schid = sch.id and t.subtype$='UTAB'
where ur.type$='UR' and ur.subtype$='USER'-- and ur.name not in('SYSDBA','SYS','SYSSSO','SYSAUDITOR')-- 可选项,用来排除系统预置用户
group by rollup(ur.name, sch.name)
order by 1 nulls last,2 nulls last;
003. 预览数据库所有用户分配的默认表空间
查询用户的默认数据表空间(大小)和默认索引表空间(大小)以及用户真实占用空间多大
TIPS:快速定位用户数据量大的表空间,和在无管理工具图形界面情况下查得某用户的默认表空间 。
with function #sf_page_mb()return dec
as begin return1.0*page()/1024/1024; end;
select
username as "用户名",
#sf_page_mb()*user_used_space(username) as "用户占用空间总大小(MB)",
dat_ts as "默认数据表空间名称",
#sf_page_mb()*ts_used_space(dat_ts) as "默认数据表空间大小(MB)",
ind_ts as "默认索引表空间大小(MB)",
#sf_page_mb()*ts_used_space(ind_ts) as "默认索引表空间大小(MB)"
from
(
select
o.name as username,(
select name from v$tablespace tp where o.info3 &0xFF= tp.id
) as dat_ts,-- 数据表空间
(
select
name
from
v$tablespace tp
where
decode(info3 >>16,0, o.info3 &0xFF, info3 >>16)= tp.id
) as ind_ts -- 索引表空间
from
sysobjects o
where
o.type$ ='UR'
and o.subtype$='USER'-- and o.name='U1');--------------------------------- 华丽分割线 ----------------- 新版:加入用户表空间使用上限,弥补管理工具图形界面bug(误导)
with function #sf_page_mb()return dec
as begin return1.0*page()/1024/1024; end;
select
username as "用户名",
#sf_page_mb()*user_used_space(username) as "用户占用空间总大小(MB)",
dat_ts as "默认数据表空间名称",
#sf_page_mb()*ts_used_space(dat_ts) as "默认数据表空间大小(MB)",ifnull(#sf_page_mb()* space_limit,'无限制') as "空间使用上限(MB)",
ind_ts as "默认索引表空间大小(MB)",
#sf_page_mb()*ts_used_space(ind_ts) as "默认索引表空间大小(MB)"
from
(
select
o.name as username,(
select name from v$tablespace tp where o.info3 &0xFF= tp.id
) as dat_ts,-- 数据表空间
(
select
name
from
v$tablespace tp
where
decode(info3 >>16,0, o.info3 &0xFF, info3 >>16)= tp.id
) as ind_ts ,-- 索引表空间
r.space_limit -- 空间使用上限
from
sysobjects o
left join v$resource_limit r on o.id = r.id
where
o.type$ ='UR'
and o.subtype$='USER'-- and o.name='U1');-- 修改用户使用指定表空间上限大小
alter user ZFQ DISKSPACE limit 100;-- 限定使用默认表空间100MB.
004. 快速排查位图索引与函数索引
TIPS:针对OLTP业务系统,存在位图索引的表频繁更新,无疑是毁灭性的灾难。
select
sf_get_schema_name_by_id(oi.schid) as index_owner,-- 索引所属模式
oi.name as index_name,-- 索引名称
tp.name as index_tablespace,-- 索引表空间
decode(i.type$,'BM','位图索引','函数索引') as index_type,-- 索引类型
oi.valid as valid,-- 索引是否有效
case
when (I.XTYPE &0x00010000)=0 then 'VISIBLE'else'INVISIBLE'
end as visibility,-- 索引是否可见
indexdef(i.id,1) as index_ddl -- 索引创建语句
from sysindexes i
join sysobjects oi on i.id = oi.id
join v$tablespace tp on i.groupid = tp.id
where dm_bit_test(i.xtype,2)=1
or i.type$='BM';
005. 列出数据库中所有账户与模式对应关系
TIPS:方便直观定位某些模式隶属于某账户,对于开发人员乱用账户登录操纵数据报错很有帮助。
select
sf_get_username_by_id(pid) username,listagg(name,'|') within group(order by name) schname
from
sysobjects
where
type$='SCH'
group by
sf_get_username_by_id(pid)
order by
1;
select
sf_get_schema_name_by_id(st.schid) as table_owner,-- 模式名
st.name as table_name,-- 表名
sco.name as column_name,-- 列名
sco.type$ as column_type ,-- 列类型
ident_current(sf_get_schema_name_by_id(st.schid)||'.'||st.name) as ident_current,-- 自增列当前值
ident_seed(sf_get_schema_name_by_id(st.schid)||'.'||st.name) as ident_seed,-- 种子值(起始值)
ident_incr(sf_get_schema_name_by_id(st.schid)||'.'||st.name) as ident_increment -- 增量值
from syscolumns sco
join sysobjects st on sco.id = st.id and st.subtype$='UTAB'
where bitand(sco.info2,0x0001)=1
order by 1,2;
select*from(select-- 排除默认隐藏聚集(ROWID)和系统索引
sf_get_schema_name_by_id(i.schid)as owner,-- 模式名
sf_get_tablename_by_id(pid)as table_name,-- 表名
i.name as index_name,-- 索引名
i.valid as is_valid,-- 索引有效性
indexdef(s.id,1)as index_ddl -- 索引定义DDL语句from sysobjects i -- index objectjoin sysindexes s on s.id = i.id and i.subtype$='INDEX'-- index abstractwhere dm_bit_test(s.flag,1)=0UNIONALLselect-- 主键索引(索引)、唯一约束(索引)
sf_get_schema_name_by_id(i.schid)as owner,
t.name as table_name,
i.name as index_name,
i.valid as is_valid,convert(varchar, dbms_metadata.get_ddl('INDEX', i.name, sf_get_schema_name_by_id(i.schid)))as index_ddl
from syscons c -- constraint outlinejoin sysobjects i on c.indexid = i.id and i.subtype$='INDEX'and c.type$ in('P','U')-- constraint (pimary & unique)-- index objectjoin sysobjects t on t.id = c.tableid and t.subtype$='UTAB'-- table object)-- 外层过滤条件,根据自身需求而定where owner='SYSDBA'-- 模式名--and table_name='XXXXX' ; -- 表名 orderby1,2;
select
sf_get_schema_name_by_id(t.schid)as owner,
t.name as table_name,
sc.name as constraint_name,
sc.valid as enabled,
rtrim(replace(replace(dbms_metadata.get_ddl('CONSTRAINT', sc.name, sf_get_schema_name_by_id(SC.schid)),'ADD CONS','MODIFY CONS'),'FOREIGN KEY','TO FOREIGN KEY'),';')||' WITH INDEX;'as foreign_index_ddl
from syscons c -- constraint detailjoin sysobjects sc on c.id = sc.id and c.type$='F'and sc.subtype$='CONS'-- constraint outlinejoin sysobjects t on c.tableid = t.id -- retrieve tablejoin sysindexes i on c.indexid = i.id -- index comparewhere(i.xtype &0x00000001)=1and(i.flag &0x00000003)=0x00000003;-- 虚索引bit(1) + PK bit(2)