Oracle 临时表、数据闪回、系统常用表、及常用操作

心已赠人 2023-03-01 12:48 6阅读 0赞

**目录**

Oracle 系统常用表/视图

Oracle 临时表概述与创建

flashback  闪回表中被删除的数据

flashback  闪回整个被 drop 的表

查询 Oracle 中被锁定的表

查询 Oracle 中未提交的事务

设置 Oracle 最大连接数

Oracle 数据库表调整字段顺序

--------------------

# Oracle 系统常用表/视图 #

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

<table> 
 <tbody> 
 <tr> 
 <td style="width:168px;">名称</td> 
 <td style="width:59px;">类型</td> 
 <td style="width:675px;">举例</td> 
 </tr> 
 <tr> 
 <td style="width:168px;">user_col_comments</td> 
 <td style="width:59px;">视图</td> 
 <td style="width:675px;">select * from user_col_comments t where t.TABLE_NAME = 'EMP'; --查询表/视图字段的注释信息（不含表/视图本身）</td> 
 </tr> 
 <tr> 
 <td style="width:168px;">user_tab_comments</td> 
 <td style="width:59px;">视图</td> 
 <td style="width:675px;">select * from user_tab_comments a;--查询表/视图的注释信息（不含字段）</td> 
 </tr> 
 <tr> 
 <td style="width:168px;">user_tab_columns</td> 
 <td style="width:59px;">视图</td> 
 <td style="width:675px;">select table_name ,column_name,data_type,data_length,nullable from user_tab_columns t where t.TABLE_NAME = 'EMP'; --查询表/视图的列信息，如列名称、类型、长度等等</td> 
 </tr> 
 <tr> 
 <td style="width:168px;">user_tab_cols&nbsp;</td> 
 <td style="width:59px;">视图</td> 
 <td style="width:675px;">select * from user_tab_cols&nbsp;t where t.TABLE_NAME = 'EMP'; --查询表/视图的列信息，如列名称、类型、长度等等</td> 
 </tr> 
 <tr> 
 <td style="width:168px;">user_tables</td> 
 <td style="width:59px;">视图</td> 
 <td style="width:675px;">select * from user_tables; -- 查询当前登陆用户下所有的表，其中的 duration（持续时间）为 null 表示非临时表，SYS$SESSION 表示会话临时表，SYS$TRANSACTION 表示事务临时表</td> 
 </tr> 
 <tr> 
 <td style="width:168px;">user_views</td> 
 <td style="width:59px;">视图</td> 
 <td style="width:675px;">select * from user_views;-- 查询当前登陆用户下所有的视图，</td> 
 </tr> 
 <tr> 
 <td style="width:168px;">dba_jobs</td> 
 <td style="width:59px;">视图</td> 
 <td style="width:675px;"> oracle 定时任务 -- JOB：每个任务的唯一标识符 -- LOG_USER：提交作业时登录的用户 -- LAST_DATE、LAST_SEC：上次成功执行此作业的日期 -- THIS_DATE、THIS_SEC：当前正在运行的作业的开始时间，不在运行时为 null. -- NEXT_DATE、NEXT_SEC：下一次执行此作业的日期 -- TOTAL_TIME：系统在此作业上花费的总挂钟时间，以秒为单位 -- BROKEN：如果为Y，则表示此作业已停止运行 -- failures：执行失败的次数 </td> 
 </tr> 
 <tr> 
 <td colspan="3" style="width:168px;">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&nbsp; t ;--查询表/视图的列信息，如列名称、类型、长度等等(含所有用户) SELECT T.* FROM all_tables t ;-- 查询所有表(含所有用户) SELECT T.* FROM all_views t ;-- 查询所有视图(含所有用户)</td> 
 </tr> 
 <tr> 
 <td style="width:168px;">all_objects</td> 
 <td style="width:59px;">视图</td> 
 <td style="width:675px;">所有用户创建的对象(比如表、视图、索引、定时任务、函数、存储过程、触发器等等)，可以查看对象创建的时间，最后修改的时间等等</td> 
 </tr> 
 <tr> 
 <td style="width:168px;">user_objects</td> 
 <td style="width:59px;">视图</td> 
 <td style="width:675px;">当前登陆用户创建的对象(比如表、视图、索引、定时任务、函数、存储过程、触发器等等)，可以查看对象创建的时间，最后修改的时间等等</td> 
 </tr> 
 </tbody> 
</table>

2、[系统字段与注释表应用][Link 1].sql

# Oracle 临时表概述与创建 #

**临时表概述**

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

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

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

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

**临时表创建**

<table> 
 <tbody> 
 <tr> 
 <td> 事务级临时表创建语法：create global temporary table&nbsp; &lt;temp_table_name&gt; (&lt;column specification&gt;) 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 &lt;temp_table_name&gt; (&lt;column specification&gt;) 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; </td> 
 </tr> 
 </tbody> 
</table>

**温馨提示：**

如果 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、查看当前用户下的表是否为临时表：

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

**临时表删除**

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

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

![20200726091415189.gif][]

**事务级临时表验证**

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

![20200726103048953.gif][]

**会话级临时表验证**

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

![20200726145856392.gif][]

# 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、所以一旦发生误删数据情况：

<table> 
 <tbody> 
 <tr> 
 <td> 第一步：最好暂时先让其它人无法继续修改表中的数据。 第二步：找到之前误删的数据，记录此时间点 第三步：将此时表中的数据进行备份，因为时间差内可能会有数据变化 第四步：将整个表使用 flashback&nbsp; 命令退回到第二步的时间点，此时误删除的数据已经回来了 第五步：比照第四步退回后的数据与第三步最新的数据两者之间的变化，因为这个时间差内可能有用户新增、删除、修改数据，这些变化的数据也需要处理。 </td> 
 </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
 <tr> 
 <td> select * from dept ;--查询数据 delete from dept t where t.deptno in(50,60);--删除删除，假设是误删除 --开始恢复数据 --1、查询数据库当前时间（防止数据库时间与自己参照的时间不一致） select&nbsp; 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'); --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; </td> 
 </tr> 
 </tbody> 
</table>

![20200726184704906.gif][]

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

# flashback  闪回整个被 drop 的表 #

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

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

<table> 
 <tbody> 
 <tr> 
 <td> 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; </td> 
 </tr> 
 </tbody> 
</table>

![20200904103014347.gif][]

--------------------

# 查询 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 最大连接数来解决这个问题：

<table> 
 <tbody> 
 <tr> 
 <td>select count(*) from v$session t; -- 查询 Oracle 当前的会话数 select count(*) from v$process;&nbsp;&nbsp;&nbsp; --查询 Oracle 当前的连接数 select count(*) from v$session where status='ACTIVE' -- 查询Oracle当前并发连接数</td> 
 <td></td> 
 </tr> 
 <tr> 
 <td>select value from v$parameter where name = 'processes'; -- 查询 Oracle 允许的最大连接数 select value from v$parameter where name = 'sessions';&nbsp; -- 查询 Oracle 允许的最大会话数</td> 
 <td></td> 
 </tr> 
 <tr> 
 <td> alter system set processes = 1024 scope = spfile; --修改 Oracle 允许的最大连接数.重启数据库服务（OracleServiceORCL）后生效 。 alter system set sessions = 1024 scope=spfile; --修改 Oracle 允许的最大会话数.重启数据库服务（OracleServiceORCL）后生效 。 </td> 
 <td></td> 
 </tr> 
 </tbody> 
</table>

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

完整操作演示：[https://gitee.com/wangmx1993/my-document/blob/master/sql/][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;

[Link 1]: https://gitee.com/wangmx1993/material/blob/master/sql/oracle/05_%E7%B3%BB%E7%BB%9F%E5%AD%97%E6%AE%B5%E4%B8%8E%E6%B3%A8%E9%87%8A%E8%A1%A8%E5%BA%94%E7%94%A8.sql
[20200726091415189.gif]: /images/20230208/9506dd1d080d407a8f6bd3f9b146e2a5.png
[20200726103048953.gif]: /images/20230208/4b5e108e6cb74be5a9bd266999af23c6.png
[20200726145856392.gif]: /images/20230208/40cb80e00a3e4b04a4330d418105c416.png
[20200726184704906.gif]: /images/20230208/88e2d548f92841daad22e61fd6fe3a56.png
[20200904103014347.gif]: /images/20230208/8b334735eccf471c9dd441cb6cdee910.png
[ORA-32001_ _ SPFILE_ _ SPFILE]: https://wangmaoxiong.blog.csdn.net/article/details/106134401#t7
[https_gitee.com_wangmx1993_my-document_blob_master_sql]: https://gitee.com/wangmx1993/my-document/blob/master/sql/Oracle%20%E4%BF%AE%E6%94%B9%E6%9C%80%E5%A4%A7%E8%BF%9E%E6%8E%A5%E6%95%B0.sql