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 返回當前節點的最頂端節點 2> CONNECT_BY_ISLEAF 判斷是否為葉子節點，如果這個節點下面有子節點，則不為葉子節點 3> LEVEL 偽列表示節點深度 4> SYS_CONNECT_BY_PATH函數顯示詳細路徑，并用“/”分隔

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';/* 系統數據庫相關查詢 */select * from user_tablespaces;select username,default_tablespace from dba_users where username='ZZS'select count(*) from user_views; --yb53 zzs 53select 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_MBFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY 1--查詢表空間的free spaceselect tablespace_name,count(*) AS extends,round(sum(bytes) / 1024 / 1024, 2) AS MB,sum(blocks) AS blocksfrom dba_free_spacegroup 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 )) USEDFROM (SELECT TABLESPACE_NAME TABLESPACE_NAME,Sum(BYTES) BYTESFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME TABLESPACE_NAME,Sum(BYTES) FREEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) BWHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME)ORDER BY Floor(10000 * USED / BYTES) DESC;

DECLAREcon number;BEGINcon :=1;LOOPDBMS_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) ascounts number;num number;begincreate 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 loopdbms_output.put_line('循環開始:');dbms_output.put_line('第'+num+'條數據');select column1into column1from (select tablename.*, rownum as con from tablename)where con = num;select column2into column2from (select tablename.*, rownum as con from tablename)where con = num;select column3into column3from (select tablename.*, rownum as con from tablename)where con = num;insert into COM_DEPARTMENTvalues(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;