Oracle操作筆記

標籤：io   ar   使用   sp   檔案   資料   on   bs   ad   

1、查詢Oracle版本，資料庫的SID select * from v$version; select name from v$database;

2、查詢Oracle資料庫所支援的功能 SELECT * FROM V$OPTION;

3、擷取資料庫字元集 SELECT * FROM NLS_DATABASE_PARAMETERS; SELECT * FROM V$NLS_PARAMETERS;

4、查詢資料表空間資訊 SELECT * FROM DBA_DATA_FILES;

5、查看最大會話數 SELECT * FROM V$PARAMETER WHERE NAME LIKE ‘proc%‘; select sessions_highwater,sessions_current, sessions_max from v$license;

6、分辨某個使用者是從哪台機器登陸ORACLE的 SELECT machine , terminal FROM V$SESSION;

7、查詢在使用系統的使用者 select * from v$session where  username like ‘%GXX%‘;

8、查詢訪問Oracle服務的電腦IP地址 select sys_context(‘userenv‘,‘ip_address‘) from dual;

9、查看資料表佔用空間 select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_data_files group by tablespace_name union all select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_temp_files group by tablespace_name order by GB;

10.表分區佔有情況 SELECT   a.tablespace_name,          ROUND (a.total_size) "total_size(MB)",          ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",          ROUND (b.free_size, 3) "free_size(MB)",          ROUND (b.free_size / total_size * 100, 2) || ‘%‘ free_rate   FROM   (  SELECT   tablespace_name, SUM (bytes) / 1024 / 1024 total_size               FROM   dba_data_files           GROUP BY   tablespace_name) a,          (  SELECT   tablespace_name, SUM (bytes) / 1024 / 1024 free_size               FROM   dba_free_space           GROUP BY   tablespace_name) b  WHERE   a.tablespace_name = b.tablespace_name(+);

11、查詢資料表空間佔用情況 SELECT B.FILE_ID 檔案ID號, B.TABLESPACE_NAME 資料表空間名, B.BYTES 位元組數, (B.BYTES-SUM(NVL(A.BYTES,0))) 已使用, SUM(NVL(A.BYTES,0)) 剩餘空間, SUM(NVL(A.BYTES,0))/(B.BYTES)*100 剩餘百分比 FROM DBA_FREE_SPACE A,DBA_DATA_FILES B WHERE A.FILE_ID=B.FILE_ID GROUP BY B.TABLESPACE_NAME,B.FILE_ID,B.BYTES ORDER BY B.FILE_ID

12、查詢資料表空間 SELECT * FROM DBA_TABLESPACES;

13、查看系統被鎖定的系統時間 select * from v$locked_object ;

14.檢查UNDO Segment狀態 select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;

15、查詢系統鎖定的表 SELECT S.SID SESSION_ID, S.USERNAME, DECODE(LMODE, 0, ‘None‘, 1, ‘Null‘, 2, ‘Row-S (SS)‘, 3, ‘Row-X (SX)‘, 4, ‘Share‘, 5, ‘S/Row-X (SSX)‘, 6, ‘Exclusive‘, TO_CHAR(LMODE)) MODE_HELD, DECODE(REQUEST, 0, ‘None‘, 1, ‘Null‘, 2, ‘Row-S (SS)‘, 3, ‘Row-X (SX)‘, 4, ‘Share‘, 5, ‘S/Row-X (SSX)‘, 6, ‘Exclusive‘, TO_CHAR(REQUEST)) MODE_REQUESTED, O.OWNER||‘.‘||O.OBJECT_NAME||‘ (‘||O.OBJECT_TYPE||‘)‘, S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID ;

16、如果解除鎖定 ALTER SYSTEM KILL SESSION  ‘C3MLSC,1001#‘;

17、擷取資料庫連結 SELECT * FROM DBA_DB_LINKS;

18、查詢欄位 select * from all_tables where table_name like ‘%SYS%‘ and owner =‘GXX01‘ select * from all_tab_columns where table_name=‘SYS_DEPT‘

19、修改表的主鍵 ALTER TABLE AAA DROP CONSTRAINT AAA_KEY ;

ALTER TABLE AAA ADD CONSTRAINT AAA_KEY PRIMARY KEY(A1,B1) ;

Oracle操作筆記