国产Oscar数据库实用SQL-随时更新

#查询用户会话信息

SELECT * FROM V_SYS_SESSIONS;

#获取schame下所有表名

select TABLE_NAME from all_tables WHERE OWNER =‘schamename’;

#获取表结构

SELECT *  where owner='schamename' AND TABLE_NAME = 'abctest';

#获取主键

select col.column_name from user_constraints con,user_cons_columns col where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.table_name='abctest';

#神通Oscar数据库查询锁表

select b.owner,b.object_name,a.sid,a.lmode from v$lock a,dba_objects b where b.object_id = a.XID;

#查询表空间使用情况

SELECT T.TSNAME AS TSNAME,

D.PATH,

COUNT(*) "File Count",

SUM(D.SIZE) / 1048576 AS "Size(MB)",

SUM(D.FREESIZE) / 1048576 AS "Free Size(MB)",

(SUM(D.SIZE) - SUM(D.FREESIZE)) / 1048576 AS "Used Size(MB)"

FROM SYS_TABLESPACE T

LEFT JOIN V_SYS_DATAFILE_INFO D

ON T.TSID = D.TABLESPACEID

GROUP BY T.TSNAME,D.PATH;

#查询各节点表空间使用情况

execute direct on all 'SELECT T.TSNAME AS Schema, SUM(D.SIZE) / 1048576 AS "Size(MB)", SUM(D.FREESIZE) / 1048576 AS "Free Size(MB)", (SUM(D.SIZE) - SUM(D.FREESIZE)) / 1048576 AS "Used Size(MB)" FROM SYS_TABLESPACE T,V_SYS_DATAFILE_INFO D where T.TSID = D.TABLESPACEID and T.TSNAME=''STLTS'' GROUP BY T.TSNAME';

#查询数据文件使用情况

SELECT T.TSNAME as "NAME",

PATH,

"CREATIONTIME" as "Creation Time",

"NEXTSIZE" / 1048576 as "Next Size(MB)",

"MAXSIZE" / 1048576 AS "Max Size(MB)",

"SIZE" / 1048576 AS "Size(MB)",

FREESIZE / 1048576 AS "Free Size(MB)",

("SIZE" - FREESIZE) / 1048576 AS "Used Size(MB)",

("SIZE" - FREESIZE) * 100.0 / SIZE AS "Used RATIO"

FROM V_SYS_DATAFILE_INFO, SYS_TABLESPACE T

WHERE T.TSID = TABLESPACEID;

#新主线查询数据文件使用情况

SELECT T.TSNAME as "NAME",

PATH,

"CREATIONTIME" as "Creation Time",

"NEXTSIZE" / 1048576 as "Next Size(MB)",

"MAXSIZE" / 1048576 AS "Max Size(MB)",

"SIZE" / 1048576 AS "Size(MB)",

FREESIZE / 1048576 AS "Free Size(MB)",

("SIZE" - FREESIZE) / 1048576 AS "Used Size(MB)",

("SIZE" - FREESIZE) * 100.0 / SIZE AS "Used RATIO"

FROM V_SYS_DATAFILE_INFO, SYS_TABLESPACE T

WHERE T.TSID = TABLESPACEID;

#查询日志文件使用情况

SELECT PATH,

"CREATIONTIME" as "Creation Time",

"INITSIZE" / 1048576 as "Init Size(MB)",

"REALSIZE" / 1048576 as "Real Size(MB)",

"NEXTSIZE" / 1048576 as "Next Size(MB)",

"MAXSIZE" / 1048576 AS "Max Size(MB)",

"ISACTIVE" AS "Is Active)",

("REALSIZE" * (100-"USAGERATIO")/100) / 1048576 AS "Free Size(MB)",

("REALSIZE" * "USAGERATIO"/100) / 1048576 AS "Used Size(MB)",

"USAGERATIO" AS "Used RATIO"

FROM V_SYS_LOGFILE_INFO;

#查询普通表空间占用情况

select u.USENAME, c.relname, s.size, ts.tsname

from sys_class c, v_segment_info s, sys_tablespace ts, v_sys_user u

where c.oid = s.relid

and c.relname='MM_T_GW_NORATE_17_201107'

and s.fileid = ts.tsid

and u.USESYSID = c.RELOWNER

and u.USENAME='TWBTEST'

order by s.size;

#查询表分区名

select * from V_SYS_PARTITION_INFO_EX where RELNAME='T_YD_UE_17_201109';

8.--查询分区表的总大小

select sum(SIZE) from V_SYS_PARTITION_INFO_EX where RELNAME='T_YD_NORATE_17_201109';

#查询当前Session状态

select "SESSION ID", "APPNAME", "CURRENT SQL", "LAST SQL" from v_sys_sessions where "LOGON USER" != 'INVALID USER';

#查询当前Session诊断信息

select w.SESSION_ID,w.EVENT_NAME, s."SESSION ID", s."APPNAME", s."MACHINE", s."LOGONTIME", s."TOTAL PORTAL", s."SQL COUNT", s."CURRENT SQL", s."LAST SQL" from v_sys_sessions s, V$SESSION_WAIT w where s."SESSION ID"=w.SESSION_ID;

select w.SESSION_ID,w.EVENT_NAME, s."SESSION ID", s."APPNAME", s."MACHINE", s."LOGONTIME", s."TOTAL PORTAL", s."SQL COUNT", s."CURRENT SQL", s."LAST SQL" from v_sys_sessions s, V$SESSION_WAIT w where s."SESSION ID"=w.SESSION_ID and s."APPNAME"<>'java.exe';

select w.SESSION_ID,w.EVENT_NAME, s."SESSION ID", s."APPNAME", s."MACHINE", s."LOGONTIME", s."TOTAL PORTAL", s."SQL COUNT", s."CURRENT SQL", s."LAST SQL" from v_sys_sessions s, V$SESSION_WAIT w where s."SESSION ID"=w.SESSION_ID and s."APPNAME"='insert_db';

#查看等待链

select * from V$WAIT_CHAINS;

--kill掉session id

kill session 631 abort;

alter tablespace stlts default nologging;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

#列与字符串的链接

select b.called_number || ',' || sum(a.total_due_amount)/1000 from t_sms_13_201202 a,call b where a.called_number=b.CALLING_NUMBER and record_type=0 group by b.called_number;

#查询表定义

select SYS_GET_TABLEDEF from v_sys_table where tablename='XXX';

select SYS_GET_TABLEDEF((SELECT OID from sys_class where relname='TEST_02'));

#查询计划

create table test(tc1 int) partition by hash(tc1) partitions 4;

explain select * from test;

#水平分区查询

--根据分区键值查询

select count(*) from bill partition for('2010-01-01');

--根据分区名查询

select * from t1 partition(分区名);

#查询依赖

查询相关表的试图

select * from v_sys_views where DEFINITION like '%T_GW_NORATE_88_201112%'

select c.relname,d.* from sys_depend d,sys_class c where d.OBJID=c.oid

#查询数据库信息

SELECT * FROM V_SYS_DATABASE_INFO;

#查询表的级联

select r.relname, r.REFOBJID, c2.relname "REF RelName", r.OBJID "REF OID" from (select c.relname relname,d.* from sys_depend d,sys_class c where d.REFOBJID=c.oid and c.relname='T_GW_UE_18_201204

') r, sys_class c2 where c2.oid=(r.OBJID::integer-1);

#查询阻塞语句sid

select s.prev_sql, 'kill session ' || s.sid || ' abort ;', w.pid, w.BLOCKER_PID from v$wait_chains w, v$session s where BLOCKER_PID is null and s.sid = w.SESSION_ID;

#查询用户默认表空间

select defaulttablespace as "default tablespace" from sys_shadow where usename='SYSDBA';

select SID from V$SESSION where LOGON_USER<>'' and CURRENT_SQL ='' and PREV_SQL_EXEC_START<=now()::timestamp-1/48;

select BUFFERS*8/1024 total,FREE_BUFFERS*8/1024 free,DIRTY_BUFFERS*8/1024 dirty from V$BUFFER_STATISTICS ;

#查询失效索引

execute direct on all 'select * from V_SYS_IND_SUBPARTITIONS where IDXUSABLE=''f''';

#不同模式表分区显示

select d.NSPNAME || '.' || c.relname,a.* from v_sys_tab_partitions a,sys_tabpart b , sys_class c,SYS_NAMESPACE d where a.oid = b.oid and b.BO=c.oid and c.RELNAMESPACE = d.oid;