ORACLE数据字典与视图
当ORACLE数据库系统启动后,数据字典总是可用,它驻留在SYSTEM表空间中。数据字典包含视图集,在许多情况下,每一视图集有三种视图包含有类似信息,彼此以前缀相区别,前缀为USER、ALL和DBA。
l 前缀为USER的视图,为用户视图,是在用户的模式内。
l 前缀为ALL的视图为扩展的用户视图(为用户可存取的视图)。
l 前缀为DBA的视图为DBA的视图(为全部用户可存取的视图)。
在数据库ORACLE还维护了一组虚表(virtual table)记录当前数据库的活动,这些表称为动态性能表。动态性能表不是真正的表,许多用户不能直接存取,DBA可查询这些表,可以建立视图,给其它用户授予存取视图权。
ORACLE数据字典与视图
视 图 名 | 说 明 |
ALL_CATALOG | 为用户可存取的全部表、视图和序列 |
ALL_COL_COMMENTS | 为用户可存取的表和视图列上的注释 |
ALL_COL_PRIVS | 在列上授权,该用户或PUBLIC是被授与者 |
ALL_COL_PRIVS_MADE | 在列上授权,该用户为持有者或授与者 |
ALL_COL_PRIVS_RECD | 在列上授权,该用户或PUBLIC是被授与者 |
ALL_CONSTRAINTS | 在可存取表上的约束定义 |
ALL_CONS_COLUMN | 关于在约束定义中可存取列的信息 |
ALL_DB_LINKS | 用户可存取的数据库链 |
ALL_DBF_AUDIT_OPTS | 在对象建立时,所应用的缺省对象审计选择 |
ALL_DEPENDENCIES | 用户可存取的对象之间的从属关系 |
ALL_ERROES | 在用户可存取对象上的当前错误 |
ALL_INDEXES | 在用户可存取的表上的索引说明 |
ALL_IND_COLUMNS | 在可存取的表上的索引列 |
ALL_OBJECTS | 用户可存取的对象 |
ALL_SEQUENCES | 用户可存取的序列说明 |
ALL_SNAPSHOTS | 用户可存取的全部快照 |
ALL_SOURCE | 用户可存取的全部存储对象文本源程序 |
ALL_SYNONYM | 用户可存取的全部同义词 |
ALL_TABLES | 用户可存取的表的说明 |
ALL_TAB_COLUMNS | 用户可存取的表、视图、聚集的列 |
ALL_TAB_COMMENTS | 用户可存取的表或视图上的注释。 |
ALL_TAB_PRIVS | 在对象上授权,该用户或PUBLIC被授与者 |
ALL_TAB_PRIVS_MADE | 在对象上的授权或用户授权 |
ALL_TAB_PRIVS_RECD | 在对象上授权,该用户或PUBLIC是被授与者 |
ALL_TRIGGERS | 用户可存取的触发器 |
ALL_TRIGGER_COLS | 显示用户持有的表中的列和用户所持有的触发器中列的使用,或者用户具有CREATE ANY TRIGGER特权时在所有触发器上列的使用 |
ALL_USERS | 关于数据库是所有用户的信息 |
ALL_VIEW | 用户可存取的视图文本 |
AUDIT_ACTIONS | 审计跟踪动作类型代码描述表 |
CAT | 为USER_CATALOG的同义词 |
CHAINED_ROWS | ANALYZE CHAINED ROWS命令的缺省值 |
CLU | 为USER_CLUSTERS的同义词 |
COLS | 为USER_TAB_COLUMNS的同义词 |
COLUMN_PRIVILEGES | 在列上授权,用户是其授权者,被授与权者、持有者或授与PUBLIC |
DBA_2PC_NEIGHBORS | 关于悬挂事务入和出连接的信息 |
DBA_2PC_PENDING | 关于在PREPARED状态时失败的分式事务信息 |
DBA_AUDIT_EXISTS | 由AUDIT EXISTS命令建立的审计跟踪记录 |
DBA_AUDIT_OBJECT | 系统中全部对象的审计跟踪记录 |
DBA_AUDIT_SESSION | 系统中涉及CONNECT和DISCONNECT的全部审计跟踪记录 |
DBA_AUDIT_STATEMENT | 系统中涉及GRANT、REVOKE、AUDIT、NOAUDIT和ALTER SYSTEM语句的全部审计记录 |
DBA_AUDIT_TRAIL | 为系统中全部审计记录的集合 |
DBA_BLOCKERS | 为会话集,它们具有别的会话正等待的一封锁,而它们本身不等待封锁 |
DBA_CATALAOG | 为全部数据库表、视图、同义词和序列 |
DBA_CLUSTERS | 数据库中全部聚集的说明 |
DBA_CLU_CLOUMNS | 表列对聚集列的映射 |
DBA_COL_COMMENTS | 在所有表和视图的列上的注释 |
DBA_COL_PRIVS | 在数据库列上的全部授权 |
DBA_CONSTRAINTS | 在数据库全部表上的约束定义 |
DBA_CONS_CLOUMNS | 关于约束定义中全部列的信息 |
DBA_DATA_FILES | 关于数据文件的信息 |
DBA_ DB_LINKS | 在数据库中的全部数据链 |
DBA_ DDL_LOCKS | 数据库中当前所有全部DDL封锁和所有未完成的DML封锁请求 |
DBA_ DEPENDENCIES | 全部对象之间的从属关系 |
DBA_DML_LOCKS | 数据库中当前所持有DDL封锁和所有未完成的DML封锁请求 |
DBA_ERRORS | 数据库中全部存储对象上的当前错误 |
DBA_EXP_FILES | 输出文件说明 |
DBA_EXP_OBJECTS | 已有增量输出的对象 |
DBA_EXP_VERSION | 最后的输出会话的版本 |
DBA_EXTENTS | 数据库中全部段的范围 |
DBA_FREE_SPACE | 在所有表空间中未用的范围 |
DBA_INDEXES | 数据库中全部索引的说明 |
DBA_IND_COLUMN | 全部表和聚集上的索引列 |
DBA_LOCKS | 在数据库中持有的全部封锁和未完成请求的封锁(包括DML和DDL封锁) |
DBA_OBJECT | 在数据库中定义的全部聚集、数据库链、索引、包、包体、序列、同义词、表和视图 |
DBA_OBJECT_SIZE | 数据库中的全部PL/SQL对象 |
DBA_OBJ_AUDIT_OPTS | 为全部表和视图的审计选择 |
DBA_PRIV_AUDIT_OPTS | 特权审计选择 |
DBA_PROFILES | 赋给每个环境文件的资源限制 |
DBA_ROLES | 在数据库中已有的全部角色 |
DBA_ROLE_PRIVS | 授权给用户或角色的角色的说明 |
DBA_ROLLBACK_SEGS | 回滚段的说明 |
DBA_SEGMENTS | 分配给全部数据库段的存储 |
DBA_SEQUENCES | 在数据库中全部序列的说明 |
DBA_SNAPSHOTS | 在数据库中的全部快照 |
DBA_SNAPSHOTS_LOGS | 在数据库中的全部快照日志 |
DBA_SOURCE | 在数据库中全部存储对象的源文本 |
DBA_SYNONYMS | 在数据库中的全部同义词 |
DBA_STMT_AUDIT_OPTS | 为当前系统审计选择 |
DBA_SYS_PRIVS | 授权给用户或角色的系统特权 |
DBA_TABLES | 在数据库中的全部表的说明 |
DBA_TABLESSPACES | 数据库中的全部表空间的说明 |
DBA_TAB_CLOUMNS | 全部表、视图和聚集中的列 |
DBA_TAB_COMMENTS | 在数据库中全部表和视图上的注释 |
DBA_TAB_PRIVS | 在数据库中对象上的全部授权 |
DBA_TRIGGERS | 在数据库中全部触发器的说明 |
DBA_TRIGGERS_COLS | 显示由用户定义或在任何用户表上的触发器中列的用法 |
DBA_TS_QUOTAS | 为全部用户的表空间份额 |
DBA_USERS | 关于数据库全部用户的信息 |
DBA_VIEWS | 数据库中全部视图的文本 |
DBA_WAITERS | 等待封锁的全部会话和持有该锁的会话 |
DICT | 为DICTIONARY的同义词 |
DICTIONARY | 数据库字典表和视图的说明 |
DICT_COLUMNS | 数据库字典表和视图中的列的说明 |
EXCEPTIONS | 违反完整性约束的信息 |
GLOBAL_NMAE | 当前数据库的全局名 |
IND | 为USER__INDEXES的同义词 |
INDEX_STATE | 存储VAILDATE INDEX命令的信息 |
OBJ | 为USER_ OBJECT的同义词 |
RESOURCE_COST | 每种资源的费用 |
ROLE_ROLE_PRIVS | 授权给其它角色的角色的信息 |
ROLE_SYS_PRIVS | 授权给角色的系统特权的信息 |
ROLE_TAB_PRVS | 授权给角色的表特权的信息 |
SEQ | 为USER_SEQUENCES的同义词 |
SEESSIONS_PORIVS | 用户当前可用的特权 |
SESSION_ROLES | 用户当前已使其使能打角色 |
SYN | 为USER_SYNONYMS的同义词 |
SYSTEM_PRILEGE_MAP | 为系统特权代码的说明表 |
TABLE_PRIVILEGES | 在对象上授权 |
TABLE_PRIVILEGE_MAP | 为存取特权代码的说明表 |
TABS | 为USER_TABLES的同义词 |
USER_AUDIT_OBJECT | 涉及对象审计跟踪记录 |
USER_AUDIT_SESSION | 涉及连接或删除连接的全部审计跟踪记录 |
USER_AUDIT_STATEMENT | 为用户发出的GRANT、REVOKE、AUDIT、NOAUDIT、ALL SYSTEM语句的审计跟踪项 |
USER_AUDIT_TRAIL | 与用户有关的审计跟踪项 |
USER_CATALOG | 为用户所持有的表、视图、同义词和序列 |
USER_CLUSTERS | 为用户持有的聚集的说明 |
USER_CLU_CLOUMNS | 用户的表列到聚集的映射 |
USER_COL_COMMENTS | 在用户的表或视图的列上的注释 |
USER_COL_PRIVS | 在列上的授权,该用户是持有者、授权者或被授与者 |
USER_COL_PRIVS_MADE | 为用户持有的对象的列上的全部授权 |
USER_COL_PRIVS_RECD | 该用户是被授权者的列上的授权 |
USER_CONSTRAINT | 在用户表上的约束定义 |
USER_CONS_COLUMNS | 由用户持有约束定义中的列的信息 |
USER_DB_LINKS | 为用户持有的数据库链 |
USER_DEPENDENCIES | 用户的对象之间的从属关系 |
USER_ERRORS | 用户的存储对象上的当前错误 |
USER_EXTRNTS | 属于用户对象的段的范围 |
USER_FREE_SPACE | 用户可存取的表空间中未用的范围 |
USRE_INDEXES | 用户自己的索引说明 |
USER_IND_CLOUMNS | 用户索引的列或用户表上的列 |
USER_OBJECTS | 用户所持有的对象 |
USER_OBJECT_SIZE | 用户的PL/SQL对象 |
USER_OBJ_AUDIT_OPTS | 为用户的表和审计选择 |
USER_RESOURCE_LIMITS | 为当前用户的资源限制 |
USER_ROLE_PRIVS | 特权给用户的角色 |
USER_SEGMENT | 属于用户对象的数据库段的存储分配 |
USER_SEQUENCE | 用户自己的序列的说明 |
USER_SNAPSHOTS | 用户可查看的快照 |
USER_SNAPSHOT_LOGS | 用户可持有的快照日志 |
USER_SOURCE | 属于用户的全部存储对象的源文本 |
USER_SYNONYM | 用户专用同义词 |
USER_SYS_PRIVS | 特权给用户的系统特权 |
USER_TABLES | 用户持有表的说明 |
USER_TABLESPACES | 可存表空间的说明 |
USER_TAB_COLUMNSS | 用户的表、视图和聚集的列 |
USER_TAB_COMMENTS | 用户所持的表和视图上的注释 |
USER_TAB_PRIVS | 用户为授权者、持有者或被授权者的对象上的授权 |
USRE_TAB_PRIVS_MADE | 用户所持有的对象的全部特权 |
USER_TAB_PRIVS_RECD | 用户为被授权者的对象上授权 |
USER_TRIGGRS | 用户触发器的说明 |
USER_TRIGGRS_COLS | 用户所持有的或在用户表上的触发器中的列的用法 |
USER_TS_QUOTAS | 用户在表空间上的份额 |
USER_USERS | 关于当前用户的信息 |
USER_VIEWS | 用户持有的视图的文本 |
附录B 动态性能表
表名 | 说明 |
V$ACCESS | 显示数据库中的对象信息 |
V$ARCHIVE | 数据库系统中每个索引的归档日志方面的信息 |
V$BACKUP | 所有在线数据文件的状态 |
V$BGPROCESS | 描述后台进程 |
V$CIRCUIT | 有关虚拟电路信息 |
V$DATABASE | 控制文件中的数据库信息 |
V$DATAFILE | 控制文件中的数据文件信息 |
V$DBFILE | 构成数据库所有数据文件 |
V$DB_OBJECT_CACHE | 表示库高速缓存中被缓存的数据库对象 |
V$DISPATCHER | 调度进程信息 |
V$ENABLEDPRIVS | 那些特权接通 |
V$FILESTAT | 文件读/写统计信息 |
V$FIXED_TABLE | 显示数据库中所有固定表、视图和派生表 |
V$INSTANCE | 当前实例状态 |
V$LATCH | 每类闩锁的信息 |
V$LATCHHOLDER | 当前闩锁占有者的信息 |
V$LATCHNAME | 在V$LATCH表中表示的闩锁的译码闩锁名 |
V$LIBRARYCACHE | 库高速缓冲存储管理统计 |
V$LICENSE | 许可限制信息 |
V$LOADCSTAT | SQL*Loader在直接装入执行过程中的编译统计 |
V$LOCK | 有关封锁和资源信息,不包含DDL封锁 |
V$LOG | 控制文件中的日志文件信息 |
V$LOGFILE | 有关日志文件信息 |
V$LOGHIST | 控制文件中的日志历史信息 |
V$LOGHISTORY | 日志历史中所有日志的归档日志名 |
V$NLS_PARAMETERS | NLS参数的当前值 |
V$OPEN_CURSOR | 每一个用户会话期当前已打开和分析的光标 |
V$PARAMETER | 当前参数值的信息 |
V$PROCESS | 当前活动进程的信息 |
V$QUEUE | 多线索信息队列的信息 |
V$REVOVERY_LOG | 需要完成介质恢复的归档日志 |
V$RECOVERY_FILE | 需要介质恢复的文件状态 |
V$REQDIST | 请求时间直方图,分为12个范围 |
V$RESOURCE | 有关资源信息 |
V$ROLLNAME | 所有在线回滚段的名字 |
V$ROLLSTAT | 所有在线回滚段的统计信息 |
V$ROWCACHE | 数据字典活动的统计信息(每一个包含一个数据字典高速缓存的统计信息) |
V$SESSION | 每一个当前会话期的会话信息 |
V$SESSION_WAIT | 列出活动会话等待的资源或事件 |
V$SESSTAT | 对于每一个当前会话的当前统计值 |
V$SESS_IO | 每一个用户会话的I/O统计 |
V$SGA | 系统全局区统计信息 |
V$SGASTAT | 系统全局区的详细信息 |
V$SHARED_SERVER | 共享服务器进程信息 |
V$SQLAREA | 共享光标高速缓存区的统计信息,每一个有一个共享光标的统计信息 |
V$SQLTEXT | 属于SGA中的共享SQL光标的SQL语句文本 |
V$STATNAME | 在V$SESSTAT表中表示的统计信息的译码统计名 |
V$SYSSTAT | 表V$SESSETA中当前每个统计的全面的系统值 |
V$THREAD | 从控制文件中得到线索信息 |
V$TIMER | 以百分之一秒为单位的当前时间 |
V$TRANSACTION | 有关事务的信息 |
V$TYPE_SIZE | 各种数据库成分的大小 |
V$VERSION | ORACLE Server中核心库成员的版本号,每个成员一行 |
V$WAITSTAT | 块竞争统计,当时间统计可能时,才能更新该表 |
附录C SQL语言运算符与函数
表1 各种运算符
运算符 | 含 义 |
= | 相等 |
!=或<> | 不等于 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
IN(列表) | 等于列表中的任意值 |
BETWEEN值1 AND值2 | 大于等于值1并且小于等于值2 |
LIKE%或- | 模式匹配。“%”匹配0个或任意个字符,“-”匹配一个字符 |
IS NULL | 空值 |
IS NOT NULL | 非空值 |
NOT | 逻辑非 |
AND | 逻辑与 |
OR | 逻辑或 |
表2 字符函数列表
函数 | 功能 | 注释 |
ASCII(char) | 计算char的第一个字符的ASCII值或EBCDIC码值 | 函数返回值取决于计算机系统采用的字符 |
CHAR(n) | 计算ASCⅡ码值或EBCDIC码值是n的字符 | 函数n依赖于计算机系统采用的字符集,n的取值为0~127或0~254之间 |
INITCAP(char) | 将char串口的每个单词的首字母变成大写,其余字母变为小写 | 单词之间用数字、空格、逗号、顿号、冒号、分号、句号、1、@、#、$等字符分隔 |
INSTR(char1,char[,m[,n]]) | 求char1中从m位置起char2第n次出现的位置 | m,n缺省值为1,当>0时,表示从char1的首部起始(从左向右)正向搜索;n<0时,表示从char1的尾部起始(从右向左)反向搜索 |
LENGTH(char) | 计算字符串char的长度 |
|
LOWER(char) | 将char中所有的字母改成小写 |
|
LPAD(char1,n[,char2]) | 从左侧用char2补齐char1至长度n | char2省略时,用空格填充,n<char1的长度时,表示截取char1从左至右侧n个字符 |
RPAD(char1,n[,char2]) | 从右侧用char2补齐char1至长度n | char2省略时,用空格填充,n<char1的长度时,表示截取char1右侧n个符 |
LTRIM(char[,SET]) | 把char1中最左侧的若干个字符去掉,以使其首字符不在SET中 | SET表示单个字符组在的字符集合。SET若被省略时,表示截取char左边的前置空格 |
RTRIM(char[,SET]) | 把char中最右侧的若干个字符去掉,以使其尾字符不在SET中 | SET表示单个字符组成的字符集合。SET若被省略时,表示截取char右边的后置空格 |
REPLACE(char1,char2[,char3] | 将char1中出现的所有char2用char3来代替 | char2和char3同时被省略时,函数返回NULL,仅char3省略时,则表示删除char1中出现的所有char2 |
SOUNDEX(char) | 求与char中一个或多个单词发音相同的字符串 |
|
SUBSTR(char,m[,n]) | 返回char中第m个字符起始n个字符长的子串 | n省略时,表示截取char中第m个字符后的子串 |
TRANSLATE(char1,from,to) | 将from字符集转换为to字符集,char中以from表达的字符用to中相对应的字符所代替 |
|
UPPER(char) | 将char中所有的字母改变大写 |
|
表3 数值函数列表
函数 | 功能 | 注释 |
ABS(n) | 计算n的绝对值 | 允许是数值型常数、数值型列名、包含一个有效数值的文字串或包含一个有效数值的字符型列名 |
CELL(n) | 计算大于或等于n的最小整数 | 数学中的向上取整运算 |
FLOOR(n) | 计算大于或等于n的最大整数 | 数学中的向下取整运算 |
MOD(m,n) | 计算m除以n的余数n=0时,返回m | MOD(m,1)=0说明m是一个整数 |
POWER(m,n) ROUND(m,[,n]) | 计算m的n次方,n>0时,将m四舍五入到小数点右边n位n<0时,将m四舍五入到小数点左边n位 | n要求为整数,否则出错n=0或n被省略表示对m进行取整 |
SIGN(n) | 判断n的正负 | 如果n>0,函数返回1;n=0,函数反加0,n<0,函数反回 |
SQRT(n) TRUNC(m,[,n]) | 计算n的平方根,n>0时,将m小数点右边n位截断;n<0时,将m小数点左边的n位后各位截断,并添加n个0 | n<0时,函数返回NULLn=0或n被省略表示对m进行取整 |
表4 日期函数列表
函数 | 功能 | 注释 |
ADD_MONTHS(d,n) | 计算d加上n个月的日期 | n要求为整数,n>0时,返回d之后n个月的日期;n<0时,返回d之前n个月的日期 |
LAST_DAY(d) | 计算d所在月份最后一天的日期 | 用来确定给定月份中的天数 |
MONTHS_BETWEEN (d1,d2) | 计算d1和d2之间相隔的月数 | 返加结果>0时,表示d1晚于d2;返回结果<0时,表示d1早于d2 |
NEXT_DAY(d,s) | 计算晚于d的第一个s的日期 | s要求是’Sunday’,’Monday’, ’Tuesday’,’Wednesday’,’Thursday’, ’Friday’或’Saturday’ |
SYSDATE | 求系统当前日期和时间 |
|
表5 聚组函数列表
函 数 | 功 能 |
AVG(e) | 计算一组行中e值的平均值 |
COUNT(e) | 计算一组行中e值为非空值的行数 |
COUNT(*) | 计算表中的行数(包括重复值和空值) |
MAX(e) | 计算一组行中e值的最大值 |
MIN(e) | 计算一组行中e值的最小值 |
STDDEV(e) | 计算一组行中e值的标准差 |
SUM(e) | 计算一组行中e值的总和 |
VARIANCE(e) | 计算一组行中e值的方差 |
oracle常用的数据字典的用法
查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users;
查看当前用户的角色
SQL>select * from user_role_privs;
查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
查看用户下所有的表
SQL>select * from user_tables;
1、用户
查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users;
查看当前用户的角色
SQL>select * from user_role_privs;
查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
显示当前会话所具有的权限
SQL>select * from session_privs;
显示指定用户所具有的系统权限
SQL>select * from dba_sys_privs where grantee='GAME';
2、表
查看用户下所有的表
SQL>select * from user_tables;
查看名称包含log字符的表
SQL>select object_name,object_id from user_objects
where instr(object_name,'LOG')>0;
查看某表的创建时间
SQL>select object_name,created from user_objects where object_name=upper('&table_name');
查看某表的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&table_name');
查看放在ORACLE的内存区里的表
SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;
3、索引
查看索引个数和类别
SQL>select index_name,index_type,table_name from user_indexes order by table_name;
查看索引被索引的字段
SQL>select * from user_ind_columns where index_name=upper('&index_name');
查看索引的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&index_name');
4、序列号
查看序列号,last_number是当前值
SQL>select * from user_sequences;
5、视图
查看视图的名称
SQL>select view_name from user_views;
查看创建视图的select语句
SQL>set view_name,text_length from user_views;
SQL>set long 2000; 说明:可以根据视图的text_length值设定set long 的大小
SQL>select text from user_views where view_name=upper('&view_name');
6、同义词
查看同义词的名称
SQL>select * from user_synonyms;
7、约束条件
查看某表的约束条件
SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper('&table_name');
SQL>select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
8、存储函数和过程
查看函数和过程的状态
SQL>select object_name,status from user_objects where object_type='FUNCTION';
SQL>select object_name,status from user_objects where object_type='PROCEDURE';
查看函数和过程的源代码
SQL>select text from all_source where owner=user and name=upper('&plsql_name');
常用SQL查询:
1、查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
2、查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
3、查看回滚段名称及大小
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name;
4、查看控制文件
select name from v$controlfile;
5、查看日志文件
select member from v$logfile;
6、查看表空间的使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
7、查看数据库库对象
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
8、查看数据库的版本
Select version FROM Product_component_version
Where SUBSTR(PRODUCT,1,6)='Oracle';
9、查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;
10、捕捉运行很久的SQL
column username format a12
column opname format a16
column progress format a8
select username,sid,opname,
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining,sql_text
from v$session_longops , v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value
11。查看数据表的参数信息
SELECT partition_name, high_value, high_value_length, tablespace_name,
pct_free, pct_used, ini_trans, max_trans, initial_extent,
next_extent, min_extent, max_extent, pct_increase, FREELISTS,
freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,
empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,
last_analyzed
FROM dba_tab_partitions
--WHERE table_name = :tname AND table_owner = :towner
ORDER BY partition_position
12.查看还没提交的事务
select * from v$locked_object;
select * from v$transaction;
13。查找object为哪些进程所用
select
p.spid,
s.sid,
s.serial# serial_num,
s.username user_name,
a.type object_type,
s.osuser os_user_name,
a.owner,
a.object object_name,
decode(sign(48 - command),
1,
to_char(command), 'Action Code #' || to_char(command) ) action,
p.program oracle_process,
s.terminal terminal,
s.program program,
s.status session_status
from v$session s, v$access a, v$process p
where s.paddr = p.addr and
s.type = 'USER' and
a.sid = s.sid and
a.object='SUBSCRIBER_ATTR'
order by s.username, s.osuser
14。回滚段查看
select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents
Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,
v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,
sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,
v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and
v$rollstat.usn (+) = v$rollname.usn order by rownum
15。耗资源的进程(top session)
select s.schemaname schema_name, decode(sign(48 - command), 1,
to_char(command), 'Action Code #' || to_char(command) ) action, status
session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,
nvl(s.username, '[Oracle process]') user_name, s.terminal terminal,
s.program program, st.value criteria_value from v$sesstat st, v$session s , v$process p
where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL' = 'ALL'
or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc
16。查看锁(lock)情况
select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,
decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',
'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,
o.object_name object, decode(ls.lmode, 1, null, 2, 'Row Share', 3,
'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)
lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2
from sys.dba_objects o, ( select s.osuser, s.username, l.type,
l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s,
v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner
<> 'SYS' order by o.owner, o.object_name
17。查看等待(wait)情况
SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value
FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets',
'consistent gets') group by v$waitstat.class, v$waitstat.count
18。查看sga情况
SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC
19。查看catched object
SELECT owner, name, db_link, namespace,
type, sharable_mem, loads, executions,
locks, pins, kept FROM v$db_object_cache
20。查看V$SQLAREA
SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS,
VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS,
USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,
BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA
21。查看object分类数量
select decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 ,
'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(*) quantity from
sys.obj$ o where o.type# > 1 group by decode (o.type#,1,'INDEX' , 2,'TABLE' , 3
, 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union select
'COLUMN' , count(*) from sys.col$ union select 'DB LINK' , count(*) from
22。按用户查看object种类
select u.name schema, sum(decode(o.type#, 1, 1, NULL)) indexes,
sum(decode(o.type#, 2, 1, NULL)) tables, sum(decode(o.type#, 3, 1, NULL))
clusters, sum(decode(o.type#, 4, 1, NULL)) views, sum(decode(o.type#, 5, 1,
NULL)) synonyms, sum(decode(o.type#, 6, 1, NULL)) sequences,
sum(decode(o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))
others from sys.obj$ o, sys.user$ u where o.type# >= 1 and u.user# =
o.owner# and u.name <> 'PUBLIC' group by u.name order by
sys.link$ union select 'CONSTRAINT' , count(*) from sys.con$
23。有关connection的相关信息
1)查看有哪些用户连接
select s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command),
'Action Code #' || to_char(command) ) action, p.program oracle_process,
status session_status, s.terminal terminal, s.program program,
s.username user_name, s.fixed_table_sequence activity_meter, '' query,
0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num
from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'
order by s.username, s.osuser
2)根据v.sid查看对应连接的资源占用等情况
select n.name,
v.value,
n.class,
n.statistic#
from v$statname n,
v$sesstat v
where v.sid = 71 and
v.statistic# = n.statistic#
order by n.class, n.statistic#
3)根据sid查看对应连接正在运行的sql
select /*+ PUSH_SUBQ */
command_type,
sql_text,
sharable_mem,
persistent_mem,
runtime_mem,
sorts,
version_count,
loaded_versions,
open_versions,
users_opening,
executions,
users_executing,
loads,
first_load_time,
invalidations,
parse_calls,
disk_reads,
buffer_gets,
rows_processed,
sysdate start_time,
sysdate finish_time,
'>' || address sql_address,
'N' status
from v$sqlarea
where address = (select sql_address from v$session where sid = 71)
2)根据v.sid查看对应连接的资源占用等情况
select n.name,
v.value,
n.class,
n.statistic#
from v$statname n,
v$sesstat v
where v.sid = 71 and
v.statistic# = n.statistic#
order by n.class, n.statistic#
3)根据sid查看对应连接正在运行的sql
select /*+ PUSH_SUBQ */
command_type,
sql_text,
sharable_mem,
persistent_mem,
runtime_mem,
sorts,
version_count,
loaded_versions,
open_versions,
users_opening,
executions,
users_executing,
loads,
first_load_time,
invalidations,
parse_calls,
disk_reads,
buffer_gets,
rows_processed,
sysdate start_time,
sysdate finish_time,
'>' || address sql_address,
'N' status
from v$sqlarea
where address = (select sql_address from v$session where sid = 71)
24.查询表空间使用情况
select a.tablespace_name "表空间名称",
100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)",
round(a.bytes_alloc/1024/1024,2) "容量(M)",
round(nvl(b.bytes_free,0)/1024/1024,2) "空闲(M)",
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",
Largest "最大扩展段(M)",
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b,
(select round(max(ff.length)*16/1024,2) Largest,
ts.name tablespace_name
from sys.fet$ ff, sys.file$ tf,sys.ts$ ts
where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#
group by ts.name, tf.blocks) c
where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name
25. 查询表空间的碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;
26。查询有哪些数据库实例在运行
select inst_name from v$active_instances;
//取得服务器的IP 地址
select utl_inaddr.get_host_address from dual
//取得客户端的IP地址
select sys_context('userenv','host'),sys_context('userenv','ip_address') from dual