--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
oracle常用管理sql
最新推荐文章于 2024-10-17 10:02:06 发布