dba一些常用的管理语句（不断更新）

------------------UNDO----------

oracle会在修改数据的时候把这条数据存一份到undo段中（没修改之前的状态）

select * from v$rollname;           ------在线的回滚段

select segment_name,OWNER,tablespace_name,status from dba_rollback_segs;    ------所有的回滚段

select XIDUSN from v$transaction;    ------当前事物用到的回滚段的USN

SELECT SUM(undoblks) / SUM((end_time - begin_time) * 86400)FROM v$undostat;     -----计算平均1秒产生多少个undo块。

create  rollback segment  r1 tablespace undo2;                  ------手工创建一个新的回滚段  （undo_management=manual 才能使用）

alter rollback segment r1online;

drop rollback segment r1 ;

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

SQL> select * from v$rollname;
 
       USN NAME
---------- ------------------------------
         0 SYSTEM
         1 _SYSSMU1_812862839$
         2 _SYSSMU2_2273225004$
         3 _SYSSMU3_98857225$
         4 _SYSSMU4_2159844899$
         5 _SYSSMU5_3361858080$
         6 _SYSSMU6_1796026529$
         7 _SYSSMU7_1608419395$
         8 _SYSSMU8_1178788706$
         9 _SYSSMU9_2880222061$
        10 _SYSSMU10_2611178102$

SQL> select segment_name,extent_id,file_id,block_id,blocks,tablespace_name from dba_extents where segment_name='_SYSSMU1_812862839$';
 
SEGMENT_NAME                                                                      EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS TABLESPACE_NAME
-------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ------------------------------
_SYSSMU1_812862839$                                                                       0          3        128          8 UNDOTBS1
_SYSSMU1_812862839$                                                                       1          3        288          8 UNDOTBS1
_SYSSMU1_812862839$                                                                       2          3       2432        128 UNDOTBS1
_SYSSMU1_812862839$                                                                       3          3       2560        128 UNDOTBS1

SQL> select owner,SEGMENT_NAME,BLOCKS,EXTENTS from dba_segments where SEGMENT_NAME='_SYSSMU1_812862839$';
 
OWNER                          SEGMENT_NAME                                                                         BLOCKS    EXTENTS
------------------------------ -------------------------------------------------------------------------------- ---------- ----------
SYS                            _SYSSMU1_812862839$                                                                     272          4
 

Undo段中区的状态

free
expired
inactive
active

显示UNDO区信息
SELECT extent_id, bytes, status FROM dba_undo_extents
WHERE segment_name='_SYSSMU1_812862839$';

 EXTENT_ID      BYTES STATUS
---------- ---------- ---------
         0      65536 EXPIRED
         1      65536 EXPIRED
         2    1048576 EXPIRED
         3    1048576 UNEXPIRED

undo_retention这个参数是保留commit的undo信息（或者说undo段中的inactive的extent）
alter tablespace undotbs1 retention guarantee              -----保证undo_retention的时间内inactive的extent（提交了的undo信息）不被覆盖
alter tablespace undotbs1 retention noguarantee         ----不保证，默认

select tablespace_name ,retention from dba_tablespaces;

--------------闪回--------------

flashback database to scn ###### (归档模式下、flashback_on打开、mount下)

闪回数据库的3个参数：db_recovery_file_dest、db_recovery_file_dest_size、db_flashback_retention_target

flashback table t1 to scn #####(闪回DML操作，flashback_on不需要打开)

flashback table t1 to before drop (从recyclebin里闪回来，flashback_on不需要打开)

闪回版本查询（可以找出undo_sql）

1、 select versions_starttime, versions_endtime, versions_xid,versions_operation, sal

from t1

versions between timestamp minvalue and maxvalue

order by VERSIONS_STARTTIME;

2、  SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY  WHERE XID = '08001100C7010000';

------------------临时表空间-----------------

create temporary tablespace temp2 tempfile'........' size 20M reuse;             ----建新的

drop tablespace temp2 including contents and datafiles;                              ----删老的

alter database defalut temporary tablespace temp2;                                      -----改默认

------------------临时表----------------------

create global temporary table tmp1 on commit preserve rows as select * from emp;      -----SYS$session  会话级，会话结束表才清空

create global temporary table tmp2 on commit delete rows as select * from emp;          ------SYS$transaction 事物级 ，事物结束清空  

select table_name,LOGGING,TEMPORARY,DURATION from user_tables;
                    TMP2       NO                 Y             SYS$TRANSACTION
                    TMP1       NO                 Y             SYS$SESSION

临时表存在排序段中（sort_segment）,排序段在临时表空间,所以说临时表的数据存储在临时表空间里

 select tablespace_name,total_blocks,used_blocks,free_blocks,current_users from v$sort_segment;

-----------------move、shrink-------------------------------

alter table t1 move tablespace users;                 消除碎片（delete掉的数据），降低高水位，释放空间( 单位是extent)，消除行迁移，改变了rowid，索引失效需重建  

----move 就相当于咵咵咵把数据揉实了，然后找个新的空间放进去。

 alter table t1 shrink space （compact）;           消除碎片，降低高水位，释放空间，索引不失效，产生大量的undo和redo。

-----shrink 就相当于把数据用小锤子夯实了，还在原来的地方。

碎片：

表空间的碎片------>分配新的范围所致，会有一些分散的，较小的空间被跳过-------->碎片是extent级的--------->影响：空间利用率差 

解决办法：手动合并alter tablespace XXX coalesce;      貌似LMT的表空间就不用管它了，系统会自动帮你合并表空间的碎片

表的碎片--------->delete等DML操作所致，HWM以下产生了一下空块或不满的块----------->碎片是block级的 -------->影响：导致全表扫描效率低，因为HWM是全表扫描的终点

解决办法：move，shrink等

-----------------------------------------------sqlldr---------------------------------------------------------------------------

load data

(infile '/bk/1.txt')

(append/ replace /truncate)

into table t1

fields terminated by ','   (optionally enclosed by '"')

(deptno,dname,loc,createdate DATE "yyyy-mm-dd hh24:mi:ss")

sqlldr scott/tiger control=/bk/2.txt discard=/bk/dis.txt bad=/bk/bad.txt (data=/bk/1.txt) log=/bk/log.txt

----------------------------外部表的使用--------------

grant create any directory to scott

create directory bk as '/bk'

select * from dba_directories;

CREATE TABLE t1 (
DEPTno NUMBER, Dname CHAR(20), LOC CHAR(20))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER 
DEFAULT DIRECTORY bk 
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE 'bad.txt'
LOGFILE 'log.txt'
FIELDS TERMINATED BY ' '
(DEPTno CHAR,
Dname CHAR,
LOC CHAR))
LOCATION ('1.txt'))
PARALLEL 5 
REJECT LIMIT unlimited;

1.txt的内容是：

10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

--------------------处理挂起的事物（resumable）------

这个功能主要是防止空间不够导致session failed  （参见：http://blog.163.com/yanenshun@126/blog/static/1283881692012917102016513/）

例如你create一个很大index,三天三夜后，完成了99%，这时候表空间用完了（autoextent off）导致create失败，那只能吐血了

有了resumable session后，他会等待一段时间，等你有了空间后继续

当事物缺少某些资源不能运行的时候，会有两种处理方法：①自动的回滚当前语句（默认） ②将事物挂起，等待新的资源

Alter session enable resumable (timeount 3600);      ------开启挂起的特性

                 select  sid from v$mystat where rownum=1;   ----看当先的session id

select dbms_resumable.get_session_timeout(159) from dual;  -----看159会话的挂起时间

                select event from v$session_wait where sid=159;    ---看看159的等待事件

select * from dba_resumable;              ----检查被挂起的事物的原因

---------------------------索引--------------------------------------

user_indexes、user_ind_columns

create index i_t1_empno on t1(empno);

analyze index i_t1_empno validate structure;     ------分析索引

select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats;

alter index i_t1_empno coalesce;                  ------只是合并枝干内的叶子，不会改变索引的结构和高度，不释放段所拥有的空间，可以有事物

alter index i_ti_empno rebuild (online)        --------将老的抛弃，重新建立一个新的，降低高度改变结构，有事物的话会报错，online的话事物挂起

挂起以后咱们可以用下面的语句去看看等待事件

select event from v$session_wait where sid=159;

EVENT
-----------------------------
enq: TM – contention                           ------行级锁争用

*******************************************************

顺带脚说说锁吧

可以通过DBA_blockers,DBA_waiters 这两个字典找出哪个session占用了锁，哪个在等待锁

也可以这样：select  sid from v$lock where request<>0;         ------被阻塞的sid

                        select  sid from v$lock where block<>0;              -------阻塞方的sid（block=1就是阻塞方）

ok~~ 找到了sid我们也就能找到serial# ，然后杀了它：alter system kill session 'sid,serial#';

*******************************************************

号外号外~~~~

①主键对应的唯一索引是不能被单独删除的(会报错的！)，换句话说就是：有主键/唯一约束必定有对应的唯一索引，而有唯一索引不一定有对应的主键/唯一约束（这里我说个情况，当时只有sb才会这么做，就是我先手工建立一个非唯一的索引，然后建立主键/唯一约束，然后主键/唯一约束和这个非唯一索引也能对应上，就和第一条相违背了）

②我们在删除主键约束时可以保留唯一索引：alter table t1 drop constraint pk_t1_empno keep index

******************监控索引的使用情况***************

alter index pk_emp monitoring usage;

alter index pk_emp nomonitoring usage;   

select * from v$object_usage;

索引名称              表名称          被监视否           使用过否         开始监视时间                   结束监视时间

INDEX_NAME    TABLE_NAME    MONITO     USED       START_MONITORING      END_MONITORING
----------                ---------------           ------           ------         -------------------------              -------------------
PK_EMP                EMP                     YES            NO          10/13/2006 23:20:04

select 'alter index '||index_name||' monitoring usage;' from user_indexes;      ------这个看得懂吧

alter index PK_EMP monitoring usage;
alter index I_T1_EMPNO monitoring usage;
alter index PK_DEPT monitoring usage;

我们运行这些sql就会监视该用户下的所有index

在程序运行一段时间后，我们看看那个索引没有used，然后给它删了，当然主键的唯一索引删除要考虑一下喽~~

--------------------------------约束constraint-------------------------------------

select table_name,constraint_name,constraint_type,status,validated,deferrable,deferred from user_constraints;                    -----查看约束的状态

*************延迟约束****************

alter table t1 add constraint u_t1 unique(EMPNO) initially deferreddeferrable;                 ------建立一个初始状态为延迟的可延迟约束

*alter table t2 modify (empno number constraint u_t1 unique initially deferred deferrable);                                 ------同上

如果一个约束是可延迟约束

当前会话的属性决定约束是否延迟

alter session set constraint=immediate       ------可延迟的约束立即检查

alter session set constraint=deferred           ------可延迟的约束会延迟

alter session set constraint=default              ------约束初始状态是什么就是什么 (就看deferred字段了)

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

㈠    唯一约束和主键约束都会自动创建唯一索引   ，其他约束不创建索引

㈡    想把一个非延迟的约束改为延迟约束：只能删除这个约束再重新建立

㈢    延迟约束，会导致建立的索引是非唯一（主键约束、唯一约束）

㈣    停掉约束后，约束自带的索引（索引和约束同名）被删除   （延迟约束自带的索引不会被删除）     (如果是先手动建立的索引，停掉约束，索引不删除)

*㈤  延迟约束比较特殊，自带的索引不会随着约束的删除\停掉而被删除

*㈥  事先手工建立的索引，不会随着对应约束的删除\停掉而被删除

-------控制约束的状态--------

alter table t1disable constraint u_t1;               ------------停掉约束后，约束自带的索引被删除   （延迟约束自带的索引不会被删除）

select table_name,constraint_name,constraint_type,status,validated,deferrable,deferred from user_constraints;      ---- 看约束的状态

TABLE_NAME                     CONSTRAINT_NAME                C       STATUS         VALIDATED                DEFERRABLE                 DEFERRED

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

T2                                               U_T2                                      U      DISABLED    NOT VALIDATED     NOT DEFERRABLE         IMMEDIATE

select table_name,index_name,index_type,uniqueness from user_indexes;          ----看看索引

alter table t1 enable constraint u_t1;              -------------启用约束后，索引重新建立

**************约束的容错方法*******************

当为了加速批量插入数据的速度 把表上的约束disable掉，插入的有违反约束的行，enable时 起不来，为了让数据库能立即上线 不能立即去处理数据            怎么办？

使用约束容错方法：

①约束不带有唯一索引（外键 非空 check）

alter table t1 enable novalidate constraint x                           -------novalidate对表中已有数据不作检测 但新录入做检测

②约束带有唯一索引 (唯一 主键)

要先建立一个非唯一性的索引：create index IND_NAME on t1(column_name);

alter table t1 enable novalidate constraint x using index IND_NAME;

（索引变成了非唯一索引，搜索时多了一步范围扫描）

####等有时间了，把违反约束的行找出来，剔除掉

***********找出违反约束的列***************

oracle提供一种简单方法 利用约束启动时的检测过程 将违反的约束行存起来

SQL> @?/rdbms/admin/utlexpt1.sql     ----创建一个exceptions表

alter table t1 enable constraint u_t1 exceptions into exceptions;          -----将违反约束的行存起来

select * from exceptions                          ----可以查到rowid

-----------------------------------------用户相关-----------------------------------------------------------

dba_users   ------看用户相关的属性

alter user u1 quota 1m on users;      ----限制用户在users表空间的磁盘配额

alter user u1 quota 0 on users          ----配额为零，不再分配新的空间，现有的可以继续使用

select * from dba_ts_quotas             ----查看用户的配额情况

***************profile*****************

profile 是限制用户使用资源的一种手段（密码管理策略、限制会话的资源）

数据库初始有两个profile：default、MONITORING_PROFILE；

create profile p1 limit failed_login_attempts 2;  

alter profile p1 limit PASSWORD_LOCK_TIME 2;

drop profile p1 cascade

select * from dba_profiles;                  ----看看现在有几个profile

alter user u1 profile p1;                        ----把p1赋予u1

select username,profile from dba_users;

密码管理策略：                                                                      

PASSWORD_VERIFY_FUNCTION
PASSWORD_REUSE_MAX
FAILED_LOGIN_ATTEMPTS
PASSWORD_LOCK_TIME
PASSWORD_LIFE_TIME 30
PASSWORD_GRACE_TIME 3
PASSWORD_REUSE_TIME
以上都是限制密码的，time单位是天

限制会话的资源：

LOGICAL_READS_PER_CALL
LOGICAL_READS_PER_SESSION
CPU_PER_CALL
CPU_PER_SESSION
PRIVATE_SGA
COMPOSITE_LIMIT
IDLE_TIME
CONNECT_TIME
SESSIONS_PER_USER

以上是限制会话资源的，生效的前提是alter system set resource_limit=true;

*************权限的管理***************

权限这块总结了挺多，看似很乱~！其实你把：系统权限、对象权限、角色之间的关系弄明白了，就清楚了！（重点看紫底儿的）

系统权限就是系统权限、对象权限就是对象权限、角色中既能有系统权限也能有对象权限     ----不是废话~~记住~！

 系统中有166个权限

 select * from session_privs     -------查看当前用户拥有的系统权限(包括系统权限和角色中的系统权限，像select on scott.emp这样的权限看不见)

想看一个用户下的所有权限：① 系统权限：select * from session_privs;   +    ②对象权限：select * from user_tab_privs;      这两条加起来就是所有权限

 

 select * from dba_sys_privs where grantee='SCOTT';    ------查看用户和角色的系统权限授予情况

授权的级联

①with admin option权限回收无级联、适用系统权限和角色     eg：grant select any table to u1 with admin option;

②with grant option  权限回收有级联、适用于对象权限                eg:  grant select on scott.t1 to u1 with grant option;

是否级联可以查看相应的字典：

㈠dba_sys_privs    查看系统权限的授予了哪些用户或角色：select * from dba_sys_privs where grantee in ('U1','U2');

㈡dba_role_privs   查看角色的授予情况：select * from dba_role_privs where GRANTEE in ('U1','U2');

㈢dba_tab_privs    查看普通表的权限的授予情况：select * from dba_TAB_privs where GRANTEE in ('U1','U2');

   *dba_col_privs

㈣我要查看一个角色中有哪些权限：

SELECT * FROM dba_sys_privs WHERE grantee IN('CONNECT','RESOURCE');

select  * from dba_role_privs where grantee in ('connect','resource');

select * from dba_tab_privs where grantee in ('connect','resource');

以上三个结果的总和就是角色的权限

*Resource 角色被授予后用户自动有UNLIMITED TABLESPACE 的系统权限。UNLIMITED TABLESPACE 就是你可以再任意表空间建立表

************自定义角色************

 select * from dba_roles      ----数据库内所有的角色

默认角色是针对用户而言的，角色本身并没有默认不默认（从dba_role_privs 中看）：就是用户登录后这个角色你就能用，不是默认角色的话，你需要激活这个角色          ---这个了解即可，应该不怎么用

给角色加上密码：

create role r1 identified by r1;

alter role r1 identified by r1;

指定某个用户的某个角色为默认角色（实际上就是我可以将某个用户的角色给关掉）

u1 现在有两个角色：connect和resource

conn /as sysdba

ALTER USER U1 DEFAULT ROLE NONE;     ------我把u1的所有角色都关了，他啥角色都不能用了，把所有角色都置成非默认

alter user u1 default role connect ;                    -------我把u1的connect角色开了，把connect置成默认

conn u1/u1

set role resource （identified by XXX）           --------u1登录后自己把resource角色激活，如果这个角色在创建的时候有密码就用到括号里的了

---------------------------------备份----------------------------------------------------

expdp\impdp(数据泵)

比如我从win的pc上导出linux服务器上的数据

PC（win）：

conn /as sysdba

create directory ok as 'E:\bk';

create public database link testlk connect to system identified by managerconfig using 'testyd';

select * from scott.t1@testlk;

expdp system/managerconfig full=y directory=ok dumpfile=full.dmp network_link=testlk

********************

热备

备份数据文件：①alter tablespace users begin backup;

    ②cp  ......

    ③alter tablespace users end backup;

select * from v$backup;

备份控制文件：①热备份控制文件的建立脚本到udump下：

alter database backup controlfile to trace;

    ②热备份当前的控制文件：

alter database backup controlfile to 'c:\bk\control.bak';

热备份中几个常用的命令： 

说白了啊，热备份就三步   ①定位那个文件坏了②cp ③recover      然后再琢磨琢磨下面这5个绿底的命令

1、select name,checkpoint_change# from v$datafile;                        -----控制文件中的信息

2、select name,checkpoint_change# from v$datafile_header;        ------数据文件头的信息

3、select name,status from v$datafile;                                                 -------数据文件的状态：（online，recover，offline）

4、select * from v$recover_file;                                                              ------需要恢复的数据文件

5、select * from v$recovery_log;

6、alter tablespace users offline(online);

-------------------无备份的恢复--------------------------------------

⒈ alter database create datafile '/u01/app/oracle/oradata/tt/ly.dbf' (as '/bk/ly.dbf');   

⒉ alter database rename file '/u01/app/oracle/oradata/tt/ly.dbf' to '/bk/ly.dbf';

1和2的关系：其实没什么关系......2就是给数据文件改名字，1就是根据控制文件从新建立一个新的空的数据文件（就和最初刚建立这个数据文件的时候一样），一般用于无备份的恢复，新建完了跑归档。

select name,creation_change# from v$datafile;         -----看一数据个文件的创建时的SCN

select name,first_change# from v$arcived_log;         ------归档的信息

--------------------恢复索引表空间-------------------------------------------------------

一句话:取创建索引的元数据，删除表空间，然后从新建立表空间，用元数据创建索引！（也可以cp后recover那么恢复）

-----------------这笔账怎么算-----------------------------

rowid

AAAMfP      AAE      AAAAAg   AAA
对象代码    文件        块             行

dbms_rowid.rowid_object(rowid)  object#

dbms_rowid.rowid_relative_fno(rowid)  file#

dbms_rowid.rowid_block_number(rowid) block#

dbms_rowid.rowid_row_number(rowid) row#

select segment_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name='T1';                -----3 ID

 select dbms_rowid.rowid_block_number(rowid),count(*) from t1 group by dbms_rowid.rowid_block_number(rowid);        -------每个块里对应的行数

analyze table t1 compute statistics

select table_name,blocks,empty_blocks,avg_space,num_freelist_blocks from dba_tables where table_name='T1';          ----avg_space是所用使用的块里的平均剩余空间，num_freelist_blocks是空闲列表指向了几个块，blocks是用了的块，

empty_blocks是没用的块              ||这里面的blocks和empty_blocks我现在只能用来看个大大概数，分不清哪个算用了的，哪个算没用的？

***************字符集************

select * from nls_database_parameters;        -----这个是库的

PARAMETER                                    VALUE
------------------------------              --------------------------------------------------
NLS_LANGUAGE                              AMERICAN                                                  -----oracle的错误提示、时期的字符集（simplified chinese）
NLS_TERRITORY                             AMERICA             ------oracle的货币的字符集

NLS_CHARACTERSET                   ZHS16GBK                                                  ----数据库字符集

NLS_NCHAR_CHARACTERSET   AL16UTF16    ----国家语言字符集

NLS_DATE_FORMAT                       DD-MON-RR

select * from v$nls_parameters;                      -----这个是当前session的

NLS_LANG=language_territory.characterset

**NLS_LANG的字符集要和客户端的操作系统一样（linux命令locate，win命令chcp）

-----转化当前session的错误提示、日期、货币的输出字符集

 alter session set NLS_LANGUAGE='simplified chinese';                       -----设置后，当前会话的提示、日期就是中文的了

alter session set NLS_TERRITORY=china                                                 ------设置后，当前会话的货币格式就是中国的了

也可以这样：set NLS_LANG=simplified chinese_china.zhs16gbk  (windos)

export NLS_LANG='simplified chinese_china.zhs16gbk'   (linux)

********取元数据**************

set long 10000

select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;         ------表的

select dbms_metadata.get_ddl('INDEX','PK_EMP') from dual;                    ---索引的

select dbms_metadata.get_ddl('SEQUENCE','SYSTEM_GRANT','SYS') from dual;            ----序列的

select dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') from dual;                          ---表空间的

select
'select dbms_metadata.get_ddl('||''''||'TABLE'||''''||','||''''||table_name||''''||') from dual;'
||chr(10)
||'select '||''''||'/'||''''|| ' from dual;' from user_tables;                                                          ---- 批量生成元数据

*********DBlINK*********

create public database link TTLK connect to scott identified by tiger using 'TT';

select * from dba_db_links;

select * from emp@ttlk

alter session close database link ttlk;                        ----关闭

drop public database link ttlk;

***************SCN********************

select checkpoint_change# from v$database;                   ------存在控制文件中

select name,checkpoint_change# from v$datafile;           ------存在控制文件中

select name,checkpoint_change# from v$datafile_header;       -----存在数据文件的头

select * from v$recover_file;

select * from v$recovery_log;

*************日志挖掘*****************

alter database add supplemental log data;               --------开启后，会额外记录DML的信息，你才能挖到DML操作

alter database drop supplemental log data;             --------关闭后，就只能挖到DDL操作了

select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

--日志中加入额外的信息

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) columns;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) columns;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA 
select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI
from v$database; 

exec dbms_logmnr.add_logfile('.....');

select filename from v$logmnr_logs;                        ----查看正在挖哪些日志

exec dbms_logmnr.start_logmnr(options=>16);     /       exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

select ..... from v$logmnr_contents where ...;

exec dbms_logmnr.end_logmnr;

*******************碰巧遇到一个小问题****************************

select * from v$flash_recovery_area_usage;                -----这个不是内存这块的，碰巧看见，查看闪回区的使用情况。  我的归档路径是默认的，在闪回区，我发现我切换不了用户了，报错：ORA-00257:archiver error.Connect internal only, until freed，原来是闪回区的归档日志满了 。从查询到的结果能看到

    FILE_TYPE    PERCENT_SPACE_USED         PERCENT_SPACE_RECLAIMABLE              NUMBER_OF_FILES
------------             ------------------                                   -------------------------                                              ---------------
CONTROLFILE                   0                                         0                                                        0
ONLINELOG                     0                                            0                                                       0
ARCHIVELOG                99.42                                        0                                                    49
BACKUPPIECE                   0                                        0              0
IMAGECOPY                     0                                            0              0
FLASHBACKLOG                  0                                      0               0                     

删除一些日志或者扩大闪回日志文件的最大大小

alter system set DB_RECOVERY_FILE_DEST_SIZE=10g

*************内存相关*************************

一、shared_pool

share pool分为两个部分

1、library cache（库高速缓存）： 加速解析 减少语句重解析，曾经使用的SQL，PL/SQL 的语句和执行计划

2、data dictionary cache 又叫rowcache（数据字典高速缓存）：加速sql的解析，使用过的字典 就会缓存在这里 再次使用这个字典就直接从内存获取，这些统计的信息在 v$rowcache中记录

v$sgastat  ----查看sga的状态

select * from v$sgastat where pool='shared pool' and name='free memory';        -----查看sga中shared_pool的空余空间；

select sum(pinhits)/sum(pins) from v$librarycache;                              ----查看librarycache的sql命中率

如果sql的命中率在90%一下就有点低了，这是我们就要想办法优化了。

1、加大 shared_pool_size 的大小,但也不要太大,太大会增加管理的额外费用（这条是在sga手动管理的情况下）

2、编写程序的时候使用变量传入,而不是使用常量（对dba有点不实际，对开发人员说的~！）

3、将大的包定在内存中

4、修改初始化参数 cursor_sharing(这个我们重点说一下)

可以做一个试验：① create table t1 as select * from emp ;建立一个10W行左右的表，然后让修改empno这一列，让第一行位2000，剩下的都为1000，然后在empno上建立索引

② 修改参数cursor_sharing的值，打开执行计划，观察各种情况下select *  from t1 where empno=1000; 和select * from t1 where empno=2000;下有什么不同

完成上面的试验，总结得到：

1、强制匹配（force）命中高：将where的条件都用变量来处理，提高了命中率，但是不能区分列值的敏感性，会导致部分sql的语句的执行计划不正确！

上面试验empno=2000的情况，拿到了empno=1000的执行计划，走了全表扫描,而没走索引！

2、近似匹配（similar）：将where 条件都用变量来处理，提高了SQL 的命中率，但可以区分列值的数据敏感性，既保证了语句的复用，又提高的命中率，又可以区分列的条件差异。但oralce 有的时候会有bug，导致美好的东西变成了泡影，所以我们改了以后要测试一下性能。

上面试验empno=2000的情况，走了索引，没用empno=1000的执行计划！

3、精确匹配（exact）命中低：原语句不处理，是什么就是什么，降低了sql的命中，但是保证执行计划是正确的。oracle默认值！

试验中empno2000，走索引，empno=1000，走全表扫描！

写到这里我有一个疑问，如果我的程序都用的是绑定变量，那么，我的sql语句也可能会拿到不正确的执行计划吧？

***********************************************************

查看数据库中那些表的物理读最多：

select * from
(select object_name,statistic_name,statistic#,value from v$segment_statistics where statistic_name='physical reads' order by value desc)
where rownum<=10
/

查看emp表的统计：

select object_name,statistic_name,statistic#,value from v$segment_statistics where object_name='EMP';

OBJECT_NAME                    STATISTIC_NAME                 STATISTIC#      VALUE
------------------------------ ------------------------------ ---------- ----------
EMP                            logical reads                           0         16
EMP                            buffer busy waits                       1          0
EMP                            gc buffer busy                          2          0
EMP                            db block changes                        3          0
EMP                            physical reads                          4         12
EMP                            physical writes                         5          0
EMP                            physical reads direct                   6          0
EMP                            physical writes direct                  7          0
EMP                            gc cr blocks received                   9          0
EMP                            gc current blocks received             10          0
EMP                            ITL waits                              11          0
EMP                            row lock waits                         12          0
EMP                            space used                             14          0
EMP                            space allocated                        15          0
EMP                            segment scans                          17          0

********************************收集统计信息*****************************************************

统计信息对执行计划有决定性影响：

execute dbms_stats.gather_database_stats;      ----全收集

execute dbms_stats.gather_database_stats_job_proc;    ----gather_stats_job调度任务的跑的存储过程

execute dbms_stats.gather_schema_stats('SCOTT',dmbs_stats.auto_sample_size);

execute dbms_stats.gather_table_stats('SCOTT','EMP');

*******************************sys用户的登陆验证*****************************************

1, remote_login_passwordfile 是用来控制能否以as sysdba来连接到数据库. 如果为exclusive, 则远程可通过conn sys/***@*** as sysdba来登陆; 如果为None, 则远程可通过conn sys/***@*** as sysdba来登陆时会提示用户名密码错误(其实是不能以as sysdba登陆的原因).
2,sqlnet.ora文件中SQLNET.AUTENTICATION_SERVICES是用来控制在本机中以as sysdba登陆时,是不是需要输入sys/****. 如果SQLNET.AUTHENTICATION_SERVICES=(NONE), 则要以conn sys/*** as sysdba登陆; 如果SQLNET.AUTHENTICATION_SERVICES=(NTS), 以conn /as sysdba即可.

******************************硬解析过多的危害******************************************************

1、慢，优选执行计划的时候消耗cpu资源

2、会造成shared pool latch

3、使free空间中产生大量的碎片（小chunk）