ORACLE学习之常用数据库系统表语句
2018-08-18 本文已影响7人
twoapes
说明
本文介绍系统表的相关slq常用语句,方便日常工作和学习
假定用户为TWOAPES
常用数据库语句(一)
统计分析:analyze table table_name compute statistics;
ORACLE查询当前数据库的默认日期格式:SELECT SYSDATE FROM dual;
ORACLE修改当前数据库的默认日期格式(使用dba权限的用户):
ALTER SYSTEM SET nls_date_format='YYYY-MM-DD hh24:mi:ss' scope=spfile;
SHUTDOWN IMMEDIATE;
startup;
SELECT SYSDATE FROM dual;
SHOW PARAMETERS NLS;
查询表空间文件位置:select * from dba_data_files;
查询当前用户下,有哪些表:SELECT * FROM user_tables;
查询当前用户下, 可以访问哪些表 [也就是访问自己 和 其他用户的]:SELECT * FROM all_tables;
查询当前数据库所有的表， 需要你有 DBA 的权限:SELECT * FROM dba_tables;
查询当前用户下的所有对象:SELECT * FROM tab;
创建用户和密码:CREATE USER TWOAPES IDENTIFIED BY pass_word;
授予连接,资源和dba权限:GRANT CONNECT,RESOURCE,DBA TO TWOAPES;
用户需要查询数据库中所有数据：GRANT SELECT ANY TABLE TO TWOAPES;
该用户需要读取数据字典、使用OEM工具等：GRANT SELECT ANY DICTIONARY TO TWOAPES;
给表或存储过程赋权限:(with grant option权限会被回收,with admin option会永久保留)
GRANT CREATE TABLESPACE TO TWOAPES;
GRANT SELECT ON tabelname TO TWOAPES;
GRANT UPDATE ON tablename TO TWOAPES;
GRANT EXECUTE ON procedurename TO TWOAPES;
授权存储过程:grant update on tablename to TWOAPES with grant option;
给所有用户向表 tablename 插入记录的权限：GRANT ALL ON TWOAPES TO PUBLIC;
赋予权限SQL语句（给普通用户user赋所有权限）：GRANT ALL TO TWOAPES;
如果只要赋予部分权限，则：GRANT CREATE SESSION, SELECT ANY TABLE, DBA TO TWOAPES;
清空数据:TRUNCATE TABLE table_name;
查看表:select * from user_tables;
查看视图:SELECT view_name FROM user_views;
查询包体:
SELECT s.name, s.text,s.line
FROM all_source s
WHERE TYPE = 'PACKAGE BODY'
AND owner = 'TWOAPES'
GROUP BY s.name, s.text,s.line;
删除用户:DROP USER TWOAPES CASCADE;
创建用户表空间:
create tablespace ods datafile'D:\app\Administrator\oradata\user_name\ods.dbf' size 100m autoextend on next 100m maxsize 1024m extent
management local autoallocate;
无需用户修改sys密码:
sqlplus/nolog;
CONNECT SYS AS SYSDBA ALTER USER SYS IDENTIFIED BY password;
查询某字段是否有重复的数据:
SELECT s.comment, COUNT(*)
FROM table_name s
GROUP BY s.comment
HAVING COUNT(*) > 1;
查询指定数据库的表创建语句(但是不能超过100条, rownum <= 100条件不能去除):
SELECT dbms_metadata.get_ddl('TABLE', table_name, USER)
FROM user_tables s
WHERE s.tablespace_name = 'tablespace_name'
AND rownum <= 100
最高效率分页查询:
SELECT * FROM
( SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= end )
WHERE RN >= star
查看密码到期时间:SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'
去除密码到期:ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED
获得当年实际天数:SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - TRUNC(SYSDATE, 'YYYY') days FROM DUAL
创建新用户需要的语句:
CREATE TABLESPACE TWOAPES DATAFILE 'D:\app\Administrator\oradata\TWOAPES\TWOAPESO01.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER TABLESPACE TWOAPES ADD DATAFILE 'D:\app\Administrator\oradata\TWOAPES\TWOAPESO02.dbf' SIZE 1024M;
ALTER DATABASE DATAFILE 'D:\app\Administrator\oradata\TWOAPES\TWOAPESO02.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;
CREATE USER TWOAPES IDENTIFIED BY 1 DEFAULT TABLESPACE TWOAPES;
GRANT CONNECT TO TWOAPES;
GRANT RESOURCE TO TWOAPES;
GRANT DBA TO TWOAPES;
GRANT UNLIMITED TABLESPACE TO TWOAPES;
GRANT create any table TO TWOAPES;
--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;
SQL2:
代码如下:
--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
--查看主键外键,索引等
SELECT t.table_name AS 表名,
t.column_name AS 表字段,
co.comments AS 表备注,
t.data_type AS 字段类型,
t.data_length AS 字段长度,
c.comments AS 字段备注
FROM user_tab_columns t
JOIN user_col_comments c
ON t.table_name = c.table_name
JOIN user_tab_comments co
ON co.table_name = t.table_name;
SELECT au.table_name AS 表名,
cu.column_name AS 字段名,
au.owner AS 用户,
cu.constraint_name AS 约束名称,
decode(au.constraint_type,
'C',
'普通字段',
'P',
'主键',
'U',
'唯一约束',
'R',
'外键') AS 约束类型
FROM user_constraints au
JOIN user_cons_columns cu
ON au.table_name = cu.table_name;
--综合查询
SELECT 用户,
索引名称,
字段名称,
字段类型,
字段长度,
字段备注,
约束名称,
约束对应的字段名,
约束类型,
表名,
表备注,
所属表空间
FROM (
--所有非约束表
SELECT NULL AS 用户,
NULL AS 索引名称,
t.column_name AS 字段名称,
t.data_type AS 字段类型,
t.data_length AS 字段长度,
c.comments AS 字段备注,
NULL AS 约束名称,
NULL AS 约束对应的字段名,
NULL AS 约束类型,
t.table_name AS 表名,
co.comments AS 表备注,
ut.tablespace_name AS 所属表空间
FROM user_tab_columns t
JOIN user_col_comments c
ON t.table_name = c.table_name
AND t.column_name = c.column_name
JOIN user_tab_comments co
ON co.table_name = t.table_name
JOIN user_tables ut
ON ut.table_name = t.table_name
UNION
--所有约束表
SELECT au.owner AS 用户,
au.index_name AS 索引名称,
t.column_name AS 字段名称,
t.data_type AS 字段类型,
t.data_length AS 字段长度,
c.comments AS 字段备注,
au.constraint_name AS 约束名称,
cu.column_name AS 约束对应的字段名,
decode(au.constraint_type,
'C',
'普通字段',
'P',
'主键',
'U',
'唯一约束',
'R',
'外键') AS 约束类型,
t.table_name AS 表名,
co.comments AS 表备注,
ut.tablespace_name AS 所属表空间
FROM user_tab_columns t
JOIN user_col_comments c
ON t.table_name = c.table_name
AND t.column_name = c.column_name
JOIN user_tab_comments co
ON co.table_name = t.table_name
JOIN user_constraints au
ON au.table_name = t.table_name
JOIN user_cons_columns cu
ON cu.constraint_name = au.constraint_name
JOIN user_tables ut
ON ut.table_name = t.table_name) k;
创建序列:
CREATE SEQUENCE seq_SEQUENCE
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXvalue -- 不设置最大值
NOCYCLE -- 一直累加，不循环
NOCACHE; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE
常用数据库语句(二)
--查询当前用户下所有的可用索引属性
SELECT s.index_name AS 索引名称,
s.index_type AS 索引类型,
s.table_name AS 表名,
s.table_type AS 表类型,
s.uniqueness AS 唯一性标志,
s.tablespace_name AS 表空间名称,
s.num_rows AS 当前行数
FROM user_indexes s;
--查询所有用户下所有的可用索引属性
SELECT s.owner AS 用户名称,
s.index_name AS 索引名称,
s.index_type AS 索引类型,
s.table_name AS 表名,
s.table_type AS 表类型,
s.uniqueness AS 唯一性标志,
s.tablespace_name AS 表空间名称,
s.num_rows AS 当前行数
FROM dba_indexes s;
--查询当前用户下所有的可用表空间属性
SELECT s.tablespace_name AS 表空间名称, s.max_size AS 最大值
FROM user_tablespaces s;
--查询所有用户下所有的可用表空间属性
SELECT s.tablespace_name AS 表空间名称, s.max_size AS 最大值
FROM dba_tablespaces s;
--查询当前用户下所有的表属性
SELECT s.table_name AS 表名,
s.num_rows AS 表中的行数,
s.tablespace_name AS 所在的表空间
FROM user_tables s;
--查询所有用户下所有的表属性
SELECT s.owner AS 用户名称,
s.table_name AS 表名,
s.num_rows AS 表中的行数,
s.tablespace_name AS 所在的表空间
FROM dba_tables s;
--查询当前用户下所有非对象视图属性
SELECT view_name AS 视图名称,
text_length AS 视图文本的长度,
text AS 查看文本,
superview_name AS 子视图名称,
editioning_view AS "同一个版本视图(Y-是,N-否)",
read_only AS "只读(Y-是,N-否)"
FROM user_views;
--查询所有用户下所有非对象视图属性
SELECT s.owner AS 用户名称,
s.view_name AS 视图名称,
s.text_length AS 视图文本的长度,
s.text AS 查看文本,
s.superview_name AS 子视图名称,
s.editioning_view AS "同一个版本视图(Y-是,N-否)",
s.read_only AS "只读(Y-是,N-否)"
FROM dba_views s
WHERE s.owner = 'ORACLE账户的用户名';
--当前用户的表及视图中字段名称及字段注释
SELECT s.table_name AS 对象名称,
s.column_name AS 字段名称,
s.comments AS 注释
FROM user_col_comments s;
--所有用户的表及视图中字段名称及字段注释
SELECT s.owner AS 用户名称,
s.table_name AS 对象名称,
s.column_name AS 字段名称,
s.comments AS 注释
FROM all_col_comments s
WHERE s.owner = 'ORACLE账户的用户名';
--查看所有用户下的非图像格式字段的表及视图结构
SELECT s.owner AS 用户,
s.table_name AS "表,视图,群集名称",
s.column_name AS 字段名称,
s.data_type AS 字段类型,
s.data_length AS "字段(字符串)的长度(字节)",
s.data_precision AS "长度(二进制或十进制数字)",
s.data_scale AS 小数点右边的数字的刻度,
s.nullable AS "是否允许为空(Y-是,N-否)",
s.column_id AS 创建字段的序字段号,
s.default_length AS 默认长度,
s.data_default AS 默认值,
s.num_distinct AS 字段的不同值数目,
s.low_value AS 字段中的最低的值,
s.high_value AS 字段中的最高的值,
s.density AS 柱的密度,
s.num_nulls AS 空值的数量,
s.num_buckets AS 字段的柱状图中的桶数,
s.last_analyzed "最近一次分析时间(统计分析)",
s.character_set_name AS 字符集名称,
s.char_col_decl_length AS 声明类型时的长度,
s.avg_col_len AS 字段的平均长度,
s.char_length AS 字符字段最大长度,
s.char_used AS "最大长度单位(C-字符,B-字节)",
s.hidden_column AS "YES-隐藏列,NO-非隐藏列",
s.virtual_column AS "YES-虚拟列,NO-非虚拟列",
s.segment_column_id AS 段中的序列号,
s.internal_column_id AS 列中的序列号,
s.qualified_col_name AS 合格的列名称
FROM all_tab_cols s
WHERE s.owner = 'ORACLE账户的用户名';
SELECT s.owner AS 用户,
s.table_name AS "表,视图,群集名称",
s.column_name AS 字段名称,
s.data_type AS 字段类型,
s.data_length AS "字段(字符串)的长度(字节)",
s.data_precision AS "长度(二进制或十进制数字)",
s.data_scale AS 小数点右边的数字的刻度,
s.nullable AS "是否允许为空(Y-是,N-否)",
s.column_id AS 创建字段的序字段号,
s.default_length AS 默认长度,
s.data_default AS 默认值,
s.num_distinct AS 字段的不同值数目,
s.low_value AS 字段中的最低的值,
s.high_value AS 字段中的最高的值,
s.density AS 柱的密度,
s.num_nulls AS 空值的数量,
s.num_buckets AS 字段的柱状图中的桶数,
s.last_analyzed "最近一次分析时间(统计分析)",
s.character_set_name AS 字符集名称,
s.char_col_decl_length AS 声明类型时的长度,
s.avg_col_len AS 字段的平均长度,
s.char_length AS 字符字段最大长度,
s.char_used AS "最大长度单位(C-字符,B-字节)"
FROM all_tab_columns s
WHERE s.owner = 'ORACLE账户的用户名';
-- 查看当前用户下非图像格式字段的表及视图结构
SELECT s.table_name AS "表,视图,群集名称",
s.column_name AS 字段名称,
s.data_type AS 字段类型,
s.data_length AS "字段(字符串)的长度(字节)",
s.data_precision AS "长度(二进制或十进制数字)",
s.data_scale AS 小数点右边的数字的刻度,
s.nullable AS "是否允许为空(Y-是,N-否)",
s.column_id AS 创建字段的序字段号,
s.default_length AS 默认长度,
s.data_default AS 默认值,
s.num_distinct AS 字段的不同值数目,
s.low_value AS 字段中的最低的值,
s.high_value AS 字段中的最高的值,
s.density AS 柱的密度,
s.num_nulls AS 空值的数量,
s.num_buckets AS 字段的柱状图中的桶数,
s.last_analyzed "最近一次分析时间(统计分析)",
s.character_set_name AS 字符集名称,
s.char_col_decl_length AS 声明类型时的长度,
s.avg_col_len AS 字段的平均长度,
s.char_length AS 字符字段最大长度,
s.char_used AS "最大长度单位(C-字符,B-字节)"
FROM user_tab_cols s;
SELECT s.table_name AS "表,视图,群集名称",
s.column_name AS 字段名称,
s.data_type AS 字段类型,
s.data_length AS "字段(字符串)的长度(字节)",
s.data_precision AS "长度(二进制或十进制数字)",
s.data_scale AS 小数点右边的数字的刻度,
s.nullable AS "是否允许为空(Y-是,N-否)",
s.column_id AS 创建字段的序字段号,
s.default_length AS 默认长度,
s.data_default AS 默认值,
s.num_distinct AS 字段的不同值数目,
s.low_value AS 字段中的最低的值,
s.high_value AS 字段中的最高的值,
s.density AS 柱的密度,
s.num_nulls AS 空值的数量,
s.num_buckets AS 字段的柱状图中的桶数,
s.last_analyzed "最近一次分析时间(统计分析)",
s.character_set_name AS 字符集名称,
s.char_col_decl_length AS 声明类型时的长度,
s.avg_col_len AS 字段的平均长度,
s.char_length AS 字符字段最大长度,
s.char_used AS "最大长度单位(C-字符,B-字节)"
FROM user_tab_columns s;
--------------------------------------------网上补充-----------------------------------------------------------------
-- DBA/ALL/USER/V_$/GV_$/SESSION/INDEX开头的绝大部分都是视图
-- DBA_TABLES意为DBA拥有的或可以访问的所有的关系表。
-- ALL_TABLES意为某一用户拥有的或可以访问的所有的关系表。
-- USER_TABLES意为某一用户所拥有的所有的关系表。
-- 当某一用户本身就为数据库DBA时，DBA_TABLES与ALL_TABLES等价。
-- DBA_TABLES >= ALL_TABLES >= USER_TABLES
SELECT view_name AS 视图名称,
text_length AS 视图文本的长度,
text AS 查看文本,
superview_name AS 子视图名称,
editioning_view AS "同一个版本视图(Y-是,N-否)",
read_only AS "只读(Y-是,N-否)"
FROM dba_views
WHERE view_name LIKE 'DBA%'
AND owner = 'SYS';
SELECT view_name AS 视图名称,
text_length AS 视图文本的长度,
text AS 查看文本,
superview_name AS 子视图名称,
editioning_view AS "同一个版本视图(Y-是,N-否)",
read_only AS "只读(Y-是,N-否)"
FROM dba_views
WHERE view_name LIKE 'ALL%'
AND owner = 'SYS';
SELECT view_name AS 视图名称,
text_length AS 视图文本的长度,
text AS 查看文本,
superview_name AS 子视图名称,
editioning_view AS "同一个版本视图(Y-是,N-否)",
read_only AS "只读(Y-是,N-否)"
FROM dba_views
WHERE view_name LIKE 'USER%'
AND owner = 'SYS';
SELECT view_name AS 视图名称,
text_length AS 视图文本的长度,
text AS 查看文本,
superview_name AS 子视图名称,
editioning_view AS "同一个版本视图(Y-是,N-否)",
read_only AS "只读(Y-是,N-否)"
FROM dba_views
WHERE view_name LIKE 'V_$%'
AND owner = 'SYS'; -- 针对某个实例的视图
SELECT view_name AS 视图名称,
text_length AS 视图文本的长度,
text AS 查看文本,
superview_name AS 子视图名称,
editioning_view AS "同一个版本视图(Y-是,N-否)",
read_only AS "只读(Y-是,N-否)"
FROM dba_views
WHERE view_name LIKE 'GV_$%'
AND owner = 'SYS'; -- 全局视图，针对多个实例环境
SELECT view_name AS 视图名称,
text_length AS 视图文本的长度,
text AS 查看文本,
superview_name AS 子视图名称,
editioning_view AS "同一个版本视图(Y-是,N-否)",
read_only AS "只读(Y-是,N-否)"
FROM dba_views
WHERE view_name LIKE 'SESSION%'
AND owner = 'SYS';
SELECT view_name AS 视图名称,
text_length AS 视图文本的长度,
text AS 查看文本,
superview_name AS 子视图名称,
editioning_view AS "同一个版本视图(Y-是,N-否)",
read_only AS "只读(Y-是,N-否)"
FROM dba_views
WHERE view_name LIKE 'INDEX%'
AND owner = 'SYS';
-- V$/GV$开头的绝大部分都是V_$/GV_$表的别名
SELECT * FROM dba_synonyms WHERE synonym_name LIKE 'V$%';
SELECT * FROM dba_synonyms WHERE synonym_name LIKE 'GV$%';
-- X$没有对应的X_$
SELECT * FROM dba_synonyms WHERE synonym_name LIKE 'X$%';
-- 比较常用的DBA开头的视图有
SELECT * FROM dba_users; --数据库用户信息
SELECT * FROM dba_roles; --角色信息
SELECT * FROM dba_segments; --表段信息
SELECT * FROM dba_extents; --数据区信息
SELECT * FROM dba_objects; --数据库对象信息
SELECT * FROM dba_lobs; --lob数据信息
SELECT * FROM dba_tablespaces; --数据库表空间信息
SELECT * FROM dba_data_files; --数据文件设置信息
SELECT * FROM dba_temp_files; --临时数据文件信息
SELECT * FROM dba_rollback_segs; --回滚段信息
SELECT * FROM dba_ts_quotas; --用户表空间配额信息
SELECT * FROM dba_free_space; --数据库空闲空间信息
SELECT * FROM dba_profiles; --数据库用户资源限制信息
SELECT * FROM dba_sys_privs; --用户的系统权限信息
SELECT * FROM dba_tab_privs; --用户具有的对象权限信息
SELECT * FROM dba_col_privs; --用户具有的列对象权限信息
SELECT * FROM dba_role_privs; --用户具有的角色信息
SELECT * FROM dba_audit_trail; --审计跟踪记录信息
SELECT * FROM dba_stmt_audit_opts; --审计设置信息
SELECT * FROM dba_audit_object; --对象审计结果信息
SELECT * FROM dba_audit_session; --会话审计结果信息
SELECT * FROM dba_indexes; --用户模式的索引信息
-- 比较常用的ALL开头的视图有
SELECT * FROM all_users; --数据库所有用户的信息
SELECT * FROM all_objects; --数据库所有的对象的信息
SELECT * FROM all_def_audit_opts; --所有默认的审计设置信息
SELECT * FROM all_tables; --所有的表对象信息
SELECT * FROM all_indexes; --所有的数据库对象索引的信息
SELECT * FROM all_tab_comments; --查询所有用户的表,视图等
SELECT * FROM all_col_comments; --查询所有用户的表的列名和注释.
SELECT * FROM all_tab_columns; --查询所有用户的表的列名等信息(详细但是没有备注)
-- 比较常用的user开头的视图有
SELECT * FROM user_objects; --用户对象信息
SELECT * FROM user_source; --数据库用户的所有资源对象信息
SELECT * FROM user_segments; --用户的表段信息
SELECT * FROM user_tables; --用户的表对象信息
SELECT * FROM user_tab_columns; --用户的表列信息
SELECT * FROM user_constraints; --用户的对象约束信息
SELECT * FROM user_sys_privs; --当前用户的系统权限信息
SELECT * FROM user_tab_privs; --当前用户的对象权限信息
SELECT * FROM user_col_privs; --当前用户的表列权限信息
SELECT * FROM user_col_comments; -- 查询本用户的表的列名和注释
SELECT * FROM user_role_privs; --当前用户的角色权限信息
SELECT * FROM user_indexes; --用户的索引信息
SELECT * FROM user_ind_columns; --用户的索引对应的表列信息
SELECT * FROM user_cons_columns; --用户的约束对应的表列信息
SELECT * FROM user_clusters; --用户的所有簇信息
SELECT * FROM user_clu_columns; --用户的簇所包含的内容信息
SELECT * FROM user_cluster_hash_expressions; --散列簇的信息
-- 比较常用的V$开头的别名有
SELECT * FROM v$database; --数据库信息
SELECT * FROM v$datafile; --数据文件信息
SELECT * FROM v$controlfile; --控制文件信息
SELECT * FROM v$logfile; --重做日志信息
SELECT * FROM v$instance; --数据库实例信息
SELECT * FROM v$log; --日志组信息
SELECT * FROM v$loghist; --日志历史信息
SELECT * FROM v$sga; --数据库SGA信息
SELECT * FROM v$parameter; --初始化参数信息
SELECT * FROM v$process; --数据库服务器进程信息
SELECT * FROM v$bgprocess; --数据库后台进程信息
SELECT * FROM v$controlfile_record_section; --控制文件记载的各部分信息
SELECT * FROM v$thread; --线程信息
SELECT * FROM v$datafile_header; --数据文件头所记载的信息
SELECT * FROM v$archived_log; --归档日志信息
SELECT * FROM v$archive_dest; --归档日志的设置信息
SELECT * FROM v$logmnr_contents; --归档日志分析的DML DDL结果信息
SELECT * FROM v$logmnr_dictionary; --日志分析的字典文件信息
SELECT * FROM v$logmnr_logs; --日志分析的日志列表信息
SELECT * FROM v$tablespace; --表空间信息
SELECT * FROM v$tempfile; --临时文件信息
SELECT * FROM v$filestat; --数据文件的I/O统计信息
SELECT * FROM v$undostat; --Undo数据信息
SELECT * FROM v$rollname; --在线回滚段信息
SELECT * FROM v$session; --会话信息
SELECT * FROM v$transaction; --事务信息
SELECT * FROM v$rollstat; --回滚段统计信息
SELECT * FROM v$pwfile_users; --特权用户信息
SELECT * FROM v$sqlarea; --当前查询过的sql语句访问过的资源及相关的信息
SELECT * FROM v$sql; --与v$sqlarea基本相同的相关信息
SELECT * FROM v$sysstat; --数据库系统状态信息
SELECT * FROM v$controlfile; --查询oracle安装位置
SELECT * FROM v$pwfile_users; --查询具有管理员权限的用户
-- 比较常用的SESSION开头的视图有
SELECT * FROM session_roles; --会话的角色信息
SELECT * FROM session_privs; --会话的权限信息
-- 比较常用的INDEX开头的视图有
SELECT * FROM index_stats; --索引的设置和存储信息
-- 伪表，参考oracle 中 dual 详解：http://blog.csdn.net/ozhouhui/article/details/7935196
SELECT * FROM dual; --系统伪列表信息
SELECT SYSDATE FROM dual; --可将Sysdate视为一个其结果为当前日期和时间的函数，在任何可以使用Oracle函数的地方都可以使用Sysdate。也可以将它视为每个表的一个隐藏的列或伪列。
SELECT current_date FROM dual; --报告会话的时区中的系统日期。注：可以设置自己的时区，以区别于数据库的时区。
SELECT systimestamp FROM dual; --报告TIMESTAMP数据类型格式的系统日期。
-- 系统权限
-- GRANTEE 接受该权限的用户名
-- OWNER 对象的拥有者
-- GRANTOR 赋予权限的用户
SELECT * FROM dba_sys_privs WHERE grantee = 'SYS';
SELECT * FROM dba_sys_privs WHERE grantee = 'CONNECT';
SELECT * FROM dba_sys_privs WHERE grantee = 'RESOURCE';
grant sysdba TO user_name; --给某用户赋予管理员权限
revoke sysdba FROM user_name; --给某用户去除管理员权限
-- 角色权限
-- 查看某个用户有哪些角色
SELECT * FROM dba_role_privs WHERE grantee = 'SYS';
-- 查看某个角色被赋予了哪些用户
SELECT * FROM dba_role_privs WHERE granted_role = 'DBA';
-- 对象权限
SELECT * FROM dba_tab_privs;
-- 查看某个系统用户是否有SYSDBA或者SYSOPER权限
SELECT * FROM v$pwfile_users;
-- 锁定、解锁用户
SELECT * FROM dba_users WHERE username = 'SCOTT';
ALTER USER scott account LOCK; --锁定用户
ALTER USER scott account unlock; --解锁用户
COMMIT;
-- oracle10g 修改用户密码:
SELECT s.password FROM dba_users s WHERE s.username = 'SCOTT';
ALTER USER scott identified BY 1; --修改用户密码
SELECT * FROM global_name; -- 查看oracle的全局数据库名
SELECT * FROM v$database; -- 查看数据库名 show parameter db_name;
-- 数据库实例名对应着SID
SELECT * FROM v$instance; --查看数据库实例名 show parameter instance_name;
SELECT instance FROM v$thread;
-- show parameter是oracle的命令，不是标准SQL语句
-- 可以在sqlplus或者pl/sql dev的命令窗口执行
-- show parameter aaaa;等价于SELECT * FROM v$parameter WHERE name like '%aaaa%';
SELECT * FROM v$parameter WHERE NAME LIKE '%name%'; --等价于show parameter name;
SELECT * FROM v$parameter WHERE NAME LIKE '%db_domain%'; --查询数据库域名
--增删改操作回滚语句:
SELECT first_load_time FROM v$sql WHERE sql_text LIKE '%需要恢复的语句%'; --查出时间点
CREATE TABLE hyz_back AS --新的表
SELECT *
FROM table_name --你误操作的表
AS OF TIMESTAMP to_timestamp('2018-01-17 19:37:20', 'yyyy-mm-dd hh24:mi:ss'); --时间点
--执行计划查询
explain plan for
select * from table;--SQL语句
select * from table(dbms_xplan.display);
