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

目录

Oracle 系统常用表/视图

Oracle 临时表概述与创建

flashback  闪回表中被删除的数据

flashback  闪回整个被 drop 的表

阻塞 & 锁

查询 Oracle 中被锁定的表

查询 Oracle 中未提交的事务

查看死锁

设置 Oracle 最大连接数

Oracle 数据库表调整字段顺序

查找表的操作记录

---

Oracle 系统常用表/视图

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

静态数据字典中的视图分为三类，它们分别由三个前缀够成： user_* 、 all_* 、dba_* 。
user_*	该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象）
all_*	该试图存储 了当前用户能够访问的对象的信息 而不是当前用户拥有的对象。（与 user_* 相比，all_* 并不需要拥有该对象，只需要具有访问该对象的权限即可）
dba_*	该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限）
除了静态数据字典中三类视图， 其他的字典视图中主要的是 V$ 视图，这些视图会不断的进行更新，从而提供了关于内存和磁盘的运行情况， 。 V$ 视图是 SYS 用户所拥有的，在缺省状况下，只有 SYS 用户和拥有 DBA 系统权限的用户可以看到所有的视图 ，没有 DBA 权限的用户可以看到 USER_ 和 ALL_ 视图，但不能看到 DBA_ 视图。
-- 查看所有数据字典对象 select * from dictionary;
-- 查看当前连接用户 select user from dual;

名称	类型	举例
select * from v$instance; -- 查看数据库实例(含版本信息、启动时间，是否开启等等)，需要管理员权限 select * from product_component_version ;-- 查看数据库版本 select * from v$version; -- 查看数据库版本 select * from v$option;  --查看数据库的组件信息 select userenv('language') from dual;  -- 查看字符集
--查询数据库以用户分组连接数。需要管理员权限 select inst_id, username, count(*)  from gv$session group by inst_id, username; --查询实例当前会话数和启动最高连接会话数量。需要管理员权限 select sessions_current,sessions_highwater from v$license;
--查询给定参数的设置值，示例参数缺省值为300，通常中等规模数据库推荐设置为1000。需要管理员权限 select * from v$parameter where name='open_cursors'; --修改给定的初始化参数，RAC环境需要注意SID参数。需要管理员权限 alter system set open_cursors=1000 comment='default 300' sid='*' scope=both;
--获取会话或全局转储位置，诊断时需检查相应Trace文件内容。 select value from v$diag_info where name='Default Trace File'; select * from sys.sm$ts_used;--查看数据库表空间的使用信息。 select tablespace_name,file_name from dba_data_files;--查看数据库表空间的数据文件信息。
--通过等待事件和等待时间，了解数据库当前连接会话的等待情况。如果会话众多，需要限定查询输出行数。 select sid,event,wait_time_micro from v$session_wait order by 3; --查看当前数据中TOP10等待事件信息，需要分析和关注非空闲的显著等待。 select * from (select event,total_waits,average_wait,time_waited from v$system_event where wait_class<>'ldle' order by time_waited desc) where rownum<=10;
--查询给定表(需大写)最后的统计信息分析收集时间。 select table_name,last_analyzed from dba_tab_statistics where table_name='&table_name';
--查看系统中分区表的基本信息：分区类型，数量，边界值等。 select table_name,partitioning_type,partition_count,status from dba_part_tables; select table_name,partition_name,high_value from dba_tab_partitions where rownum <11; --查看当前用户下分区表的基本信息：分区类型，数量，边界值等。 select table_name,partitioning_type,partition_count,status from user_part_tables; select table_name,partition_name,high_value from user_tab_partitions where rownum <11;
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 .
select * from user_tables; -- 查询当前登陆用户下所有的表，其中的 duration（持续时间）为 null 表示非临时表，SYS$SESSION 表示会话临时表，SYS$TRANSACTION 表示事务临时表。 num_rows 表示表中的数据行数 --查看当前用户下表中记录行数最多的10个表，表的基本信息数据：属主，表名，记录行数等。  select * from (select table_name,num_rows from user_tables order by num_rows desc nulls last) where rownum<11; --查看全部用户表中记录行数最多的10个表，表的基本信息数据：属主，表名，记录行数等。  select * from (select owner,table_name,num_rows from dba_tables order by num_rows desc nulls last) where rownum<11;
-- 查看当前用户定义的表, 视图, 同义词、序列 select * from user_catalog t; -- 查看全部用户定义的表, 视图, 同义词、序列 select * from dba_catalog t;
-- 查看回收站（方式2-推荐） select * from recyclebin order by droptime desc;--查看当前用户下删除的表、触发器、索引等等对象。 select * from user_recyclebin order by droptime desc;--查看当前用户下删除的表、触发器、索引等等对象。 select * from dba_recyclebin   order by droptime desc;--查看管理员权限下删除的表、触发器、索引等等对象。
select * from user_views;-- 查询当前登陆用户下所有的视图 SELECT * FROM t all_views;-- 查询所有视图(含所有用户) TEXT_LENGTH：视图内容长度 TEXT：视图完整内容，Long 类型，最大长度限制是 2GB TEXT_VC：视图内容截断为 VARCHAR2(4000)，即展示最长 4000个字符长度，超过的内容会被截断。
dba_jobs	视图	oracle 定时任务 -- JOB：每个任务的唯一标识符 -- LOG_USER：提交作业时登录的用户 -- LAST_DATE、LAST_SEC：上次成功执行此作业的日期 -- THIS_DATE、THIS_SEC：当前正在运行的作业的开始时间，不在运行时为 null. -- NEXT_DATE、NEXT_SEC：下一次执行此作业的日期 -- TOTAL_TIME：系统在此作业上花费的总挂钟时间，以秒为单位 -- BROKEN：如果为Y，则表示此作业已停止运行 -- failures：执行失败的次数
--查询系统全部用户的定时任务(job)信息，确保任务在期望的时间成功执行。 select job,log_user,last_date,next_date,interval,broken,failures from dba_jobs; --查询当前用户的定时任务(job)信息，确保任务在期望的时间成功执行。 select job,log_user,last_date,next_date,interval,broken,failures from user_jobs; --查询系统定时调度信息，查询显示了任务名称、初始启动日期以及重复间隔。 select job_name,start_date,repeat_interval from dba_scheduler_jobs;
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 ;-- 查询所有表(含所有用户)
all_objects	视图	所有用户创建的对象(比如表、视图、索引、定时任务、函数、存储过程、触发器等等)，可以查看对象创建的时间，最后修改的时间等等
user_objects	视图	当前登陆用户创建的对象(比如表、视图、索引、定时任务、函数、存储过程、触发器等等)，可以查看对象创建的时间，最后修改的时间等等
--检查数据库中的失效对象信息，通常运行健康的数据库中不应有失效的对象。 select owner,object_name,object_type,status from dba_objects where status<> 'VALID'; --检查当前用户下的失效对象信息，通常运行健康的数据库中不应有失效的对象。 select object_name,object_type,status from user_objects where status<> 'VALID';
user_indexes dba_indexes	视图	查看当前/全部用户下创建的全部索引信息：如索引名称、类型、所属的表名称，是否被禁用等等
--查看当前用户索引对象对应的具体的列信息 select * from user_ind_columns; --查看全部用户索引对象对应的具体的列信息 select * from dba_ind_columns;
--查询索引的基本信息，输出包括叶块数和聚簇因子等，如聚簇因子接近行数可能代表索引效率不高。 select * from (select index_name,num_rows,leaf_blocks,clustering_factor from dba_indexes order by 4 desc nulls last) where rownum<11;
USER_constraints dba_constraints	视图	查看当前/全部用户创建的全部约束信息：如约束名称、约束类型、所属的表、最后修改时间、是否启用、约束名称是用户自定义还是系统自动新建等等。 constraint type：约束类型：常见的值为：C(普通约束)、R(外键约束)、P(主键约束)、U(唯一约束)
--查询指定数据表的约束信息，包括名称和类型。 select constraint_name,constraint_type from dba_constraints where table_name=upper('&输入被查的表名');
user_cons_columns dba_cons_columns	视图	查看当前/全部用户创建的全部约束对应的列名，用于根据约束的名称查询约束对应的列名。
--查找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：从属类型
user_source	视图	查询触发器(TRIGGER)、存储过程(PROCEDURE)、函数(FUNCTION)，类型(Type) 等对象的源代码。 SELECT * FROM user_source T where t.NAME='xxx' order by t.LINE;
--查询当前用户下创建的所有序列 SELECT * FROM USER_SEQUENCES; --查询全部用户下创建的所有序列 SELECT * FROM dba_SEQUENCES;

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; --省略[on commit delete rows]时，默认也是创建的事务级临时表 create global temporary table transaction_temp_dept2 as select * from dept where 1=2; create global temporary table transaction_temp_dept3(guid varchar2(38),uuid varchar2(38)); 会话级临时表创建语法：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、特别注意：

• 管理员用户(不包括拥有dba权限的用户)没有回收站，无法使用此种方式闪回，所以管理员删表千万要慎重。
• 通过回收站的中名字可以查询被删除对象的数据，使用双引号括起来，比如：select * from "BIN$rWLNVqlxTSubWrDOvJUmfg==$0";

3、下面通过 scott 的 emp 表进行演示：

create table emp_temp as select * from emp; --创建一个表（emp_temp）用于测试 select * from emp_temp;--查询表数据 drop table emp_temp; -- 删除表

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

-- 查看回收站（方式2-推荐） select * from recyclebin order by droptime desc;--查看当前用户下删除的表、触发器、索引等等对象。 select * from user_recyclebin order by droptime desc;--查看当前用户下删除的表、触发器、索引等等对象。 select * from dba_recyclebin   order by droptime desc;--查看管理员权限下删除的表、触发器、索引等等对象。

purge recyclebin;    --清空回收站，慎用，清空之后将无法使用这种方式再恢复数据。

flashback table emp_temp to before drop; -- 闪回整个表（emp_temp）到删除前，执行之后表就已经恢复了。 -- 如果需要被闪回的表名已经被新建的表占用了，此时直接闪回是会失败的，解决办法是闪回的同时重命名： flashback table emp_temp to before drop rename to emp_old;

---

阻塞 & 锁

1、当两个用户希望持有对方的资源时就会发生死锁，即两个用户互相等待对方释放资源时，oracle 认定为产生了死锁，在这种情况下 将以牺牲一个用户作为代价，另一个用户继续执行，牺牲的用户的事务将回滚 。

死锁例子：
a：用户 1 对 A 表进行 Update ，没有提交。
b：用户 2 对 B 表进行 Update ，没有提交。
此时双反不存在资源共享的问题。
c：如果用户 2 此时对 A 表作 update, 则会发生阻塞，需要等到用户一的事物结束。
d：如果此时用户 1 又对 B 表作 update ，则产生死锁。此时 Oracle 会选择其中一个用户进行会滚，使另一个用户继续执行操作。

2、通常来讲，系统如果平时运行正常，突然会停止不动，多半是被阻塞（ Blocked）住了。当一个会话保持另一个会话正在请求的资源上的锁定时，就会发生阻塞。被阻塞的会话将一直挂起，直到持有锁的会话放弃锁定的资源为止。

引起阻塞原因	描述
INSERT	Insert 发生阻塞的唯一情况是当 2 个会话同时试图向建有主键约束的表中插入相同主键的数据时，其中的一个会话将被阻塞，直到另外一个会话提交或会滚。一个会话提交时，另一个会话将收到主键重复的错误，回滚时，被阻塞的会话将继续执行。
Update 和 Delete	当执行 Update 和 delete 操作的数据行已经被另外的会话锁定时，将会发生阻塞，直到另一个会话提交或会滚。
Select …for update	当一个用户发出select..for update 的错作准备对返回的结果集进行修改时，如果结果集已经被另一个会话锁定，此时 Oracle 已 经对返回的结果集上加了排它的行级锁， 所有其他对这些数据进行的修改或删除操作都必须等待这个锁的释放操作 commit 或 rollback.)rollback.)，产生的外在现象就是其他的操作将发生阻塞同样这个查询的事务将会对该表加表级锁，不允许对该表的任何 ddl 操作，否则将会报出 Ora 00054:resource busy and acquire with nowait specified.
外键没有创建索引	主表上有频繁的删除操作,以及主键上有频繁的修改操作时，主表会在从表上创建一个锁定， 以保证主表主键的修改不会导致从表的数据在引用上出现问题，这是一个数据引用完整性的要求。 如果主表上出现频繁的删除或者是对主键列频繁的修改，或者每次操作的记录数很多，都将会造成从表长时间被锁定，而影响其他用户的正常操作。 比如主表每次删除 1000 行数据，它就需要扫描从表 1000 次，以确定每一行记录的改变都不会造成从表数据在引用上的不完整。

查询 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;

查看死锁

--查看有无死锁对象，如果有，则 kill session
--注意：对于 sid 在 100 以下的应当谨慎，可能该进程对应某个application，如对应某个事务 可以 kill.
SELECT t1.sid, 'alter system kill session ''' || t1.sid || ',' || t1.serial# || ''';' 解除死锁 FROM v$session t1, v$lock t2
 where t2.BLOCK = 1 and t2.SID = t1.SID;

--查看导致死锁的 SQL(根据SID查询)
SELECT s.sid, q.sql_text
  FROM v$sqltext q, v$session s
 WHERE q.address = s.sql_address
   AND s.sid = &sid
 ORDER BY piece;

--查看谁锁了谁——SQL1
SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid ||
       ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
       s2.sid || ' ) ' AS blocking_status
  FROM v$lock l1, v$session s1, v$lock l2, v$session s2
 WHERE s1.sid = l1.sid
   AND s2.sid = l2.sid
   AND l1.BLOCK = 1
   AND l2.request > 0
   AND l1.id1 = l2.id1
   AND l2.id2 = l2.id2;

--查看谁锁了谁——SQL2
SELECT LPAD(' ', DECODE(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,
       o.owner,
       o.object_name,
       o.object_type,
       s.sid,
       s.serial#
  FROM v$locked_object l, dba_objects o, v$session s
 WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
 ORDER BY o.object_id, xidusn DESC;

设置 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;