Oracle常用sql汇总

存储过程：
往表中插入数据：
CREATE OR REPLACE procedure insertDate is
begin
for i in 1 …10 loop
INSERT INTO “TEST_DATA_200W” (“id”, “title”, “created”) VALUES (i, ‘7Cfk2f1L’, sysdate);
end loop;
end procedure;

BEGIN
procedure();
END;

查询 Oracle 版本：
select * from v$version;

select * from dba_profiles ；

select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
from v$sessiona,v$sqlarea b
where a.sql_address = b.address ；

select a.username, a.sid, b.SQL_TEXT, b.SQL_FULLTEXT
from v$sessiona,v$sqlarea b
where a.sql_address = b.address
and a.username = ‘TAPDATA’;
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$sessionb,v$process a
where
b.paddr = a.addr
and type=‘USER’ and b.username = ‘TAPDATA’
order by spid;

elect username,sql_id,program,inst_id,count(*) from gv$session group by username,sql_id,program,inst_id order by 5 ；

查询权限：
SELECT GRANTED_ROLE FROM USER_ROLE_PRIVS