oracle常用管理sql

--oracle监控函数

--查看所有表空间利用率
SELECT 
A.A1 表空间名称, C.C2 类型, C.C3 区管理, 
B.B2/1024/1024 表空间大小M, (B.B2-A.A2)/1024/1024 已使用M, 
SUBSTR((B.B2-A.A2)/B.B2*100,1,5) 利用率
FROM
(SELECT TABLESPACE_NAME A1, SUM(NVL(BYTES,0)) A2 FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME B1,SUM(BYTES) B2 FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B,
(SELECT TABLESPACE_NAME C1,CONTENTS C2,EXTENT_MANAGEMENT C3 FROM DBA_TABLESPACES) C
WHERE A.A1=B.B1 AND C.C1=B.B1;

--查看所有自建用户
SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE ACCOUNT_STATUS='OPEN';

--查看所有表空间
SELECT TABLESPACE_NAME, SUM(BYTES)/(1024*1024) AS MBYTES, SUM(BLOCKS) FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME;

--查看所有空闲表空间
SELECT TABLESPACE_NAME, SUM(BYTES)/(1024*1024) AS FREEMBYTES, SUM(BLOCKS) FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME; 

--查看所有表空间是否自动扩展
SELECT FILE_NAME,BYTES/1024/1024 MB,AUTOEXTENSIBLE,TABLESPACE_NAME FROM DBA_DATA_FILES;

--查询所有用户及创建时间
SELECT * FROM ALL_USERS;


--查看当前用户连接
SELECT * FROM V$SESSION WHERE STATUS='ACTIVE' AND SCHEMANAME!='SYS';

--查看当前用户权限
SELECT * FROM SESSION_PRIVS;

--查询当前用户函数和储存过程
SELECT * FROM USER_SOURCE;

--查询当前用户哪个表的记录数最多
SELECT TABLE_NAME, TABLESPACE_NAME, NUM_ROWS, BLOCKS FROM USER_ALL_TABLES ORDER BY NUM_ROWS DESC;

--查看当前用户哪个表占的空间最大
SELECT SEGMENT_NAME,TABLESPACE_NAME,BYTES/1024/1024 AS MBYTES,BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE' ORDER BY MBYTES DESC;

--查看坐标投影系
SELECT * FROM SDE.SPATIAL_REFERENCES;

--删除空间索引
DROP INDEX TABLENAME_SPATIAL_IDX;

--
SELECT * FROM USER_SDO_GEOM_METADATA

--删除注册空间信息
DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME='TABLENAME';

--删除sde注册空间信息
--echo y | sdelayer -o delete -l TABLENAME,SPATIALCOLUMN -u usr -p pwd

DELETE FROM SDE.TABLE_REGISTRY T WHERE T.OWNER=='USERNAME'
DELETE FROM SDE.COLUMN_REGISTRY T1 WHERE T1.OWNER=='USERNAME'
DELETE FROM SDE.GEOMETRY_COLUMNS T2 WHERE T2.F_TABLE_SCHEMA=='USERNAME'
DELETE FROM SDE.GDB_USERMETADATA T3 WHERE T3.OWNER=='USERNAME'
DELETE FROM SDE.LAYERS T4 WHERE T4.OWNER=='USERNAME'

--清空指定名称的表交释放空间
BEGIN 
  FOR T IN (SELECT TABLE_NAME FROM USER_TABLES WHERE UPPER(TABLE_NAME) LIKE 'SMR_%'OR UPPER(TABLE_NAME) LIKE 'TMP_%'OR UPPER(TABLE_NAME) LIKE 'TEMP_%') LOOP 
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || T.TABLE_NAME; 
  END LOOP; 
END; 

TRUNCATE TABLE SMR_ECABLE_COUNT

commit;

--查看被锁的表
SELECT P.SPID, A.SERIAL#, C.OBJECT_NAME, B.SESSION_ID, B.ORACLE_USERNAME, B.OS_USER_NAME
FROM V$PROCESS P, V$SESSION A, V$LOCKED_OBJECT B, ALL_OBJECTS C 
WHERE P.ADDR=A.PADDR AND A.PROCESS=B.PROCESS AND C.OBJECT_ID=B.OBJECT_ID  

--查看连接的进程
SELECT SID, SERIAL#, USERNAME, OSUSER FROM V$SESSION; 

--杀掉进程
ALTER SYSTEM KILL SESSION 'SID,SERIAL#'; 

操作命令：
SQL> alter tablespace users offline;
Tablespace altered.
SQL> alter tablespace users online;
Tablespace altered.
将表空间置为只读模式和读写模式：
SQL> alter tablespace users read only;
Tablespace altered.
SQL> alter tablespace users read write;
Tablespace altered.
删除表空间：
SQL> drop tablespace users including contents and datafiles;
Tablespace dropped.

create tablespace fjng090930 datafile 'd:\oracle\fjng090930.dbf' size 500m;
alter database datafile 'd:\oracle\fjng090930.dbf' autoextend on  next 500m maxsize unlimited;
create user fjng090930 identified by fjng090930; 
grant connect,resource,dba to fjng090930;
imp fjng090930/fjng090930@ora fromuser=fj090930 touser=fjng090930 file=d:\fjng090930.dmp