--查询数据文件名及ID
SELECT FILE#, NAME FROM V$DATAFILE;
--查看alert警告,以及oracle给出的处理建议
SELECT REASON, OBJECT_TYPE, SUGGESTED_ACTION FROM DBA_OUTSTANDING_ALERTS;
--列出当前模式下所有外键的报告表
SELECT RPAD(CHILD.TABLE_NAME, 25, ' ') CHILD_TABLENAME,
RPAD(CP.COLUMN_NAME, 17, ' ') REFERRING_COLUMN,
RPAD(PARENT.TABLE_NAME, 25, ' ') PARENT_TABLENAME,
RPAD(PC.COLUMN_NAME, 15, ' ') REFERRED_COLUMN,
RPAD(CHILD.CONSTRAINT_NAME, 25, ' ') CONSTRAINT_NAME FROM USER_CONSTRAINTS CHILD,
USER_CONSTRAINTS PARENT,
USER_CONS_COLUMNS CP,
USER_CONS_COLUMNS PC
WHERE CHILD.CONSTRAINT_TYPE = 'R' AND
CHILD.R_CONSTRAINT_NAME = PARENT.CONSTRAINT_NAME AND
CHILD.CONSTRAINT_NAME = CP.CONSTRAINT_NAME AND
PARENT.CONSTRAINT_NAME = PC.CONSTRAINT_NAME AND
CP.POSITION = PC.POSITION
ORDER BY CHILD.OWNER,
CHILD.TABLE_NAME,
CHILD.CONSTRAINT_NAME,
CP.POSITION;
--查看当前用户所有表对象
SELECT * FROM TAB;
--查看数据库大小、空间使用情况
SELECT B.FILE_ID 文件ID,
B.TABLESPACE_NAME 表空间,
B.FILE_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_NAME,
B.FILE_ID,
B.BYTES ORDER BY B.TABLESPACE_NAME;
--查看现有回滚段及其状态
SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, SEGMENT_ID, FILE_ID, STATUS
FROM DBA_ROLLBACK_SEGS;
--查看数据文件放置的路径
SELECT TABLESPACE_NAME, FILE_ID, BYTES / 1024 / 1024, FILE_NAME
FROM DBA_DATA_FILES
ORDER BY FILE_ID;
--查看系统日志
SELECT * FROM V$LOG;
--查数据库状态
SELECT STATUS FROM V$INSTANCE;
--ASCII码查询
SELECT CHR(65) FROM DUAL;
SELECT ASCII('A') FROM DUAL;
--查看ip
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') FROM DUAL;
--查看当前user
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
select user from dual;
-- 查看表各字段属性、长度
select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
from user_tab_columns;
-- 查看某表的各字段ID、长度、类型等信息,可以用来更改表内字段顺序
SELECT * FROM SYS.COL$ WHERE OBJ#=(
SELECT OBJECT_ID FROM All_Objects WHERE object_name='TEST_T1');
--查询当前会话的权限
SELECT * FROM SESSION_PRIVS;
--查询实例名
SELECT instance_name FROM v$instance;
--查询全局数据库名
select name from v$database;
-- 查看各表注释
select * from user_tab_comments;
-- 各表列注释
select * from user_col_comments;
--查看回收站信息
SELECT * FROM USER_RECYCLEBIN;
--查询最近使用的SQL语句
select * from v$sql WHERE rownum<100 ORDER BY last_load_time DESC;
--查询约束关系
select * from ALL_CONSTRAINTS;