Oracle 特殊查詢技巧彙總：遞迴查詢、表結構操作、表空間監控、Loop 與存儲過程

一、遞迴階層查詢

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;

偽欄位說明：

• CONNECT_BY_ROOT：根節點值
• CONNECT_BY_ISLEAF：1=葉節點，0=非葉節點
• LEVEL：層級深度（根=1）
• SYS_CONNECT_BY_PATH：從根到目前節點的路徑

二、表結構 DDL 操作

-- 新增欄位
alter table taxasset add (nextdate varchar2(30));

-- 修改欄位長度
alter table tax_dep_manager modify fddbrxm varchar2(120);

-- 刪除欄位
alter table test1 drop column name;

三、系統監控查詢

1. 找出被鎖定的 Session 並產生 Kill 指令

select 'alter system kill session '''||sid||','||serial#||''';'
from v$session
where username = 'USERS';

2. 表空間使用率查詢（多種寫法）

寫法 A：

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 tablespace_name,
    round(sum(bytes)/1024/1024,2) tot_grootte_mb
  from sys.dba_data_files
  group by tablespace_name
) d
where d.tablespace_name = f.tablespace_name
order by 1;

寫法 B（簡易版）：

select tablespace_name,
  count(*) extends,
  round(sum(bytes)/1024/1024,2) mb,
  sum(blocks) blocks
from dba_free_space
group by tablespace_name;

四、PL/SQL 流程控制

1. LOOP 迴圈範例

declare
  v_con number := 1;
begin
  loop
    dbms_output.put_line(v_con);
    v_con := v_con + 1;
    exit when v_con > 100;
  end loop;
  dbms_output.put_line('完了');
end;
/

2. 存儲過程範本（帶游標與動態 SQL）

create or replace procedure insert_branch(p_tablename in varchar2) as
  v_counts number;
  v_num    number := 1;
  v_col1   varchar2(100);
  v_col2   varchar2(100);
  v_col3   varchar2(100);
begin
  -- 動態建立暫存表（示範）
  execute immediate 'create global temporary table tempdata (col1 varchar2(100), col2 varchar2(100), col3 varchar2(100)) on commit preserve rows';

  select count(*) into v_counts from user_tables where table_name = upper(p_tablename);

  while v_num <= v_counts loop
    dbms_output.put_line('循環開始: 第 ' || v_num || ' 條資料');

    -- 動態查詢第 N 筆（示範用 rownum，實務建議用 PK）
    execute immediate 'select col1 from (select t.*, rownum rn from ' || p_tablename || ' t) where rn = :1'
      into v_col1 using v_num;
    execute immediate 'select col2 from (select t.*, rownum rn from ' || p_tablename || ' t) where rn = :1'
      into v_col2 using v_num;
    execute immediate 'select col3 from (select t.*, rownum rn from ' || p_tablename || ' t) where rn = :1'
      into v_col3 using v_num;

    -- 插入目標表（略）
    v_num := v_num + 1;
  end loop;
end;
/

五、表空間管理完整腳本庫

-- 建立暫存表空間
create temporary tablespace zfmi_temp
tempfile 'D:\oracle\oradata\zfmi\zfmi_temp.dbf'
size 32m autoextend on next 32m maxsize 2048m extent management local;

-- 建立資料表空間
create tablespace zfmi logging
datafile 'D:\oracle\oradata\zfmi\zfmi.dbf'
size 100m autoextend on next 32m maxsize 2048m extent management local;

-- 刪除使用者（級聯刪除物件）
drop user zfmi cascade;

-- 刪除表空間（含資料檔與外鍵）
drop tablespace zfmi including contents and datafiles cascade constraints;

-- 建立使用者並指定表空間
create user zfmi identified by zfmi
default tablespace zfmi temporary tablespace zfmi_temp;

-- 授權
grant dba to zfmi;
grant connect, resource to zfmi;

六、匯入匯出

# 完整匯出
exp system/manager@TEST file=d:daochu.dmp full=y

# 結構匯出
exp system/manager@TEST file=d:daochu.dmp owner=(system,sys)

# 指定表匯出
exp aichannel/aichannel@TESTDB2 file=d:datanewsmgnt.dmp tables=(inner_notify,notify_staff_relat)

# 條件匯出
exp system/manager@TEST file=d:daochu.dmp tables=(table1) query="where filed1 like '00%'"

# 匯入（ignore=y 忽略已存在錯誤）
imp system/manager@TEST file=d:daochu.dmp
imp aichannel/aichannel@HUST full=y file=d:datanewsmgnt.dmp ignore=y

七、改變表擁有者

建立 import.par：

FROMUSER=TGPMS
TOUSER=TGPMS2
ROWS=Y
INDEXES=Y
GRANTS=Y
CONSTRAINTS=Y
BUFFER=409600
file=/backup/ctgpc_20030623.dmp
log=/backup/import_20030623.log

執行：imp parfile=/filepath/import.par