Oracle 數(shù)據(jù)庫(kù)特殊查詢總結(jié)

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 ; 

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 返回當(dāng)前節(jié)點(diǎn)的最頂端節(jié)點(diǎn) 
2> CONNECT_BY_ISLEAF 判斷是否為葉子節(jié)點(diǎn)，如果這個(gè)節(jié)點(diǎn)下面有子節(jié)點(diǎn)，則不為葉子節(jié)點(diǎn) 
3> LEVEL 偽列表示節(jié)點(diǎn)深度 
4> SYS_CONNECT_BY_PATH函數(shù)顯示詳細(xì)路徑，并用“/”分隔

alter table taxasset add (NEXTDATE varchar2(30));
alter table tax_dep_manager modify FDDBRXM varchar2(120);
alter table test1 drop column name;

/*用戶被占用的查詢*/
select 'alter system kill session '''||sid||','||serial#||''';' from v$session where username = 'USERS';
/* 系統(tǒng)數(shù)據(jù)庫(kù)相關(guān)查詢 */
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;
--查詢表空間的總?cè)萘?
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')
|| '' "實(shí)有",
To_char(Round(FREE / 1024, 2), '99990.00')
|| 'G' "現(xiàn)有",
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;

DECLARE
con number;
BEGIN
con :=1;
LOOP
DBMS_OUTPUT.PUT_LINE(con);
con:=con+1;
EXIT WHEN con>100;
END LOOP;
DBMS_OUTPUT.PUT_LINE('完了');
END;

create or replace procedure InsertBranch(tablename in varchar2) as
counts number;
num number;
begin
create table tempdata (column1 nvarchar2,column2 nvarchar2,column3 nvarchar2);
insert tempdata 
num := 1;
select count(*) into counts from tablename;
dbms_output.put_line('數(shù)據(jù)總數(shù)'+counts);
while num = counts loop
dbms_output.put_line('循環(huán)開始:');
dbms_output.put_line('第'+num+'條數(shù)據(jù)');
select column1
into column1
from (select tablename.*, rownum as con from tablename)
where con = num;
select column2
into column2
from (select tablename.*, rownum as con from tablename)
where con = num;
select column3
into column3
from (select tablename.*, rownum as con from tablename)
where con = num;
insert into COM_DEPARTMENT
values
(brno,
brname,
upbrno,
upbrno,
'N',
null,
null,
null,
'1',
null,
'Y',
'2',
null,
null,
null,
2,
'N',
null,
null,
null,
'N',
brno,
upbrno,
null,
null,
null,
'A',
'N',
'N',
0,
0,
3,
null,
null,
null,
'0',
'0',
0,
null,
null,
null,
null,
null,
null,
null);
num := num + 1;
end loop;
end;