1一些sql

查看所有表空间大小：
select * from Dba_Tablespaces;
查看所有用户
SELECT * FROM ALL_USERS;
查看用户状态
select username,account_status from dba_users;
用户状态种类
select * from user_astatus_map;
查看是否开启归档模式
select name,log_mode from v$database;
查看是否开启自增
select tablespace_name,file_name,autoextensible from dba_data_files where autoextensible='YES';
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;

SELECT sysdate from dual 返回当前系统时间

查询游标数
select count(*) from v$open_cursor；

11g 不能导出空表

select 'alter table ' || table_name || ' allocate extent(size 64k);' sql_text,
table_name,
tablespace_name
from user_tables
where table_name not in
(select segment_name from user_segments where segment_type = 'TABLE');

查询 包含 ROOTORGAN的表 不包括SYSORGAN

select TABLE_NAME, COLUMN_NAME, DATA_TYPE
from USER_TAB_COLS
where COLUMN_NAME = 'ROOTORGAN'
and TABLE_NAME != 'SYSORGAN'

select 'update ' || TABLE_NAME || ' t set t.rootorgan=1;' sql_text,TABLE_NAME, COLUMN_NAME, DATA_TYPE
from USER_TAB_COLS
where COLUMN_NAME = 'ROOTORGAN'
and TABLE_NAME != 'SYSORGAN'

查询当前PGA 总量

select sum(PGA_ALLOC_MEM)/1024/1024 M from V$process

两张表 根据id 批量更新某一列
merge into dailypadinfo t
USING dailypadinfoloa a
on (t.orderby = a.oid)
when matched then
update set t.padoriginoid = a.padoriginoid

update dailypadinfoloa c set c.padtouseroid =(select b.oid from sysuser b where c.padtouseroid=b.nativeplace )

查询正在执行的sql
select b.sid sessionID,
b.username 用户名，b.serial#,
spid,
paddr,
sql_text 正在执行的sql
from v$process a, v$session b, v$sqlarea c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value

创建序列
create sequence sql_id;

查询起始值
select sql_id.nextval from dual;