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