ORACLE学习之常用数据库系统表语句_char col decl length-CSDN博客

本文链接：https://blog.csdn.net/qq493820798/article/details/81867885

说明

本文介绍系统表的相关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);