Oracle 数据库特殊查询总结

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 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 ;

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( '数据总数' +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 ;