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;

6. loop 的使用

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;

7. 預存程序的書寫

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(‘資料總數‘+counts);
while num <= counts loop
dbms_output.put_line(‘迴圈開始:‘);
dbms_output.put_line(‘第‘+num+‘條資料‘);
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;

8.完了。

 

Oracle 資料庫特殊查詢總結