Oracle 临时表、数据闪回、查询被锁表、系统常用表、及常用操作Oracle 系统常用表/视图Oracle 临时表概述与创建flashback 闪回表中被删除的数据flashback 闪回整个被 drop 的表查询 Oracle 中被锁定的表查询 Oracle 中未提交的事务设置 Oracle 最大连接数Oracle 数据库表调整字段顺序查找表的操作记录

86 阅读 0 评论 57 点赞

我是靠谱客的博主缓慢小馒头，最近开发中收集的这篇文章主要介绍Oracle 临时表、数据闪回、查询被锁表、系统常用表、及常用操作Oracle 系统常用表/视图Oracle 临时表概述与创建flashback 闪回表中被删除的数据flashback 闪回整个被 drop 的表查询 Oracle 中被锁定的表查询 Oracle 中未提交的事务设置 Oracle 最大连接数Oracle 数据库表调整字段顺序查找表的操作记录，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

Oracle 系统常用表/视图

Oracle 临时表概述与创建

flashback 闪回表中被删除的数据

flashback 闪回整个被 drop 的表

查询 Oracle 中被锁定的表

查询 Oracle 中未提交的事务

设置 Oracle 最大连接数

Oracle 数据库表调整字段顺序

查找表的操作记录

Oracle 系统常用表/视图

1、Oracle 内部使用了大量的表和视图来维护用户的数据，所以可以使用 Oracle 内置的表或者视图来进行查询。

名称	类型	举例
user_col_comments	视图	select * from user_col_comments t where t.TABLE_NAME = 'EMP'; --查询表/视图字段的注释信息（不含表/视图本身）
user_tab_comments	视图	select * from user_tab_comments a;--查询表/视图的注释信息（不含字段）
user_tab_columns	视图	select table_name ,column_name,data_type,data_length,nullable from user_tab_columns t where t.TABLE_NAME = 'EMP'; --查询表/视图的列信息，如列名称、类型、长度等等
user_tab_cols	视图	select * from user_tab_cols t where t.TABLE_NAME = 'EMP'; --查询表/视图的列信息，如列名称、类型、长度等等。 --查询结果中会出现类似名称为 'SYS_NC00082$' 的列，所以推荐使用 user_tab_cols .
user_tables	视图	select * from user_tables; -- 查询当前登陆用户下所有的表，其中的 duration（持续时间）为 null 表示非临时表，SYS$SESSION 表示会话临时表，SYS$TRANSACTION 表示事务临时表
user_views	视图	select * from user_views;-- 查询当前登陆用户下所有的视图，
dba_jobs	视图	oracle 定时任务 -- JOB：每个任务的唯一标识符 -- LOG_USER：提交作业时登录的用户 -- LAST_DATE、LAST_SEC：上次成功执行此作业的日期 -- THIS_DATE、THIS_SEC：当前正在运行的作业的开始时间，不在运行时为 null. -- NEXT_DATE、NEXT_SEC：下一次执行此作业的日期 -- TOTAL_TIME：系统在此作业上花费的总挂钟时间，以秒为单位 -- BROKEN：如果为Y，则表示此作业已停止运行 -- failures：执行失败的次数
SELECT T.* FROM all_col_comments t ;-- 查询表/视图字段的注释信息(含所有用户) SELECT T.* FROM all_tab_comments t ;-- 查询表/视图的注释信息（不含字段，含所有用户） SELECT T.* FROM all_tab_columns t ;-- 查询表/视图的列信息，如列名称、类型、长度等等(含所有用户) SELECT T.* FROM all_tab_cols t ;--查询表/视图的列信息，如列名称、类型、长度等等(含所有用户) SELECT T.* FROM all_tables t ;-- 查询所有表(含所有用户) SELECT T.* FROM all_views t ;-- 查询所有视图(含所有用户)
all_objects	视图	所有用户创建的对象(比如表、视图、索引、定时任务、函数、存储过程、触发器等等)，可以查看对象创建的时间，最后修改的时间等等
user_objects	视图	当前登陆用户创建的对象(比如表、视图、索引、定时任务、函数、存储过程、触发器等等)，可以查看对象创建的时间，最后修改的时间等等
user_indexes dba_indexes	视图	查看当前/全部用户下创建的全部索引信息：如索引名称、类型、所属的表名称，是否被禁用等等
USER_constraints dba_constraints	视图	查看当前/全部用户创建的全部约束信息：如约束名称、约束类型、所属的表、最后修改时间、是否启用、约束名称是用户自定义还是系统自动新建等等。 constraint type：约束类型：常见的值为：C(普通约束)、R(外键约束)、P(主键约束)、U(唯一约束)
user_cons_columns dba_cons_columns	视图	查看当前/全部用户创建的全部约束对应的列名，用于根据约束的名称查询约束对应的列名。
select * from v$instance; -- 查看数据库实例(含版本信息、启动时间，是否开启等等) select * from product_component_version ;-- 查看数据库版本 select * from v$version; -- 查看数据库版本
select userenv('language') from dual; -- 查看字符集
--查找NVARCHAR2的字符集 SELECT * FROM nls_database_parameters WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET'; --查询VARCHAR2数据类型使用的缺省字符集 SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
-- ORACLE 查询某个表被所有用户下的哪些视图引用 select * from dba_dependencies where REFERENCED_NAME='表名' and TYPE='VIEW'; -- ORACLE 查询某个表被当前用户下的哪些视图引用 select * from user_dependencies where REFERENCED_NAME='表名' and TYPE='VIEW'; --OWNER：依赖对象所属的用户名称 --NAME：依赖对象的名称，如视图名称，触发器名称等等 --TYPE：依赖对象类型:TRIGGER、PROCEDURE、FUNCTION、TYPE、VIEW 等等 --REFERENCED_OWNER：被依赖对象所属的用户名称 --REFERENCED_NAME：被依赖对象的名称，如表名，视图名称等等 --REFERENCED_TYPE：被依赖对象类型，SYNONYM、PACKAGE、FUNCTION、TYPE、TABLE、VIEW --REFERENCED_LINK_NAME：如果被倚赖的对象是一个远程表，这里显示dblink的名字，否则为空 --DEPENDENCY_TYPE：从属类型

2、示例：将已经存在的多个表的字段合成一个新表.md。

3、统计当前环境的表结构与部标规范的差异.md。

Oracle 临时表概述与创建

临时表概述

1、临时表用于存储"事务"或"会话"的中间结果集数据，临时表中保存的数据只对当前会话或事务可见，所有会话都看不到其他会话的数据，所以临时表不存在并发行为。

2、临时表分事务级临时表和会话级临时表，事务级临时表只对当前事务有效，每次提交或者回滚后 ORACLE 将删除临时表中的数据；会话级临时表只对当前会话有效，当会话中断/结束后，ORACLE 将删除表中所有的数据：

3、创建临时表时，Oracle 只创建了表的结构，并没有初始化内存空间，只有向临时表中插入数据时，才会给临时表分配存储空间。

4、临时表也是实实在在存在的表，可以对它创建索引、视图、触发器等操作，与非临时表的区别主要在于数据的可见性与存在时间。

临时表创建

事务级临时表创建语法：create global temporary table <temp_table_name> (<column specification>) on commit delete rows;

使用举例（根据现有表创建事务临时表，这里只复制表结构，不复制数据）：

create global temporary table transaction_temp_dept on commit delete rows as select * from dept where 1=2;

会话级临时表创建语法：create global temporary table <temp_table_name> (<column specification>) on commit preserve rows;

使用举例（根据现有表创建会话临时表，这里只复制表结构，不复制数据）：

create global temporary table session_temp_dept on commit preserve rows as select * from dept where 1=2;

//创建临时表即可根本已经存在表进行创建，也可以根据视图创建，如：create global temporary table session_temp_dept on commit preserve rows as select * from vw_dept where 1=2;

温馨提示：

如果 Java 程序中使用代码自动创建临时表，比如导入数据的时候，如果临时表不存在，则自动创建，如果存在，则直接使用。在 Java 程序运行过程中，如果手动删除临时表，程序访问时会直接报错："表或者视图不存在"，必须重启程序再次访问时才会自动创建。

有时候创建的临时表会存在不允许为 null 的字段，如果允许为 null，则可以进行修改：

-- 设置某个的字段允许为 null
declare
cursor vrows is SELECT t.COLUMN_NAME FROM user_tab_columns t where t.TABLE_NAME='BAS_PERSON_IMP_430000000_202009' and t.NULLABLE='Y' order by t.COLUMN_ID;
begin
  for vrow in vrows loop
    dbms_output.put_line('alter table BAS_PERSON_IMP_430000000_202009 modify ' || vrow.COLUMN_NAME || ' null;');
  end loop;
end;

临时表查询

1、查看当前用户下的表是否为临时表：

-- 其中的 duration（持续时间）为 null 表示非临时表，SYS$SESSION 表示会话临时表，SYS$TRANSACTION 表示事务临时表
select * from user_tables where duration is not null;

临时表删除

1、事务临时表提交或者回滚后，只是删除其中的数据，表结构仍然还在，会话临时表也是一样，会话结束后，数据删除了，当表结构还在。

2、如果会话临时表的会话没有结束，则无法删除此临时表，事务临时表也是同理，也只能在未被使用时才能删除。

事务级临时表验证

1、事务级临时表只对当前事务有效，每次提交或者回滚后 ORACLE 将删除临时表中的数据，回滚后删除数据是自然的，无论是否为临时表都会这样，所以主要是验证事务提交数据删除。

会话级临时表验证

1、会话级临时表只对当前会话有效，当会话中断/结束后，ORACLE 将删除表中所有的数据，会话之间也无法相互看到数据。

flashback 闪回表中被删除的数据

1、开发中甚至是生产中偶尔会遇到因为误操作删除了数据，特别是生产中特别危险，鄙人有幸就因为 where 条件写反了，把生产上表中本该删除的数据留下了，本该留下的数据却删除了，当时也是小心脏扑通扑通的。

2、flashback table xxx to timestamp ：恢复数据的方式是将整个表中的数据退回到之前的某个时间点，这很像电脑操作系统的恢复功能，同样也是将系统恢复到之前某个快照。

3、flashback table dept to timestamp 方式只适用于 delete 删除的数据，如果 truncate 删除的则没有办法。比如 10点05分15秒误删了，10点10分15秒才发现这个问题，此时将整个表退回到5分钟以前，就意味着这5分钟内表中发生的数据变化，都将因为退回而没有了。

4、所以一旦发生误删数据情况：

第一步：最好暂时先让其它人无法继续修改表中的数据。

第二步：找到之前误删的数据，记录此时间点

第三步：将此时表中的数据进行备份，因为时间差内可能会有数据变化

第四步：将整个表使用 flashback 命令退回到第二步的时间点，此时误删除的数据已经回来了

第五步：比照第四步退回后的数据与第三步最新的数据两者之间的变化，因为这个时间差内可能有用户新增、删除、修改数据，这些变化的数据也需要处理。

5、下面使用 scott 用户的 dept 部门表进行演示：

select * from dept ;--查询数据

delete from dept t where t.deptno in(50,60);--删除删除，假设是误删除

--开始恢复数据

--1、查询数据库当前时间（防止数据库时间与自己参照的时间不一致）
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

--2、查询指定时刻的数据，反复修改时间点，直到找到被删除的数据，越精确越好
select * from dept as of timestamp to_timestamp('2020-07-26 17:00:50','yyyy-mm-dd hh24:mi:ss') where deptno = 10;

--3、恢复表中的数据到某个时刻
flashback table dept to timestamp to_timestamp('2020-07-26 17:00:50','yyyy-mm-dd hh24:mi:ss');

--4、如果第三步恢复报错：ORA-08189：因为未启用行移动功能，不能闪回表；则执行如下语句，然后继续执行第三步即可。
alter table dept enable row movement;

--为指定表关闭行移动功能
alter table tableName disable row movement;

！！！除非百分百确定数据不再需要，否则不要用 truncate 删除，而应该使用 delete 进行删除，truncate 删除的数据无法恢复，或者说难以恢复。

flashback 闪回整个被 drop 的表

1、flashback table xxx to before drop：从回收站中闪回表到删除前，用于闪回被 drop table xxx 的表。

2、特别注意：拥有管理员权限的用户没有回收站，所以无法使用此种方式闪回，所以管理员删除表千万要慎重。下面通过 scott 的 emp 表进行演示：

create table emp_temp as select * from emp; --创建一个表（emp_temp）用于测试

select * from emp_temp;--查询表数据

drop table emp_temp; -- 删除表

show recyclebin; -- 查看回收站，drop(删除)的表会在其中有记录。不能在 PL/SQL 中执行此命令，需要到 sqlplus 等命令行中执行

flashback table emp_temp to before drop; -- 闪回整个表（emp_temp）到删除前，执行之后表就已经恢复了。

-- 如果想清空回收站，可以执行命令：purge recyclebin;

-- 如果需要被闪回的表名已经被新建的表占用了，此时直接闪回是会失败的，解决办法是闪回的同时重命名：

flashback table emp_temp to before drop rename to emp_old;

查询 Oracle 中被锁定的表

--查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT l.session_id sid,
    s.serial#,
    l.locked_mode as 锁模式,
    l.oracle_username as  登录用户,
    l.os_user_name as 登录机器用户名,
    s.machine as 机器名,
    'ALTER SYSTEM KILL SESSION ''' || SESSION_ID || ', ' || SERIAL# || '''; ' as 关闭会话sql,
    s.terminal as 终端用户名,
    o.object_name as 被锁对象名,
    s.logon_time as 登录数据库时间
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY sid, s.serial#;

查询 Oracle 中未提交的事务

-- 查询未提交的事务，对于应用系统突然故障，查询他未提交的事务很有用
SELECT s.sid,
       s.serial#,
        'ALTER SYSTEM KILL SESSION ''' ||  s.sid || ', ' || s.serial# || '''; ' 关闭会话sql,
       s.username as 用户名,
       s.status as 状态,
       s.machine as 机器名称,
       s.terminal as 终端,
       s.program as 程序,
       a.executions,
       s.sql_id,
       p.spid,
       a.direct_writes,
       s.event as 事件,
       a.sql_text as sql文本,
       a.sql_fulltext
  FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s
  LEFT JOIN v$sqlarea a
    ON s.sql_id = a.sql_id
 INNER JOIN v$process p
    ON s.paddr = p.addr;

设置 Oracle 最大连接数

1、有些时候当我们的应用或者 PL/sql 连接 Oracle 数据时会报如下的错误，表示拒绝连接，这个时候通常是因为连接数过多，从而超出了 Oracle 允许的连接数(默认为150)导致的。假如 10 个服务连接数据库，每个服务启动时初始化20个连接数，则就会导致部分服务启动时或者启动后连接不上数据库，因为超出了 Oralce 的最大允许连接数。

java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

2、可以通过修改 Oracle 最大连接数来解决这个问题：

select count(*) from v$session t; -- 查询 Oracle 当前的会话数
select count(*) from v$process; --查询 Oracle 当前的连接数
select count(*) from v$session where status='ACTIVE' -- 查询Oracle当前并发连接数

select value from v$parameter where name = 'processes'; -- 查询 Oracle 允许的最大连接数
select value from v$parameter where name = 'sessions'; -- 查询 Oracle 允许的最大会话数

alter system set processes = 1024 scope = spfile; --修改 Oracle 允许的最大连接数.重启数据库服务（OracleServiceORCL）后生效。

alter system set sessions = 1024 scope=spfile; --修改 Oracle 允许的最大会话数.重启数据库服务（OracleServiceORCL）后生效。

ORA-32001: 已请求写入 SPFILE, 但是没有正在使用的 SPFILE 报错解决方式。

完整操作演示：https://gitee.com/wangmx1993/my-document/blob/master/sql/Oracle 修改最大连接数.sql

Oracle 数据库表调整字段顺序

1、数据库表结构确定之后，后期再往表中增加字段时，默认会放到表的最后，有时候我们想把有关联性的字段放在一起，或者其它原因需要调整字段顺序。

方式一：如果表中的数据不多，则最简单高效的方式就是重建(优点是方便，缺点是此种建表的方式会丢失主外键，约束，索引等信息)：

-- 1、以新的顺序新建表，同时复制旧表中的数据
-- 子查询建的表只复制了目标表的结构（如字段名称、数据类型），却不复制约束、索引（如主外键、约束、索引）.
create table emp_temp as select EMPNO, ENAME, JOB, MGR, SAL, HIREDATE, COMM, DEPTNO from emp ;

-- 2、删除旧表或者将旧表重命名做留底
drop table EMP;

-- 3、将新表重命名为原来的表名，新顺序调整完成
alter table emp_temp rename to emp;

查找表的操作记录

--1，首先查找表的操作记录
select A.SQL_ID, A.SQL_TEXT, A.FIRST_LOAD_TIME, A.*
  from v$sqlarea a
 where a.SQL_TEXT like '%BAS_PERSON_INFO%'
 ORDER BY A.FIRST_LOAD_TIME DESC;

--2、从上面的记录中找到最新的sql操作记录，然后找到用户名和主机
select L.OSUSER, L.USERNAME, L.PREV_EXEC_START, S.*
  from sys.v_$session l, sys.v_$sql s
 where s.SQL_ID = 'dzx488n8ak1xp'
   and l.USERNAME is not null
 ORDER BY PREV_EXEC_START DESC;