Oracle 常用SQL

1、查看表空间使用情况等

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY F.TABLESPACE_NAME;

2、创建bigfile类型的表空间

CREATE BIGFILE TABLESPACE big_tables_10e
DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\big_tables_10e.dat'
SIZE 500M AUTOEXTEND ON NOLOGGING;

3、创建表空间

CREATE TABLESPACE hotel
DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\hotel.dbf' 
SIZE 500M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED NOLOGGING 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

4、查看表空间

select tablespace_name, logging, force_logging, bigfile from dba_tablespaces;

5、删除表空间

Alter tablespace t1 offline;
DROP TABLESPACE t1 INCLUDING CONTENTS AND DATAFILES;

6、创建用户，分配角色

create user u1 identified by u1 default tablespace t1;
grant connect, resource, dba to user;
alter user u1 default tablespace t2;

7、查看被锁对象所在的会话

select t2.username,t2.sid,t2.serial#,t2.logon_time 
from v$locked_object t1,v$session t2 
where t1.session_id=t2.sid order by t2.logon_time;

8、杀掉会话

alter system kill session '129,112';

9、查看客户端会话

select * from v$session;

10、查看长操作

select * from v$session_longops;

11、查看日志

select * from v$log;

12、当前session正在执行的语句

select a.SID,a.USERNAME,a.machine,a.TERMINAL,b.PIECE,b.SQL_TEXT
from v$session a,
     v$sqltext b
where b.ADDRESS = decode(a.SQL_HASH_VALUE,0,a.PREV_SQL_ADDR,a.SQL_ADDRESS)
  and a.status = 'ACTIVE'
  and user# >0
order by a.SQL_ADDRESS,b.PIECE;

13、查看重做日志

select * from v$logfile;

14、追加重做日志

alter database add logFile group 4 '/home/oracle/app/oracle/oradata/orcl/redo04.log' size 500M;

15、更换表所在的表空间

alter table t move tablespace ts;

16、向表空间追加数据文件

ALTER TABLESPACE ts
ADD DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\big_tables_10e.dat'  
SIZE 500M AUTOEXTEND ON;