问题故障：数据库Open,无法切换普通用户：
---递归SQL无法执行
SQL> conn hr/hr
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC_EVENTS
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
[oracle@hukou ~]$ tail -50f /picclife/app/oracle/diag/rdbms/hukou/hukou/trace/alert_hukou.log
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "field"
KUP-01008: the bad identifier was: dield
KUP-01007: at line 1 column 95
10046--无法生成详细文档
追踪操作：本次需要追踪的是conn hr/hr，数据内部执行了什么操作，但只要执行操作，就断开了连接，无法结束追踪，因此10046工具无法使用此场景：
SQL> alter session set events '10046 trace name context forever';
Session altered.
SQL> alter session set events '10046 trace name context forever,level 8';
如下：排除问题：因素
--查询用户密码，角色：排除操作问题
SQL> alter user yang identified by yang;
SQL> select grantee,granted_role from dba_role_privs where grantee='YANG';
GRANTEE GRANTED_ROLE
------------------------------ ------------------------------
YANG RESOURCE
YANG CONNECT
SQL> conn yang/yang;
ERROR:
###进入误区，误以为是存储空间问题：
--查询表空间使用的对象> 20m的对象信息：
select segment_name,sum(bytes)/1024/1024 from dba_segments having(sum(bytes)/1024/1024)>20 group by segment_name
SEGMENT_NA SUM(BYTES)/1024/1024
---------- --------------------
IDL_UB1$ 45.1328125
SOURCE$ 42.984375
##开启SQL追踪
SQL> alter system set sql_trace=true;
SQL> conn hr/hr
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
--查询用户的dump文件：
--查询参数路径：
SQL> show parameter user_dump
--根据时间排序，按修改时间排列；
more分页查看：Q结束分页 => 选择最靠前的 ora_xxx.trc文件查询
[oracle@hostuzi trace]$ ls -lt|more
-rw-r----- 1 oracle oinstall 304287 12-24 18:48 alert_PROD.log
-rw-r----- 1 oracle oinstall 70 12-24 18:43 PROD_lgwr_23734.trm
-rw-r----- 1 oracle oinstall 2627 12-24 18:43 PROD_ora_23773.trc
--------用户的DUMP文件---
PARSE ERROR #140124260648808:len=597 dep=1 uid=0 oct=2 lid=0 t
im=1511612960366612 err=942
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#,
userid,userhost,terminal,action#,returncode, obj$creator,obj$
name,auth$privileges,auth$grantee, new$owner,new$name,ses$acti
ons,ses$tid,logoff$pread, logoff$lwrite,logoff$dead,comment$te
xt,spare1,spare2, priv$used,clientid,sessioncpu,proxy$sid,use
r$guid, instance#,process#,xid,scn,auditid, sqlbind,sqltext,o
bj$edition,dbid) values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP
), :4,:5,:6,:7,:8, :9,:10,:11,:12, :13,:14,:15,:16
,:17, :18,:19,:20,:21,:22, :23,:24,:25,:26,:27, :2
8,:29,:30,:31,:32, :33,:34,:35,:36)
*** 2017-11-25 20:29:21.366
CLOSE #140124260648808:c=0,e=5,dep=1,type=0,tim=15116129613669
40
###AUD$的表：
审计参数开启后：AUD$基表存储：
查询dba_audit_trail 视图查询：
SQL> show parameter audit
audit_trail string DB_EXTENDED 【数据库开启审计模式，审计信息会记录在aud$基表中】
SQL> select owner,table_name,tablespace_name,max_extents from dba_tables where table_name='AUD$';
no rows selected
SQL> select owner,table_name,tablespace_name,max_extents from dba_tables where table_name='FAG_LOG$';
no rows selected
-----------------对象不存在------------
####此时问题可以判定：缘由在于：之前将AUD$,FGA_LOG$迁移的另一个表空间被删除：
问题原因找到：两种解决方案：
一、等待找到建表语句，重建表
二、关闭审计参数：先对外提供服务运行：需要重启库操作：
alter system set audit_trail=none scope=spfile;
一、
#另一个库--查询与基表AUD$管理的索引
SQL> select index_name,table_name from dba_indexes where table_name='AUD$';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_IL0000000407C00040$$ AUD$
SYS_IL0000000407C00041$$ AUD$
SQL> select index_name,table_name from dba_indexes where table_name='FGA_LOG$';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_IL0000000417C00013$$ FGA_LOG$
SYS_IL0000000417C00028$$ FGA_LOG$
#查询两个表的触发器
SQL> select TRIGGER_NAME,TABLE_NAME from user_triggers where table_name='AUD$';
no rows selected
SQL> select TRIGGER_NAME,TABLE_NAME from user_triggers where table_name='FGA_LOG$';
no rows selected
####通过GETDDL调出建表语句：
SQL> set line 200 pages 50000 wrap on long 999999 serveroutput on
SQL> select dbms_metadata.get_ddl('TABLE','AUD$','SYS') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','AUD$','SYS')
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."AUD$"
( "SESSIONID" NUMBER NOT NULL ENABLE,
"ENTRYID" NUMBER NOT NULL ENABLE,
"STATEMENT" NUMBER NOT NULL ENABLE,
"TIMESTAMP#" DATE,
"USERID" VARCHAR2(30),
"USERHOST" VARCHAR2(128),
"TERMINAL" VARCHAR2(255),
"ACTION#" NUMBER NOT NULL ENABLE,
"RETURNCODE" NUMBER NOT NULL ENABLE,
"OBJ$CREATOR" VARCHAR2(30),
"OBJ$NAME" VARCHAR2(128),
"AUTH$PRIVILEGES" VARCHAR2(16),
"AUTH$GRANTEE" VARCHAR2(30),
"NEW$OWNER" VARCHAR2(30),
"NEW$NAME" VARCHAR2(128),
"SES$ACTIONS" VARCHAR2(19),
"SES$TID" NUMBER,
"LOGOFF$LREAD" NUMBER,
"LOGOFF$PREAD" NUMBER,
"LOGOFF$LWRITE" NUMBER,
"LOGOFF$DEAD" NUMBER,
"LOGOFF$TIME" DATE,
"COMMENT$TEXT" VARCHAR2(4000),
"CLIENTID" VARCHAR2(64),
"SPARE1" VARCHAR2(255),
"SPARE2" NUMBER,
"OBJ$LABEL" RAW(255),
"SES$LABEL" RAW(255),
"PRIV$USED" NUMBER,
"SESSIONCPU" NUMBER,
"NTIMESTAMP#" TIMESTAMP (6),
"PROXY$SID" NUMBER,
"USER$GUID" VARCHAR2(32),
"INSTANCE#" NUMBER,
"PROCESS#" VARCHAR2(16),
"XID" RAW(8),
"AUDITID" VARCHAR2(64),
"SCN" NUMBER,
"DBID" NUMBER,
"SQLBIND" CLOB,
"SQLTEXT" CLOB,
"OBJ$EDITION" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 16384 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
LOB ("SQLBIND") STORE AS BASICFILE (
TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB ("SQLTEXT") STORE AS BASICFILE (
TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT));
SQL> select dbms_metadata.get_ddl('TABLE','FGA_LOG$','SYS') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','FGA_LOG$','SYS')
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."FGA_LOG$"
( "SESSIONID" NUMBER NOT NULL ENABLE,
"TIMESTAMP#" DATE,
"DBUID" VARCHAR2(30),
"OSUID" VARCHAR2(255),
"OSHST" VARCHAR2(128),
"CLIENTID" VARCHAR2(64),
"EXTID" VARCHAR2(4000),
"OBJ$SCHEMA" VARCHAR2(30),
"OBJ$NAME" VARCHAR2(128),
"POLICYNAME" VARCHAR2(30),
"SCN" NUMBER,
"SQLTEXT" VARCHAR2(4000),
"LSQLTEXT" CLOB,
"SQLBIND" VARCHAR2(4000),
"COMMENT$TEXT" VARCHAR2(4000),
"PLHOL" LONG,
"STMT_TYPE" NUMBER,
"NTIMESTAMP#" TIMESTAMP (6),
"PROXY$SID" NUMBER,
"USER$GUID" VARCHAR2(32),
"INSTANCE#" NUMBER,
"PROCESS#" VARCHAR2(16),
"XID" RAW(8),
"AUDITID" VARCHAR2(64),
"STATEMENT" NUMBER,
"ENTRYID" NUMBER,
"DBID" NUMBER,
"LSQLBIND" CLOB,
"OBJ$EDITION" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
LOB ("LSQLTEXT") STORE AS BASICFILE (
TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB ("LSQLBIND") STORE AS BASICFILE (
TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000000407C00040$$','SYS') FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000000407C00040$$','SYS')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "SYS"."SYS_IL0000000407C00040$$" ON "SYS"."AUD$" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
PARALLEL (DEGREE 0 INSTANCES 0)
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000000407C00041$$','SYS') FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000000407C00041$$','SYS')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "SYS"."SYS_IL0000000407C00041$$" ON "SYS"."AUD$" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
PARALLEL (DEGREE 0 INSTANCES 0)
!解决问题：创建基表则解决问题：
疑问：切换用户生成记录？ 什么记录？什么方式会记录？如何关闭：
会话一：
select count(*) from aud$
COUNT(*)
----------
195
会话二：
SQL> conn scott/tiger
会话一：再次查询
SQL> select count(*) from aud$
COUNT(*)
---------- --通过connect / as sysdba / conn hr/hr切换会产生记录：
196 --切换用户，会生成审计信息及在基表中
--通过sqlplus / as sysdba操作系统验证：基表不会存储审计文件，而是参数文件：--操作系统层面：生成审计文件记录
SQL> show parameter audit_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ----------------------
audit_file_dest /picclife/app/oracle/admin/PROD/adump
*记录的什么信息：查询基表：
truncate table aud$;
SQL> conn yang/yang
---记录登录信息：
*精细化审计信息存储的FGA_LOG$无关：
SQL> select count(*) from fga_log$;
COUNT(*)
----------
0
*如何关闭：需要重启库：
SQL> alter system set audit_trail=none scope=spfile;
SQL> startup force;
解决OK：
番外：##GETDDL 索引语法：
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000000417C00013$$','SYS') FROM DUAL;
--查询系统表空间总大小
SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='SYSTEM';
SUM(BYTES)/1024/1024
--------------------
325
--查询系统表空间已使用的大小
SQL> select sum(bytes)/1024/1024 from dba_SEGMENTS where tablespace_name='SYSTEM';
SUM(BYTES)/1024/1024
--------------------
275.25
--查询系统表空间对应的数据文件名称
SELECT FILE_NAME FROM DBA_DATA_FILES where tablespace_name='SYSTEM'
FILE_NAME
---------------------------------------------
/picclife/app/hukou/data/system01.dbf
--增加数据文件大小
SQL> ALTER database datafile '/picclife/app/hukou/data/system01.dbf' resize 500m;