oracle快捷查询语句

--查看dblink

select* from dba_db_links

 

--查看表占用空间大小

SELECTsegment_name AS TABLENAME,BYTES||'B' FROM user_segments order by BYTES desc

 

查看实例名称(sid)：

selectinstance_name from V$instance; 

--CLOB内容转字符串

to_char(DBMS_LOB.substr(responsexml,20))responsexml

-----查重

select* from table a where a.rowid in(

selectt.rowid from table1 t, table2 n

wheret.COL01||t.COL02=n.COL01||n.COL02

groupby t.rowid having count(1) >1)

---扩展表空间sql

selectfile_name,blocks,tablespace_name

fromdba_data_files where tablespace_name='TBS_AA'

 

ALTERTABLESPACE TBS_YX

ADDDATAFILE '/SA/FDSG/oradata/DB/datafile/tbs_AA_01.dbf'

SIZE8G

--子父节点结构sql查询

select A,pA 

from TABLE  

startwith A=pA  

connect by nocycle prior A=pA  

orderby sortorder;