数据库
Oracle

Oracle相关知识杂记

# Oracle 常用性能排查语句

# 查看表空间大小

--oracle表空间大小
SELECT a.tablespace_name "表空间名",
       total "表空间大小",
       free "表空间剩余大小",
       (total - free) "表空间使用大小",
       round(total / (1024 * 1024 * 1024),2) "表空间大小(G)",
       round(free / (1024 * 1024 * 1024),2) "表空间剩余大小(G)",
       round((total - free) / (1024 * 1024 * 1024),2) "表空间使用大小(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 *
  FROM (SELECT T.TABLESPACE_NAME,
               T.OWNER,
               T.SEGMENT_NAME,
               T.SEGMENT_TYPE,
               SUM(T.BYTES / 1024 / 1024) MB
          FROM DBA_SEGMENTS T
         WHERE T.SEGMENT_TYPE = 'TABLE' AND T.SEGMENT_NAME = 'TABLE_NAME'
         GROUP BY T.TABLESPACE_NAME, T.OWNER, T.SEGMENT_NAME, T.SEGMENT_TYPE) T
 ORDER BY T.MB DESC;

# 查看锁表及解决

1.下面的语句用来查询哪些对象被锁：
select object_name,machine,s.sid,s.serial#,l.ORACLE_USERNAME
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;
2.下面的语句用来杀死一个进程：
alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#);
3.批量删除
select 'alter system kill session ''' || 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;

select object_name,a.SQL_TEXT,machine,s.sid,s.serial#,s.status,lockwait,machine,program
from v$locked_object l,dba_objects o ,v$session s,v$sql a
where l.object_id　=　o.object_id and l.session_id=s.sid and a.HASH_VALUE = s.SQL_HASH_VALUE;
Username：死锁语句所用的数据库用户；
Lockwait：死锁的状态，如果有内容表示被死锁。
Status： 状态，active表示被死锁
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序。



   select b.file_id　　文件ID,
　　      b.tablespace_name　　表空间,
　　      b.file_name　　　　　物理文件名,
　　      b.bytes　　　　　　　总字节数,
　　      (b.bytes-sum(nvl(a.bytes,0)))　　　已使用,
　　      sum(nvl(a.bytes,0))　　　　　　　　剩余,
　　      sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比
　　 from dba_free_space a,dba_data_files b
　　where a.file_id=b.file_id
 group by b.tablespace_name,b.file_name,b.file_id,b.bytes
 order by b.tablespace_name



--最新
select object_name,a.SQL_TEXT,machine,s.sid,s.serial#,s.status,lockwait,machine,program,l.ORACLE_USERNAME
  from v$locked_object l,dba_objects o ,v$session s,v$sql a
 where l.object_id　=　o.object_id and l.session_id=s.sid and a.HASH_VALUE = s.SQL_HASH_VALUE;

alter system kill session '1902,24317'; 


grant alter system to ysgl_guanli

# 查询行锁并解决

--------------------------------- 方案1
--行锁查看
select s.SID,s.SERIAL#,s.MACHINE,s.TYPE,l.TYPE,l.CTIME,l.BLOCK,l.REQUEST,l.LMODE,
       decode(l.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', substr(to_char(l.lmode), 1, 13)) as "Locked Mode",
       DECODE(L.TYPE,'MR','File_ID:' || L.ID1,'TM',t.NAME,'TX','USN:' || to_char(TRUNC(L.ID1 / 65536)) || 'RWO:' ||nvl(r.NAME, 'None'), L.ID1) as LOCK_ID1,
       'alter system kill session ''' || s.SID || ',' || s.SERIAL# || '''immediate;' as "Kill"
  from gv$process p
 inner join gv$session s
    on s.PADDR = p.ADDR
 inner join v$lock l
    on l.SID = s.SID
  left join sys.obj$ t
    on l.ID1 = t.obj#
  left join sys.obj$ r
    on s.ROW_WAIT_OBJ# = r.obj#
 where 1 = 1
   and l.TYPE != 'MR' 
 and l.TYPE  = 'TM'
   and l.lmode = 3
 order by s.SID;

-- 查看原因?
WITH sessions AS
(SELECT  /*+materialize*/
         sid
        ,sql_id
        ,event
        ,blocking_session
        ,row_wait_obj#
FROM gv$session)
SELECT LPAD(' ', LEVEL ) || sid sid
      ,sql_id
      ,event
      ,owner||decode(owner,null,null,'.')||object_name object_name
      ,substr(sql_text,1,40) sql_text
FROM sessions s
LEFT OUTER JOIN dba_objects ON (object_id = row_wait_obj#)
LEFT OUTER JOIN v$sql USING (sql_id)
WHERE sid IN (SELECT blocking_session FROM sessions)
OR blocking_session IS NOT NULL
CONNECT BY PRIOR sid = blocking_session
   START WITH blocking_session IS NULL;

--------------------------------- 方案2
   select 'alter system kill session ''' || ss.sid || '' || ',' || ss.serial# || ',@' ||
       ss.inst_id || ''' immediate;' db_kill_session
  from gv$session s, 
  gv$session ss
 where s.final_blocking_session is not null
   and s.final_blocking_instance = ss.inst_id
   and s.final_blocking_session = ss.sid
   and s.sid <> ss.sid;

# 刷新表统计信息（做表分析）

统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。例如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划。

统计信息是存放在数据字典表中的，如tab$，一般可通过察看某些视图来获取统计信息状况，如DBA_TABLES，DBA_INDEXES，DBA_TAB_COL_STATISTICS， DBA_TAB_HISTOGRAMS等。在这些视图中包含表示统计信息的一些字段，这些字段只有搜集过统计信息之后才有值，否则是空的。例如：last_analyzed 字段表示上次统计信息搜集的时间，可以根据这个字段，快速的了解最近一次统计信息搜集的时间。

博客： 有关Oracle统计信息的知识点 (opens new window)
博客： Oracle 11g新特性之统计信息收集 (opens new window)

--当表没有做分析的时候，Oracle 会使用动态采样来收集统计信息。获取准确的段对象（表，表分区，索引等）的分析数据，是CBO存在的基石，
--CBO的机制就是收集尽可能多的对象信息和系统信息，通过对这些信息进行计算，分析，评估，最终得出一个成本最低的执行计划。
--所以对于CBO，数据段的分析就非常重要。

解锁单个用户schema
exec dbms_stats.unlock_schema_stats('用户名');

单个表统计数据的统计数据更新
EXEC dbms_stats.gather_table_stats('【username】','【tablename】', estimate_percent => dbms_stats.auto_sample_size, cascade=>true);
-- EXEC dbms_stats.gather_table_stats('【username】','【tablename】',cascade=>true);
-- analyze table tablename compute statistics 等同于 analyze table tablename compute statistics for table for all indexes for all columns

-- 更新整个用户所有表的统计数据更新
-- EXEC dbms_stats.gather_schema_stats('【username】',estimate_percent=>100,cascade=> TRUE);


参数说明: (https://www.cnblogs.com/tingxin/p/12663682.html)
ownname:要分析表的拥有者
tabname:要分析的表名.
partname:分区的名字,只对分区表或分区索引有用.
estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.
block_sapmple:是否用块采样代替行采样.
method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下(默认值为FOR ALL COLUMNS SIZE AUTO):
for all columns:统计所有列的histograms.
for all indexed columns:统计所有indexed列的histograms.
for all hidden columns:统计你看不到列的histograms
for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in thedata
degree:决定并行度.默认值为null.
granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.
cascade:是收集索引的信息.默认为FALSE.
stattab:指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.
no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.
force:即使表锁住了也收集统计信息.


这是对命令与工具包的一些总结
1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。
a) 可以并行进行，对多个用户，多个Table
b) 可以得到整个分区表的数据和单个分区的数据。
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
d) 可以倒出统计信息
e) 可以用户自动收集统计信息
2、DBMS_STATS的缺点
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。
c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True
3、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。

# 查询正在执行的SQL

SELECT b.inst_id,b.sid oracleID,
       b.username 登录Oracle用户名,
       b.serial#,
       spid 操作系统ID,
       paddr,
       sql_text 正在执行的SQL,
       sql_fulltext,
       b.machine 计算机名,
       b.EVENT,
       'alter system kill session '''||b.sid||','||b.serial#||''';'
FROM gv$process a, gv$session b, gv$sql c
WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value
   and a.inst_id=1
   and b.inst_id=1
   and c.inst_id=1
   and b.status='ACTIVE'
   ;

# 查询SQL平均耗时

--根据平均耗时大小排序
SELECT a.SQL_TEXT,
       a.SQL_ID,
       a.EXECUTIONS "总执行次数",
       nvl(a.ELAPSED_TIME, 0) / 1000 / 1000 "总耗时(秒)",
       (nvl(a.ELAPSED_TIME, 0) /
       nvl(decode(a.EXECUTIONS, 0, 1, a.EXECUTIONS), 1)) / 1000 / 1000 "平均耗时（秒）",
       a.PARSE_CALLS "硬解析次数",
       a.DISK_READS "物理读次数",
       a.BUFFER_GETS "读缓存区次数",
       a.FIRST_LOAD_TIME "sql开始执行时间"
  FROM v$SQL a
 WHERE a.first_load_time like '2022-07-13%'
 order by (nvl(a.ELAPSED_TIME, 0) /
          nvl(decode(a.EXECUTIONS, 0, 1, a.EXECUTIONS), 1)) / 1000 / 1000 desc;

# 慢查询耗时

-- 慢查询耗时
select *
 from (select sa.SQL_TEXT "执行 SQL",
        sa.EXECUTIONS "执行次数",
        round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
        sa.COMMAND_TYPE,
        sa.PARSING_USER_ID "用户ID",
        u.username "用户名",
        sa.HASH_VALUE
     from v$sqlarea sa
     left join all_users u
      on sa.PARSING_USER_ID = u.user_id
     where sa.EXECUTIONS > 0
     order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
 where rownum <= 50;

# 找到正在运行的存储过程并杀死

-- ddl锁 dba_ddl_locks
1.查询正在运行的存储过程（包、函数等）
select name from v$db_object_cache where type IN ('PROCEDURE', 'PACKAGE BODY', 'PACKAGE') and locks > 0 and pins > 0;
-- 获取ddl锁的信息
select * from dba_ddl_locks where owner = 'XXX' AND name='XXX';

2.找到对应存储过程的sid
select/*+rule */ sid from v$access o where OWNER='过程的所属用户' AND o.OBJECT = '存储过程'   --（参数是存储过程名称）

3.根据sid找到对应的serial#
select sid,serial# from v$session a WHERE A.SID='sid'

4.终止运行存储过程
alter system kill session 'sid,serial#'  (例如：alter system kill session 'sid,serial#')

# 其他

--查询当前用户使用了多少还原表空间
select d.username,c.name,b.writes
from v$transaction a,v$rollstat b,v$rollname c,v$session d
where d.taddr=a.addr
  and a.xidusn=b.usn
  and b.usn=c.usn
order by d.username;

-- 查看临时表空间的使用情况
select ts.name,ts.phyrds "reads",ts.phywrts "writes",ts.phyblkrd,ts.phyblkwrt,ts.readtim "rtime",ts.writetim "wtime"
  from v$tablespace ts,v$tempfile tf ,v$tempstat ts
 where ts.ts#=tf.ts#
   and tf.file#=ts.file#;

-- 查看数据文件的i/o分布情况
select name,phyrds,phywrts,readtim,writetim
  from v$filestat a,v$datafile b
 where a.file#=b.file#
 order by readtim desc;

-- 查看连接oracle的所有机器的连接数和状态
select machine,status,count(*) from v$session group by machine,status order by status;

/*
SGA(System Global Area)：由所有服务进程和后台进程共享；
PGA(Program Global Area)：由每个服务进程、后台进程专有；每个进程都有一个PGA。
*/
-- oracle的PGA、SGA和process count
select 'SGA' AS NAME,ROUND(sum(value)/1024/1024,2)||'M' AS "SIZE(M)" from v$sga
UNION
select 'PGA' AS NAME,ROUND(value/1024/1024,2)||'M' AS "SIZE(M)" from v$pgastat where name='total PGA allocated'
UNION
select 'TOTAL' AS NAME,((SELECT ROUND(sum(value)/1024/1024,2) from v$sga)+(select ROUND(value/1024/1024,2) from v$pgastat where name='total PGA allocated'))||'M' AS "SIZE(M)" FROM DUAL
UNION
SELECT NAME,TO_CHAR(VALUE) FROM V$PGASTAT WHERE NAME='process count';

# SCN

SCN（System Change Number），也就是通常所说的系统改变号，是数据库中非常重要的一个数据结构。
SCN用以标识数据库在某个确切时刻提交的版本。在事务提交时，它被赋予一个唯一的标识事务的SCN。SCN同时被作为Oracle数据库的内部时钟机制，可被看做逻辑时钟，每个数据库都有一个全局的SCN生成器。
作为数据库内部的逻辑时钟，数据库事务依SCN而排序，Oracle也依据SCN来实现一致性读（Read Consistency）等重要数据库功能。另外对于分布式事务（Distributed Transactions），SCN也极为重要，这里不做更多介绍。
SCN在数据库中是唯一的，并随时间而增加，但是可能并不连贯。除非重建数据库，SCN的值永远不会被重置为0.
一直以来，对于SCN有很多争议，很多人认为SCN是指System Commit Number，而通常SCN在提交时才变化，所以很多时候，这两个名词经常在文档中反复出现。即使在Oracle的官方文档中，SCN也常以System Change/Commit Number两种形式出现。
到底是哪个词其实不是很重要，重要的是需要知道SCN是Oracle内部的时钟机制，Oracle通过SCN来维护数据库的一致性，并通过SCN实施Oracle至关重要的恢复机制。
SCN在数据库中是无处不在，常见的事务表、控制文件、数据文件头、日志文件、数据块头等都记录有SCN值。
冠以不同前缀，SCN也有了不同的名称，如检查点SCN（Checkpint SCN）、Resetlogs SCN等。

-- 当前scn号和时间的对应关系：
select dbms_flashback.get_system_change_number,SCN_TO_TIMESTAMP(dbms_flashback.get_system_change_number) from dual;

-- 查询当前scn：
select CURRENT_SCN from v$database;
select dbms_flashback.get_system_change_number from dual;

-- SCN与时间的相互转换
将SCN转换成时间戳: SCN_TO_TIMESTAMP(scn_number)
将时间戳转换成SCN: TIMESTAMP_TO_SCN(timestamp)

-- 数据行伪列保存数据最后更新时间  select ora_rowscn from table_name;
-- 系统表保存scn与时间戳对应关系（保留5天）   SELECT * FROM sys.smon_scn_time

了解更多请核查看博客：https://blog.csdn.net/fuwencaho/article/details/21256973

# Oracle 表闪回

查看是否开启闪回功能：SELECT flashback_on FROM V$DATABASE;

# 查询历史版本数据（闪回查询）

-- 查询出类似拉链表的数据（包含历史版本）
SELECT 
       字段名,
       TO_CHAR(VERSIONS_STARTTIME, 'yyyy-MM-dd hh24:mi:ss') AS VERSIONS_STARTTIME,
       TO_CHAR(VERSIONS_ENDTIME, 'yyyy-MM-dd hh24:mi:ss') AS VERSIONS_ENDTIME,
       VERSIONS_OPERATION
  FROM 表名 VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
 WHERE VERSIONS_STARTTIME IS NOT NULL
 ORDER BY VERSIONS_STARTTIME DESC;

# 恢复已删除的表

-- 恢复已删除的表
flashback table table_name to before drop;

-- 若删除后已经创建了同名的表（ORA-38312）
-- 如果表名重复，则闪回时遵循后入先出的原则。
flashback table test to before drop rename to test1;

扩展：

-- 闪回时可指明被恢复的回收站对象
flashback table "BIN$AyId7ZbBjWngUKjADQIIuA==$0" to before drop;

-- 闪回删表的工作原理是：当“drop table”命令执行时，表及其索引并没有被真正删除，其所占空间只是分配给了另一个数据库对象：回收站对象，本质上相当于重命名。注意：表空间在自动增长的压力下会按照先入先出的规则将回收站对象的空间分配给需要空间的段，在将回收站对象耗尽之前数据文件是不会自动增长的。
-- Oracle启用回收站功能后，使用drop table ...语句删除一张表时，并不会将表给删除，而是以一定的格式重新对表重命名，然后将该表放到数据字典表中，在遇到表空间不足的情况下自动清理这些表，释放被占用的空间，让其它对象能够使用这些空间。

-- 可禁用回收站功能
alter system set recyclebin='OFF' scope=spfile;

-- 清理指定用户的表空间中的回收站对象
PURGE TABLESPACE USERS user user_name;
-- 删除当前用户回收站的所有对象
purge recyclebin;
    
-- dbms_flashback包
-- 利用dbms_flashback包的enable_at_time或enable_at_scn存储过程锁定一个会话级别的闪回时间目标，即进入闪回模式，随后的查询命令可以省略“as of”，直到调用dbms_flashback_disable存储过程将其关闭为止。

# 恢复表到某个时间点

-- 确定要恢复的表的时间点
SELECT * FROM table_name AS OF TIMESTAMP(to_timestamp('2024-08-29 19:00:00', 'yyyy-MM-dd hh24:mi:ss'));

-- 将表恢复到指定时间点
FLASHBACK TABLE table_name TO TIMESTAMP(to_timestamp('2024-08-29 19:00:00', 'yyyy-MM-dd hh24:mi:ss'));
-- 1. "FLASHBACK TABLE"命令的执行者必须有"FLASHBACK ANY TABLE"系统权限或者在被闪回的表上具有"FLASHBACK"对象权限。
-- 2. 需启用行移动  alter table DEBT_T_FILE_INFO_FABZ_BAK enable row movement;
-- 3. "FLASHBACK TABLE"属于DDL命令，隐式提交。
-- 4. SYS用户的任何表无法使用此功能。

# LogMiner

众所周知，所有对用户数据和数据字典的改变都记录在Oracle的Redo Log中，因此，Redo Log包含了所有进行恢复操作所需要的信息。但是，原始的Redo Log文件无法看懂，所以，Oracle从8i以后提供了一个非常有用的分析工具，称为LogMiner。使用该工具可以轻松获得Redo Log文件（包含归档日志文件）中的具体内容。特别是该工具可以分析出所有对于数据库的DML操作（INSERT、UPDATE、DELETE等）语句。Oracle 9i后可以分析DDL语句，另外还可分析得到一些必要的回滚SQL语句。LogMiner一个最重要的用途就是不用全部恢复数据库就可以恢复数据库的某个变化。该工具特别适用于调试、审计或者回退某个特定的事务。 LogMiner工具既可以用来分析在线日志，也可以用来分析离线日志文件，既可以分析本身自己数据库的重作日志文件，也可以用来分析其它数据库的重作日志文件。当分析其它数据库的重作日志文件时，需要注意的是，LogMiner必须使用被分析数据库实例产生的字典文件，而不是安装LogMiner的数据库产生的字典文件，另外，必须保证安装LogMiner数据库的字符集和被分析数据库的字符集相同。源数据库（Source Database）平台必须和分析数据库（Mining Database）平台一样。 Oracle通过LogMiner工具对Redo Log进行挖掘，显示出一系列可读的信息，该过程称为日志挖掘。LogMiner通过V$LOGMNR_CONTENTS视图显示Redo Log中的信息。

总的说来，LogMiner工具的主要用途有：

跟踪数据库的变化：可以离线地跟踪数据库的变化，而不会影响在线系统的性能
回退数据库的变化：回退特定的变化数据，减少Point-In-Time Recovery的执行
优化和扩容计划：可通过分析日志文件中的数据以分析数据的增长模式
确定数据库的逻辑损坏时间：准确定位操作执行的时间和SCN
确定事务级要执行的精细逻辑恢复操作，可以取得相应的UNDO操作
执行后续审计

了解更多请查看博客： https://blog.csdn.net/yes_is_ok/article/details/79296614

# 动态性能视图(x$,v$,gv$,v_$,gv_$)

官方文档12.2 (opens new window)

v$database 数据库信息
v$datafile 数据文件信息
v$controlfile 控制文件信息
v$logfile 重做日志信息
v$instance 数据库实例信息
v$log 日志组信息
v$loghist 日志历史信息
v$sga 数据库SGA信息
v$parameter 初始化参数信息
v$process 数据库服务器进程信息
v$bgprocess 数据库后台进程信息
v$controlfile_record_section 控制文件记载的各部分信息
v$thread 线程信息
v$datafile_header 数据文件头所记载的信息
v$archived_log归档日志信息
v$archive_dest 归档日志的设置信息
v$logmnr_contents 归档日志分析的DML DDL结果信息
v$logmnr_dictionary 日志分析的字典文件信息
v$logmnr_logs 日志分析的日志列表信息
v$tablespace 表空间信息
v$tempfile 临时文件信息
v$filestat 数据文件的I/O统计信息
v$undostat Undo数据信息
v$rollname 在线回滚段信息
v$session 会话信息
v$transaction 事务信息
v$rollstat 回滚段统计信息
v$pwfile_users 特权用户信息
v$sqlarea 当前查询过的sql语句访问过的资源及相关的信息
v$sql 与v$sqlarea基本相同的相关信息
v$sysstat 数据库系统状态信息

# 常用系统表

原文链接 (opens new window)

dba_开头


dba_users 数据库用户信息
dba_segments 表段信息
dba_extents 数据区信息
dba_objects 数据库对象信息
dba_tablespaces 数据库表空间信息
dba_data_files 数据文件设置信息
dba_temp_files 临时数据文件信息
dba_rollback_segs 回滚段信息
dba_ts_quotas 用户表空间配额信息
dba_free_space 数据库空闲空间信息
dba_profiles 数据库用户资源限制信息
dba_sys_privs 用户的系统权限信息
dba_tab_privs 用户具有的对象权限信息
dba_col_privs 用户具有的列对象权限信息
dba_role_privs 用户具有的角色信息
dba_audit_trail 审计跟踪记录信息
dba_stmt_audit_opts 审计设置信息
dba_audit_object 对象审计结果信息
dba_audit_session 会话审计结果信息
dba_indexes 用户模式的索引信息

user_开头

user_objects 用户对象信息
user_source 数据库用户的所有资源对象信息
user_segments 用户的表段信息
user_tables 用户的表对象信息
user_tab_columns 用户的表列信息
user_col_comments 用户的表列注释信息
user_constraints 用户的对象约束信息
user_sys_privs 当前用户的系统权限信息
user_tab_privs 当前用户的对象权限信息
user_col_privs 当前用户的表列权限信息
user_role_privs 当前用户的角色权限信息
user_indexes 用户的索引信息
user_ind_columns 用户的索引对应的表列信息
user_cons_columns 用户的约束对应的表列信息
user_clusters 用户的所有簇信息
user_clu_columns 用户的簇所包含的内容信息
user_cluster_hash_expressions 散列簇的信息

all_开头

all_users 数据库所有用户的信息
all_objects 数据库所有的对象的信息
all_def_audit_opts 所有默认的审计设置信息
all_tables 所有的表对象信息
all_indexes 所有的数据库对象索引的信息

session_开头
session_roles 会话的角色信息
session_privs 会话的权限信息

index_开头
index_stats 索引的设置和存储信息

**伪表**
dual 系统伪列表信息

# 实例

点击查看

实例

-- 查看视图中用到的表和视图
   SELECT NAME, REFERENCED_TYPE, REPLACE(TO_CHAR(WM_CONCAT(DISTINCT REFERENCED_NAME)), ',', chr(13))
     FROM SYS.ALL_DEPENDENCIES
    WHERE TYPE = 'VIEW'
      AND NAME = UPPER('视图名')
      AND REFERENCED_TYPE IN ('TABLE', 'VIEW')
      AND OWNER = '用户名'
 GROUP BY NAME, REFERENCED_TYPE
 ORDER BY REFERENCED_TYPE
;

-- 用到 某个表 的视图
   SELECT NAME, REFERENCED_TYPE, REPLACE(TO_CHAR(WM_CONCAT(DISTINCT REFERENCED_NAME)), ',', chr(13))
     FROM SYS.ALL_DEPENDENCIES
    WHERE TYPE = 'VIEW'
      AND REFERENCED_NAME = '表名'
      AND OWNER = '用户名'
 GROUP BY NAME, REFERENCED_TYPE
 ORDER BY REFERENCED_TYPE
;

-- 查看视图创建语句
SELECT * FROM all_views where VIEW_NAME = UPPER('视图名') AND OWNER = '用户名';

-- 查询建表语句
select dbms_metadata.get_ddl('TABLE',upper('tablename')) from dual;

# 与MySQL区别

1、oracle是大型数据库，mysql是小型数据库；
2、mysql是主键是支持自动增长类型的（在创建表的时候指定表的主键为auto_increment）,在插入记录时，不需要再指定该记录的主键值，mysql将自动增长；
3、oracle没有自动增长类型主键一般使用序列，在插入记录时，将序列号的下一个值付给该字段即可；
4、mysql可以用双引号包起字符串，而oracle只能用单引号包起。
5、处理分页逻辑不一样，mysql使用limit处理分页；而oracle使用rownum字段标明位置，并且只能用rowNum&lt;100,不能用rowNum>80;
6、mysql定义的空字段里面可以有空的内容，但是oracle种非空字段不能有空的内；
7、两者都能使用like “%字符串%”,但是oracle中使用模糊查询后不能使用索引，速度不快；
8、两者给字段添加注释的方式不一样，

————————————————
原文链接：https://blog.csdn.net/z19950712/article/details/115478505

# Shell跑oracle脚本

#! /bin/sh
logpath="log"
filepath="/home/ap/user"
#输入Oracle数据库的用户名密码等信息
DBINFO="username/password@ip:port/orcl"
if  ! -d ${logpath} ]
then
 mkdir log
fi
echo "`date +'%Y%m%d %H:%M:%S'`] Info: Begin to execute init sql!"
logfile="${logpath}/init_table_data_info.log"
#“${logfile} 2>&1”这里的意思就是把标准输出信息和错误输出信息都记录在init_table_data_info.log文件中
sqlplus -s ${DBINFO} << ! > ${logfile} 2>&1
whenever oserror exit 1;
whenever sqlerror exit 1;
@${filepath}/initTable.sql
@${filepath}/initTable2.sql
quit
!
if  $? -ne 0 ]
then
    echo "`date +'%Y%m%d %H:%M:%S'`] Error: Initialize initTable failed!"  >> ${logpath}
    exit 1
fi
echo "`date +'%Y%m%d %H:%M:%S'`] Initialize initTable  successed."  >> ${logpath}

# Oracle关联方式（执行计划）

为啥有说只有三种的（待完善！！！）

不同数据库支持情况不同：PGSQL支持 nested-loop join 、hash join 、merge join；MySQL只支持 nested-loop join

Oracle的SQL优化器（Optimizer）在执行多表连接查询时，通常采用的连接算法有以下几种方式：
1、嵌套循环连接（NESTED LOOPS JOIN）
2、哈希连接（HASH JOIN）
3、排序合并连接（SORT MERGE JOIN）
4、笛卡尔连接（CARTESIAN JOIN）
5、群集连接（CLUSTER JOIN）
6、索引连接（INDEX JOIN）

多表之间的连接有三种方式：Nested Loops，Hash Join 和 Sort Merge Join. 下面来介绍三种不同连接的不同：

NESTED LOOP

对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO默认的驱动表，使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。

Nested loop一般用在连接的表中有索引，并且索引选择性较好的时候.

步骤：确定一个驱动表(outer table)，另一个表为inner table，驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。适用于驱动表的记录集比较小（<10000）而且inner表需要有有效的访问方法（Index）。需要注意的是：JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。

HASH JOIN

散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使用散列连接HASH_AREA_SIZE 初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET 即可。

Hash join在两个表的数据量差别很大的时候.

步骤：将两个表中较小的一个在内存中构造一个HASH表（对JOIN KEY），扫描另一个表，同样对JOIN KEY进行HASH后探测是否可以JOIN。适用于记录集比较大的情况。需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个partition，写入磁盘的temporary segment，则会多一个写的代价，会降低效率。

SORT MERGE JOIN

通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接. Sort Merge join 用在没有索引，并且数据已经排序的情况. cost = (outer access cost * # of hash partitions) + inner access cost

步骤：将两个表排序，然后将两个表合并。通常情况下，只有在以下情况发生时，才会使用此种JOIN方式：

1.RBO模式
2.不等价关联(>,<,>=,<=,<>)
3.HASH_JOIN_ENABLED=false
4.数据源已排序

三种连接工作方式比较

Hash join的工作方式是将一个表（通常是小一点的那个表）做hash运算，将列数据存储到hash列表中，从另一个表中抽取记录，做hash运算，到hash 列表中找到相应的值，做匹配。

Nested loops 工作方式是从一张表中读取数据，访问另一张表（通常是索引）来做匹配，nested loops适用的场合是当一个关联表比较小的时候，效率会更高。

Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配，因为merge join需要做更多的排序，所以消耗的资源更多。通常来讲，能够使用merge join的地方，hash join都可以发挥更好的性能。

强制使用关联方式

select /*+use_hash(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;
select /*+use_nl(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;
select /*+use_merge(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;

原文链接 (opens new window)

# 点状知识

# 匹配中文

使用正则匹配不太好使

替换方案：chr(128) - chr(255) 可以匹配双字节字符（同样不够准确）
select regexp_replace('', '['||chr(128)|| '-' || chr(255) ||']', '-') from dual;

# 创建DBLink

注：由于 dblink 跨库查询是把一个数据库数据通过网络发送到另一数据库关联查询，可以通过指定 hint /*+ driving_site(t)*/ 指定驱动表（t 一般是大表），从而减少网络传输。当然，最终效率还是要综合磁盘IO、网络IO和计算能力等综合考虑

-- Create database link 
create public database link <dblink名>
  connect to <用户名>
  IDENTIFIED BY <"密码">
  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL=TCP)(HOST=<ip>)(PORT=<端口>)))(CONNECT_DATA=(SERVICE_NAME=orcl)))';

# SQL并行

官方文档 (opens new window)

原文地址 (opens new window)

1.查询开启并行（Parallel query）

select /*+parallel(a, 16)*/ count(1) from dual a;     -- 第一种方式（Hist方式）
alter table tab1 parallel n;                          -- 第二种方式
alter session force parallel query parallel n;        -- 第三种方式

2.修改数据开启并行（Parallel DML (INSERT, UPDATE, DELETE, and MERGE) ）

-- 第一种方式（Hist方式）
UPDATE /*+ PARALLEL(tab1,4) */ tbl_2 SET c1=c1+1;
INSERT /*+ PARALLEL(tbl_ins,2) */ INTO tbl_ins
SELECT /*+ PARALLEL(tbl_sel,4) */ * FROM tbl_sel;
DELETE /*+ PARALLEL (t1, 2) */ FROM t1

-- 第二种方式
alter session force parallel DML parallel n;

-- 第三种方式
alter table tab1 parallel n;

3.Parallel DDL

-- 第一种方式（Hist方式）
ALTER SESSION FORCE PARALLEL DDL parallel n;

-- 第二种方式
CREATE INDEX ….parallel 10;
ALTER INDEX ... REBUILD parallel 10;
ALTER INDEX ... MOVE PARTITION parallel 10;
ALTER INDEX ...SPLIT PARTITION parallel 10;

附：存储过程中开启并行

declare
...
begin
 execute immediate 'alter session force parallel dml parallel  8';
 --更新操作
 ...
 commit; --必须先commit,否则会报 ora-12841
 execute immediate 'alter session disable parallel dml ';

exception
 when others then
   rollback;
   execute immediate 'alter session disable parallel dml '; 
end;

注：使用临时表with as时不能使用并行（比如：在存储过程中开启session并行），否则会导致结果不一致

# 中文排序方式

Oracle9i之前，中文是按照二进制编码进行排序的。
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值

SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序
SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序
SCHINESE_PINYIN_M 按照拼音排序

修改ORACLE字段的默认排序方式：
按拼音(默认)：
alter session set nls_sort = SCHINESE_PINYIN_M;
按笔画：
alter session set nls_sort = SCHINESE_STROKE_M;
按偏旁：
alter session set nls_sort = NLS_SORT=SCHINESE_RADICAL_M;

NLSSORT()，用来进行语言排序

示例:
SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,'NLS_SORT =SCHINESE_PINYIN_M')

# 数据中包含特殊符号需要转义

执行语句前先执行 set define off; （去掉oracle自定义的字符含义，还原它本来的意思）
使用ASCII编码对 & 进行转义 chr(38) select 'xxx?xxx=11' || chr(38) || 'xxx=33' from dual
不转义，直接字符串的形式写进去 select 'xxx?xxx=11' || '&' || 'xxx=33' from dual

# 数据泵导入导出（待完善）

导入：首先我们需要知道导出用户和表空间的名字

注意：1. 如果表空间不是只有一个，需要使用remap_tablespace指定到我们创建的表空间 2. 单个表空间文件最大32G

所以拿到导出log文件是非常有必要的

-- 创建表空间  UNLIMITED指定不限制最大表空间
create TABLESPACE BANANA datafile 'E:\OracleData\tablespace\DATAS.dbf' size 50m autoextend on next 50m MAXSIZE UNLIMITED extent management local; 
-- 删除表空间
drop TABLESPACE BANANA including contents and datafiles cascade constraints;
-- 查询表空间
select * from dba_tablespaces;


-- 管理员登录，创建新用户，分配已创建好的表空间
create user banana identified BY banana default TABLESPACE BANANA;
-- 删除用户带级联
drop user banana cascade;


-- 授权
Grant dba to banana with admin option;


-- 查询dup存放目录目录    注意：后面的E盘下面的dpdump 必须把你要导入的xxx.dmp文件放进该文件夹  DATA_PUMP_DIR
select * from dba_directories;
-- 数据泵导入数据 (CMD命令行   DATA_PUMP_DIR为上面查出的目录名  如果有其他表空间的，需要使用remap_tablespace指定到我们创建的表空间)
impdp banana remap_tablespace=MOF:BANANA,TEST:BANANA directory=DATA_PUMP_DIR dumpfile=XXX.DMP logfile=impdp.log -- FULL=YES parallel=16


-- 增加指定表空间文件（单个表空间文件最大32G，需要扩充）
ALTER TABLESPACE BANANA ADD DATAFILE 'E:\OracleData\tablespace\DATAS01.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M  ;

命令保存

-- 创建目录
create directory my_dir as '/home/oracle/tmp';
grant read,write on directory my_dir to username;

-- 环境变量(若没配置好，需先执行)
export ORACLE_HOME=/xxx/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

-- exp/imp 导入导出指定表
exp 导出用户名/'密码'@orcl file=/home/oracle/xxx.dmp tables=\(tablename1, tablename2\) log=/home/oracle/xxx.log schemas=scott
imp 导入用户名/'密码'@orcl file=/home/oracle/xxx.dmp tables=\(tablename1, tablename2\) log=/home/oracle/xxx.log statistics=none schemas=scott


-- expdp/impdp 导入导出 
expdp $USERNAME/$PASSWD DUMPFILE=xxx.dmp(据说可以为任意后缀名) DIRECTORY=DATA_PUMP_DIR(Oracle内变量) FULL=y LOGFILE=xxx.log schemas=scott
impdp $USERNAME/$PASSWD directory=DATA_PUMP_DIR dumpfile=xxx.dmp logfile=xxx.log full=yes schemas=scott
expdp $USERNAME/$PASSWD DUMPFILE=xxx.dmp DIRECTORY=DATA_PUMP_DIR LOGFILE=xxx.log tables=\(tablename1, tablename2\) schemas=scott
impdp $USERNAME/$PASSWD directory=DATA_PUMP_DIR dumpfile=xxx.dmp logfile=xxx.log tables=\(tablename1, tablename2\) schemas=scott

-- 参数
parallel=5 -- 并行导出
content=metadata_only -- 元数据(包含表定义、存储过程、函数等等)
include=\(procedure,function,view\)
exclude=index  -- 排除

------------------------------------------ 导出 ------------------------------------------------
##导出一张表，例：
expdp system/oracle directory=my_dir dumpfile=expdp.dmp logfile=expdp.log tables=scott.emp schemas=scott

##导出多张表，例：
expdp system/oracle directory=my_dir dumpfile=expdp.dmp logfile=expdp.log tables=\(scott.emp,scott.dept\) schemas=scott

##导出一个用户(导出这个用户的所有对象)，例：
expdp system/oracle directory=my_dir dumpfile=expdp.dmp logfile=expdp.log schemas=scott

##导出多个用户，例：
expdp system/oracle directory=my_dir dumpfile=expdp.dmp logfile=expdp.log schemas=\(scott,hr\)

##导出整个数据库（sys、ordsys、mdsys的用户数据不会被导出）例：
expdp system/oracle directory=my_dir dumpfile=expdp.dmp logfile=expdp.log full=yes

------------------------------------------ 导入 ------------------------------------------------
##导入dmp文件中的所有数据，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp logfile=impdp.log full=yes schemas=scott

##导入一张表，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp logfile=impdp.log tables=scott.emp schemas=scott

##导入多张表，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp logfile=impdp.log tables=\(scott.emp,scott.dept\) schemas=scott

##导入一个用户，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp logfile=impdp.log schemas=scott

##导入多个用户，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp logfile=impdp.log schemas=\(scott,hr\)

# Oracle 高版本导出，低版本导入，修改版本号

使用 AlexTools 工具
导出时指定版本，增加参数（例：version=11.2）

# 解决本地Oracle服务内存占用过高

Oracle安装时，为均衡电脑性能和数据库性能，默认内存大小为物理内存的1/8

用dba身份进入oracle
show parameter sga; --显示内存分配情况
alter system set sga_target=1024m scope=spfile;-修改target大小 //这个值必须小于等于sga_max_size ，否则库会起不来；
alter system set sga_max_size=1024m scope=spfile; --修改最大占用内存的大小
修改后重启Oracle服务
附：Oracle服务介绍原文链接 (opens new window)
- Oracle ORCL VSS Writer Service：Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service）能够让存储基础设备（比如磁盘，阵列等）创建高保真的时间点映像，即映射拷贝（shadow copy）。它可以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。（非必须启动）
- OracleDBConsoleorcl：Oracle数据库控制台服务，orcl是Oracle的实例标识，默认的实例为orcl。在运行Enterprise Manager（企业管理器OEM）的时候，需要启动这个服务。（非必须启动）
- OracleJobSchedulerORCL：Oracle作业调度（定时器）服务，ORCL是Oracle实例标识。（非必须启动）
- OracleMTSRecoveryService：服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。（非必须启动）
- OracleOraDb11g_home1ClrAgent：Oracle数据库 .NET扩展服务的一部分。（非必须启动）
- OracleOraDb11g_home1TNSListener：监听器服务，服务只有在数据库需要远程访问的时候才需要。（非必须启动但很常用）
- OracleServiceORCL：数据库服务(数据库实例)，是Oracle核心服务，该服务是数据库启动的基础，只有该服务启动，Oracle数据库才能正常启动。(必须启动)

# 数据插入慢的问题

新增：晚上跑存储过程稍快（好几个表两千多万条数据4688s），为什么？因为磁盘读写速度慢T.T

有个九百多万数据的表需要重新抽数（抽数速度极其慢，后发现是索引导致的，不清楚是索引有问题还是正常现象（索引增加查询速率但影响insert、update、delete效率），应该先删除索引再抽数）
没删除索引前的效率：【345430 条数据花费 1406s】【345430 条数据花费1301s】
删除后的效率：【345508条数据花费 64s】【345482 条数据花费 7.248s】【345508 条数据34.466s】【datax：1641410条数据465s】

编辑

上次更新: 2025/01/02, 13:03:48