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
