记录2

qq493288977

1.
    DML (data manipulation language)语句:数据操纵语言
        它们是SELECT、UPDATE、INSERT、DELETE
    DDL (data definition language)  语句:数据定义语言
        主要的命令有CREATE、ALTER、DROP等      
    DCL (data control language)     语句:数据控制语言
        用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。
        在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL
   
    索引:由表中一列的值快速定位到表中的一行。   
    表分区：表分区使你可以生成可伸缩的应用程序
    正常范围分区表的优点应该是：
    1.当不同的表空间存储在不同的存储设备时，可以将分区表储存在多个表空间，进而降低I/O
    2.各个分区维护各自的本地索引
    3.select语句可以根据索引进行分区范围扫描
    4.可以对单个分区进行备份或者删除
    5.方便对表的分区进行添加，删除，合并   
    正常范围分区表的缺点应该是：
    1.分区表不能跨分区定义唯一制约
    2.insert性能下降
    3.复杂的表检索时性能可以下降
2.   
    在数据库中，UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。
    UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果
    而UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。     
    sqlplus settlementdb/settlementdb@settlementdb <<!   
     define db_user_name="${db_user}"
     define db_passwd="${db_user}"
     @${_WORK_DIR}/sql/xxx.sql
     exit
    !   
    xxx.sql:
    create user &&db_user_name identified by &&db_passwd default tablespace &&aaa_tab_space;
    grant connect, resource to &&db_user_name;
    grant select on sys.v_$session to &&db_user_name;
    grant select on sys.v_$instance to &&db_user_name;
    exit;
    show all即可看到所有环境变量的默认值。   
    SGA:System Global Area   
    Spfile文件路径
    show parameter pfile
   
    控制文件路径
    desc v$controlfile;
   
    在线日志
   
    归档日志目录
    查看初始化参数
    show  parameter  log_archive_dest
    看到是哪个目录就是它了
    如果没有值，再查
    show parameter db_recovery_file_dest   
    查看是什么模式 是容灾还是生产
    select database_role,protection_mode,protection_level from v$database;

    将容灾机的DataGuard设置成real-time apply模式，这样在生产机的动作会立马在容灾机体现出来。
    alter database recover managed standby database using current logfile disconnect from session;  
    show parameter db_unique_name   
    lsnrctl
    $ORACLE_HOME/network/admin
    show parameter log_archive_dest_2
    生产机：
    alter system set log_archive_dest_2='SERVICE=droradg01 OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=drora85';
    容灾机：
    alter system set log_archive_dest_2='SERVICE=oradg01 OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora85';

    alter system set db_file_name_convert='+DG_DATA','+DG_DATA' scope=spfile;
   
    show parameter db_file_name_convert
   
    show parameter log_file_name_convert
   
    create pfile='initora86.ora' from spfile;
   
    alter system set db_file_name_convert='+DG_DATA/ora85','+DG_DATA/ora86' scope=spfile;
   
    alter system reset db_file_name_convert scope=spfile
   
    $ sqlplus '/as sysdba'
    SQL> create spfile from pfile;
    如果pfile没有放到$ORACLE_HOME/dbs/下，而是放在别的位置：
    SQL> create spfile from pfile='/backup/initPHYSTDBY.ora';

    1.在最大保护模式下，容灾机如果是read-only模式。如果生产机不停止，容灾机是停不了的，会报错ORA-01154: database busy. Open, close, mount, and dismount not allowed now
   
    这时必须先停生产再停容灾， 而在最大性能模式下则没有这些限制。
   
    2.生产机如果报ORA-03113: end-of-file on communication channel错，表示没有一台standby的容灾机是开启的。可以将保护模式降级来解决。
   
    3.数据库处在哪种模式下和log_archive_dest_2是syns还是asyns没有必然的关系，log_archive_dest_2设置成syns是能切换到最大保护模式下的必要条件，而非充分条件。
    即log_archive_dest_2为syns数据库也可以在最大性能下运行。
   
   
    查看表或者视图的字段：
    desc v$database;
    字段值模糊匹配用like
   
    lsnrctl start listener_pm
    lsnrctl start/stop默认是操作listener
    如果其它listener需要加上名字.要不是对默认listener操作.
   
    RAC，全称real application clusters，译为“实时应用集群”， 简单的说就是一个数据库，多个实例，数据库是存放在共享存储上，每个实例对应一台服务器。
   
   
    scope=both：同时改配置文件和正在运行的实例参数
    scope=spfile：只是改配置文件，需要重新启动以后生效
   
    CBO : cost based optimizer
    RBO : rule based optimizer

    当发生数据丢失时，很难查到原因，不知道究竟是由于程序问题还是在数据库中误操作引起的，而oracle审计功能，可以有效帮助我们对重要的数据表进行跟踪，以确定数据变更发生在什么时间，从哪里发出及变生变更时使用的SQL语句等．
    　　开启审计：
    　　Oracle从9i开始已提供了审计功能，但由于审计功能会降低系统性能，因此数据库默认是关闭审计的。
    要确认在数据库安装时是否安装了审计功能，请在sys用户下执行：
    Select count(*) from user_tables where table_name='AUD$';
    如果返回结果为0，表明审计还未安装，必须先在sys用户下执行:
    @$ORACLE_HOME/rdbms/admin/cataudit.sql
    来安装审计功能。
   
    　　要打开审计功能，可通过调整一个参数实现：
    　　alter system set audit_trail=DB,Extended scope=spfile;
    调整完后，要重启数据库，此时，审计功能已打开，可以使用。但由于审计结果的数据量较大，结果表sys.aud$存放在系统表空间system中，会导致系统表空间膨胀严重，因此，推荐先将aud$表移到独立的表空间中去．
   
    　　上述工作完成后，审计已经开启，但此时由于还没有设置要审计的具体对象，因此，还不会有审计数据记录。所有要审计的对象可能过查询下面的视图得到：
    select * from DBA_OBJ_AUDIT_OPTS;
    其中，owner表示要审计的表的拥有者，object_name表示要审计的对象的名称，object_type表示要审计的对象的类型，可能是表，也可能是视图等，其它都是要审计的操作项目，由于我们只关注：insert、update与delete，因此，只需要关注：ins、upd、del三列。
    要将一张表设置为审计对象，需要执行下面的命令(在对象拥有者下执行，对管控一般是共享库)：
    audit 操作 on 表名;
    如希望记录所有发生在表T1上的变更操作，可执行如下命令：
    Audio delete on T1 by access;
    Audio insert on T1 by access;
    Audio update on T1 by access;
    要取消某个对象的审计，命令如设置审计类似：
    noaudit 操作 on 表名
      这样，完整的数据库表审计操作已经大功告成，感兴趣的朋友不妨动手亲自操作一下．
   
   
    select * from DBA_AUDIT_POLICIES where ENABLED='YES'
    细粒度审计：
    添加审计策略
   
    begin
    dbms_fga.add_policy( object_schema =>'TCNEW',
                         object_name =>'test',
                         policy_name =>'chk_test',
                         audit_condition =>'a = 1',
                         audit_column =>'b',
                         enable =>FALSE
                       );
    END;
   
    激活审计：
    DBMS_FGA.ENABLE_POLICY(object_schema  VARCHAR2,   object_name    VARCHAR2,   policy_name    VARCHAR2,   enable  BOOLEAN);
   
    SYS用户审计（Auditing User SYS and Users Who Connect as SYSDBA and SYSOPER）：
    alter system set audit_sys_operations=TRUE scope=spfile;审计的日志在“audit_file_dest”参数指向的地方
   
   
    oracle的trace和alert日志目录使用show parameter BACKGROUND_DUMP_DEST查看
    $ORACLE_BASE/diag/rdbms/SID/SID/trace
   
   
    数据库由一个或多个表空间组成。一个表空间可以包括一个或者多个数据文件，但一个数据文件只能属于一个表空间。
    所有要删除数据文件语句为：drop tablespace <tablespace_name> including contents;
    物理上，表空间由一个或者多少数据文件；逻辑上，表空间由一个一个的段组成。
    段（即表、索引等）由一个或者多个（盘）区组成，（盘）区是不会跨文件的。
    （盘）区是由磁盘上连续的块组成的。
    块是数据库中最小的分配单元，也是数据库的最小I/O单元。一个数据块可能就对应了它所属的段（表、索引等）的一行数据记录。
   
    表空间：tablespace
    段：segment
    区：extent
    块：block
   
    SQL> spool output.txt
    SQL> @?/test.sql
   
    OPEN_MODE
    -----------
    READ WRITE
   
    SQL> spool off
   
    SQL> set line 300
   
    National Language Support (NLS)
   
    ............................................................
    --添加对密码复杂度的检查
    SQL> col PROFILE format a12
    SQL> col RESOURCE_NAME format a25
    SQL> col LIMIT format a15
    SQL> select * from dba_profiles;
   
    PROFILE      RESOURCE_NAME             RESOURCE_TYPE            LIMIT
    ------------ ------------------------- ------------------------ ---------------
    DEFAULT      COMPOSITE_LIMIT           KERNEL                   UNLIMITED
    DEFAULT      SESSIONS_PER_USER         KERNEL                   UNLIMITED
    DEFAULT      CPU_PER_SESSION           KERNEL                   UNLIMITED
    DEFAULT      CPU_PER_CALL              KERNEL                   UNLIMITED
    DEFAULT      LOGICAL_READS_PER_SESSION KERNEL                   UNLIMITED
    DEFAULT      LOGICAL_READS_PER_CALL    KERNEL                   UNLIMITED
    DEFAULT      IDLE_TIME                 KERNEL                   UNLIMITED
    DEFAULT      CONNECT_TIME              KERNEL                   UNLIMITED
    DEFAULT      PRIVATE_SGA               KERNEL                   UNLIMITED
    DEFAULT      FAILED_LOGIN_ATTEMPTS     PASSWORD                 10
    DEFAULT      PASSWORD_LIFE_TIME        PASSWORD                 180
   
    PROFILE      RESOURCE_NAME             RESOURCE_TYPE            LIMIT
    ------------ ------------------------- ------------------------ ---------------
    DEFAULT      PASSWORD_REUSE_TIME       PASSWORD                 UNLIMITED
    DEFAULT      PASSWORD_REUSE_MAX        PASSWORD                 UNLIMITED
    DEFAULT      PASSWORD_VERIFY_FUNCTION  PASSWORD                 NULL
    DEFAULT      PASSWORD_LOCK_TIME        PASSWORD                 1
    DEFAULT      PASSWORD_GRACE_TIME       PASSWORD                 7
   
    16 rows selected.
   
    SQL> @?/rdbms/admin/utlpwdmg.sql
   
    SQL> select * from dba_profiles;
   
    PROFILE      RESOURCE_NAME             RESOURCE_TYPE            LIMIT
    ------------ ------------------------- ------------------------ ---------------
    DEFAULT      COMPOSITE_LIMIT           KERNEL                   UNLIMITED
    DEFAULT      SESSIONS_PER_USER         KERNEL                   UNLIMITED
    DEFAULT      CPU_PER_SESSION           KERNEL                   UNLIMITED
    DEFAULT      CPU_PER_CALL              KERNEL                   UNLIMITED
    DEFAULT      LOGICAL_READS_PER_SESSION KERNEL                   UNLIMITED
    DEFAULT      LOGICAL_READS_PER_CALL    KERNEL                   UNLIMITED
    DEFAULT      IDLE_TIME                 KERNEL                   UNLIMITED
    DEFAULT      CONNECT_TIME              KERNEL                   UNLIMITED
    DEFAULT      PRIVATE_SGA               KERNEL                   UNLIMITED
    DEFAULT      FAILED_LOGIN_ATTEMPTS     PASSWORD                 10
    DEFAULT      PASSWORD_LIFE_TIME        PASSWORD                 180
   
    PROFILE      RESOURCE_NAME             RESOURCE_TYPE            LIMIT
    ------------ ------------------------- ------------------------ ---------------
    DEFAULT      PASSWORD_REUSE_TIME       PASSWORD                 UNLIMITED
    DEFAULT      PASSWORD_REUSE_MAX        PASSWORD                 UNLIMITED
    DEFAULT      PASSWORD_VERIFY_FUNCTION  PASSWORD                 VERIFY_FUNCTION

    DEFAULT      PASSWORD_LOCK_TIME        PASSWORD                 1
    DEFAULT      PASSWORD_GRACE_TIME       PASSWORD                 7
   
    16 rows selected.
3.
    安装了grid时，只有grid用户的tnsname.ora、listener.ora起作用，oracle用户下可以不用定义这些文件。
   
    listener监听器相当于一个代理的角色，他负责监听定义的端口的请求，并将请求转给数据库。
    如果只有一个实例，listener.ora中的HOST可以不用定义。有多个实例时通过端口不同区别。
    "SID_LIST_LISTENER"段已经不用定义，原先他是用来监听的静态注册的，现在都是动态注册。   
4.
    select TABLESPACE_NAME,BLOCKS,USER_BLOCKS from dba_data_files;
   
    select SEGMENT_NAME,OWNER,SEGMENT_NAME,BLOCKS,EXTENTS from DBA_SEGMENTS where tablespace_name='USERS' ;
   
    select * from dba_extents
   
    select table_name from user_tables;
   
    alter database backup controlfile to trace as '?/ctl.txt';
   
    alter database backup controlfile to trace;   
   
    SET AUTOTRACE ON
    SET AUTOTRACE OFF
   
    set autotrace on explain;
   
    set autotrace traceonly statistics
   
    rman> list backup of database;
   
    sqlplus / as sysasm
   
    AWR（Automatic Workload Repository）自动负荷信息库
5.
    alter database 改变的一个数据库的属性   一般改变的是物理上的  能看得见的
    比如 ：alter dtabase rename datafile
    alter system     动态的改变数据库例程的属性   一般是逻辑上的  看不见的
    比如：alter system set db_cache_size  
    alter session    改变的的是一个会话的属性
    比如：alter session set nls_language

    On 11gR2, Oracle Clusterware consists of two separate stacks:
    an upper stack anchored by the Cluster Ready Services (CRS) daemon (crsd)
    and a lower stack anchored by the Oracle High Availability Services daemon (ohasd).
   
    其中一个原因是因为在shutdown immediate前有大的transaction需要rollback，只能等待事务回滚完毕，数据库达到一致的状态，才能顺利shutdown.   
    因此，shutdown数据库之前最好察看一下是否有大的transaction在运行。   
    select sum(used_ublk) from v$transaction;
   
    TRUNCATE不会逐个清除用户数据块上的数据，而仅仅重置数据字典和元数据块上的元数据（如存储段头和扩展段图）。
    也就是说，此时，其基本数据并未被破坏，而是被系统回收、等待被重新分配.
    delete产生rollback，如果删除大数据量的表速度会很慢，同时会占用很多的rollback segments.
    truncate 是DDL操作，不产生rollback，速度快一些.
    truncate 调整TABLE的high water mark 而delete不.
    如果TRUNCATE一个表的分区，那么全局分区索引需要REBUILD
    当你不再需要该表时， 用 drop;
    当你仍要保留该表，但要删除所有记录时， 用 truncate;
    当你要删除部分记录时（always with a WHERE clause), 用 delete.
   
    group by:分组的含义就是这些字段都相等的合并为一条数据.
    在带有group by 子句的查询语句中，在select 列表中指定的列，要么是group by 子句中指定的列，要么包含聚组函数
    "group by 字段列表"表示根据后面的字段来分组,如果只有1个字段,那只是根据这个字段的值来进行一次分组就可以了；若后面有多个字段，
    那表示根据多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，然后在第1个字段值相同的记录中，再根据第2个字段的值进行分组；
    接着第2个字段值相同的记录中，再根据第3个字段的值进行分组......依次类推。
    SQL> select depid,sum(salenum) from sales group by depid;
    DEPID SUM(SALENUM)   
    ---------- ------------   
    1        70   
    2        20   
    3        20
    事实上只有dml语句和select...for update才会发生db block gets，你只要记住一点，db block gets永远是只从缓冲区中获得最新的数据
    (请注意‘缓冲区’和‘最新’)，而对于普通的select语句来说，如果数据还没装入缓冲区就是physical reads，否则就是consistent gets。
    db block gets      从buffer cache中读取的block的数量                  
    consistent gets    从buffer cache中读取的undo数据的block的数量,一致性读,就是发送SQL那个时刻的数据(可能涉及到未提交事务保留的undo信息等)
    physical reads     从磁盘读取的block的数量,当前(Current)读,就是Session最后时刻的数据,该数据可能是你修改过的数据,可以加锁的数据

col Tablespace format a20
select df.tablespace_name "Tablespace",
df.bytes/(1024*1024) "Total Size(MB)",
sum(fs.bytes)/(1024*1024) "Free Size(MB)",
round(sum(fs.bytes)*100/df.bytes) "%Free",
round((df.bytes-sum(fs.bytes))*100/df.bytes) "%Used"
from dba_free_space fs,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) df
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes;