ORACLE經常使用系統查詢



1? 查詢系統全部對象

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

FROM DBA_OBJECTS WHERE OWNER=UPPER('SCOTT')

2? 查看系統全部表

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES

3? 查看全部用戶的表

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM ALL_TABLES

4? 查看當前用戶表

SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES

5? 查看用戶表索引

SELECT T.*,I.INDEX_TYPE FROM 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_NAME FROM USER_CONS_COLUMNSCU, USER_CONSTRAINTS AU

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

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

8? 查看外鍵

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

SELECT * FROM USER_CONS_COLUMNSCL WHERE CL.CONSTRAINT_NAME = '外鍵名稱'

SELECT * FROM USER_CONS_COLUMNSCL WHERE CL.CONSTRAINT_NAME = '外鍵引用表的鍵名'

9? 查看表的列屬性

SELECT T.*,C.COMMENTS FROM USER_TAB_COLUMNS T, USER_COL_COMMENTS C

WHERE T.TABLE_NAME = C.TABLE_NAME AND T.COLUMN_NAME = C.COLUMN_NAME AND T.TABLE_NAME = '要查詢的表'

10???????????????????查看全部表空間

SELECT TABLESPACE_NAME FROM DBA_DATA_FILES GROUP BY 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) * 100 AS "空暇比例"

? FROM (SELECT T.TABLESPACE_NAME,

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

?????????????? SUM(T.BLOCKS) TOTALBLOCKS

????????? FROM DBA_DATA_FILES T

? ???????GROUP BY T.TABLESPACE_NAME) DBF,

?????? (SELECT TT.TABLESPACE_NAME,

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

?????????????? SUM(TT.BLOCKS) FREEBLOCKS

????????? FROM DBA_FREE_SPACE TT

???????? GROUP BY TT.TABLESPACE_NAME) DFS

?WHERE TRIM(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

???????? GROUP BY 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#

???????? GROUP BY B.NAME) T

?WHERE FREE.TABLESPACE_NAME = T.NAME