Oracle操作笔记

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) ;