ORACLE常用系统查询

来源：互联网发布：笨方法学python mobi 编辑：程序博客网时间：2024/05/04 09:43

1 查询系统所有对象

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, LAST_DDL_TIME,TIMESTAMP, STATUS

FROM DBA_OBJECTSWHERE OWNER=UPPER('SCOTT')

2 查看系统所有表

SELECT OWNER, TABLE_NAME, TABLESPACE_NAMEFROM DBA_TABLES

3 查看所有用户的表

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM ALL_TABLES

4 查看当前用户表

SELECT TABLE_NAME, TABLESPACE_NAMEFROM USER_TABLES

5 查看用户表索引

SELECT T.*,I.INDEX_TYPEFROM USER_IND_COLUMNS T, USER_INDEXES I WHERE

T.INDEX_NAME= I.INDEX_NAME AND T.TABLE_NAME= I.TABLE_NAME

AND T.TABLE_NAME ='要查询的表'

6 查看主键

SELECT CU.*FROM USER_CONS_COLUMNSCU, USER_CONSTRAINTS AU

WHERE CU.CONSTRAINT_NAME= AU.CONSTRAINT_NAME

AND AU.CONSTRAINT_TYPE=UPPER('P')AND AU.TABLE_NAME='要查询的表'

7 查看唯一性约束

SELECT COLUMN_NAMEFROM USER_CONS_COLUMNSCU, USER_CONSTRAINTS AU

WHERE CU.CONSTRAINT_NAME= AU.CONSTRAINT_NAMEAND AU.CONSTRAINT_TYPE= UPPER('U')

AND AU.TABLE_NAME='要查询的表'

8 查看外键

SELECT*FROM USER_CONSTRAINTS CWHERE C.CONSTRAINT_TYPE='R'AND C.TABLE_NAME='要查询的表'

SELECT*FROM USER_CONS_COLUMNSCLWHERE CL.CONSTRAINT_NAME='外键名称'

SELECT*FROM USER_CONS_COLUMNSCLWHERE CL.CONSTRAINT_NAME='外键引用表的键名'

9 查看表的列属性

SELECT T.*,C.COMMENTSFROM USER_TAB_COLUMNS T, USER_COL_COMMENTS C

WHERE T.TABLE_NAME= C.TABLE_NAMEAND T.COLUMN_NAME= C.COLUMN_NAMEAND T.TABLE_NAME='要查询的表'

10 查看所有表空间

SELECT TABLESPACE_NAMEFROM DBA_DATA_FILESGROUPBY TABLESPACE_NAME

11 查看oracle最大连接数

SQL>SHOW PARAMETER PROCESSES

NAME TYPE VALUE

----------------------------------------------- ------------------------------

aq_tm_processes integer 0

db_writer_processes integer 1

gcs_server_processes integer 0

global_txn_processes integer 1

job_queue_processes integer 1000

log_archive_max_processes integer 4

processes integer 150

12 修改最大连接数

SQL>ALTER SYSTEM SET PROCESSES=VALUE SCOPE=SPFILE
–重启数据库
SQL>SHUTDOWN ABORT
SQL>START FORCE

13 查看当前连接数

SQL>SELECT * FROM V$SESSION WHERE USERNAME IS NOT NULL;

14 查看不同用户的连接数

SQL>SELECT USERNAME,COUNT(USERNAME) FROM V$SESSION WHERE USERNAME IS NOT NULL GROUPBY USERNAME;

#查看指定用户的连接数

15 查看活动的连接数

SQL>SELECT COUNT(*) FROM V$SESSION WHERE STATUS='ACTIVE';

#查看并发连接数

16 查看指定程序的连接数

SQL>SELECT COUNT(*) FROM V$SESSION WHERE PROGRAM='JDBC THIN CLIENT';

#查看jdbc连接oracle的数目

17 查看数据库安装实例(dba权限)

SQL>SELECT * FROM V$INSTANCE;

18 查看运行实例名

SQL>SHOW PARAMETER INSTANCE_NAME;

19 查看数据库名

SQL>SHOW PARAMETER DB_NAME;

20 查看数据库域名

SQL>SHOW PARAMETER DB_DOMAIN;

21 查看数据库服务名

SQL>SHOW PARAMETER SERVICE_NAMES;

22 查看全局数据库名

SQL>SHOW PARAMETER GLOBAL;

23 查看表空间使用率

SELECT DBF.TABLESPACE_NAME,

DBF.TOTALSPACE "总量(M)",

DBF.TOTALBLOCKS AS "总块数",

DFS.FREESPACE "剩余总量(M)",

DFS.FREEBLOCKS "剩余块数",

(DFS.FREESPACE/ DBF.TOTALSPACE)*100AS "空闲比例"

FROM(SELECT T.TABLESPACE_NAME,

SUM(T.BYTES)/1024/1024 TOTALSPACE,

SUM(T.BLOCKS) TOTALBLOCKS

FROM DBA_DATA_FILES T

GROUPBY T.TABLESPACE_NAME) DBF,

(SELECT TT.TABLESPACE_NAME,

SUM(TT.BYTES)/1024/1024 FREESPACE,

SUM(TT.BLOCKS) FREEBLOCKS

FROM DBA_FREE_SPACE TT

GROUPBY TT.TABLESPACE_NAME) DFS

WHERETRIM(DBF.TABLESPACE_NAME)=TRIM(DFS.TABLESPACE_NAME)

SELECT T.NAME"TABLESPACE NAME",

FREE_SPACE,

(TOTAL_SPACE- FREE_SPACE) USED_SPACE,

TOTAL_SPACE

FROM(SELECT TABLESPACE_NAME,SUM(BYTES/1024/1024) FREE_SPACE

FROM SYS.DBA_FREE_SPACE

GROUPBY TABLESPACE_NAME) FREE,

(SELECT B.NAME,SUM(BYTES/1024/1024) TOTAL_SPACE

FROM SYS.V_$DATAFILE A, SYS.V_$TABLESPACE B

WHERE A.TS#= B.TS#

GROUPBY B.NAME) T

WHERE FREE.TABLESPACE_NAME = T.NAME

14 0