oracle数据库特殊查询

张兴康

1. 查询本节点及本节点以下的所有节点：
select * from table1 c start with c.p_id='0000000' connect by prior c.id=c.p_id and c.use_yn='Y' order by id ;


2. 查询节点中所有的层级关系
SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",CONNECT_BY_ROOT DEPNAME "ROOT",CONNECT_BY_ISLEAF "ISLEAF",LEVEL ,SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH" FROM DEP START WITH UPPERDEPID IS NULL CONNECT BY PRIOR DEPID = UPPERDEPID;
1> CONNECT_BY_ROOT 返回当前节点的最顶端节点
2> CONNECT_BY_ISLEAF 判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点
3> LEVEL 伪列表示节点深度
4> SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔


3. 对数据库表结构的操作
alter table taxasset add (NEXTDATE varchar2(30));
alter table tax_dep_manager modify FDDBRXM varchar2(120);
alter table test1 drop column name;


4. 其他查询
/*用户被占用的查询*/
select 'alter system kill session '''||sid||','||serial#||''';' from v$session where username = 'USERS';

/* 系统数据库相关查询 */
select * from user_tablespaces;
select username,default_tablespace from dba_users where username='ZZS'
select count(*) from user_views; --yb53 zzs 53
select count(*) from user_tables; --yb413 zzs 413

--查询表空间使用情况
SELECT Upper(F.TABLESPACE_NAME) "表空间名",D.TOT_GROOTTE_MB "表空间大小(M)",D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')|| '%' "使用比",F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME,Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1


--查询表空间的free space
select tablespace_name,
count(*) AS extends,
round(sum(bytes) / 1024 / 1024, 2) AS MB,
sum(blocks) AS blocks
from dba_free_space
group BY tablespace_name;


--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;


--表空间容量查询
SELECT TABLESPACE_NAME "表空间",
To_char(Round(BYTES / 1024, 2), '99990.00')
|| '' "实有",
To_char(Round(FREE / 1024, 2), '99990.00')
|| 'G' "现有",
To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')
|| 'G' "使用",
To_char(Round(10000 * USED / BYTES) / 100, '99990.00')
|| '%' "比例"
FROM (SELECT A.TABLESPACE_NAME TABLESPACE_NAME,
Floor(A.BYTES / ( 1024 * 1024 )) BYTES,
Floor(B.FREE / ( 1024 * 1024 )) FREE,
Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
FROM (SELECT TABLESPACE_NAME TABLESPACE_NAME,
Sum(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME TABLESPACE_NAME,
Sum(BYTES) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME)
ORDER BY Floor(10000 * USED / BYTES) DESC;

张兴康

陈晓龙

陈晓龙

陈晓龙

张兴康