本文共 18110 字，大约阅读时间需要 60 分钟。
一 传输表空间
将LEO1库的tsport表空间迁移到LEO2库中LEO1库的进行导出
[oracle@odd ~]$ sqlplus sys/oracle@LEO1 as sysdbaSQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files where file_id=1;FILE_NAME TABLESPACE_NAME---------------------------------------------------------------------------------------------------- ------------------------------/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/system01.dbf SYSTEMSQL> create tablespace tsport datafile '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/tsport01.dbf' size 20m autoextend off;Tablespace created.SQL> create user tsport identified by tsport default tablespace tsport;User created.SQL> grant connect,resource to tsport;Grant succeeded.SQL> conn tsport/tsport@LEO1Connected.SQL> create table t1 (a int) tablespace tsport;Table created.SQL> insert into t1 values (100); 1 row created.SQL> commit;Commit complete.SQL> create index idx_t1 on t1 (a) tablespace tsport;Index created.SQL> select index_name,table_name,tablespace_name from user_indexes where table_name='T1';INDEX_NAME TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------IDX_T1 T1 TSPORTSQL> conn sys/oracle@LEO1 as sysdbaConnected.SQL> select tablespace_name,segment_name,segment_type from dba_segments where tablespace_name='TSPORT';TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE------------------------------ --------------------------------------------------------------------------------- ------------------TSPORT IDX_T1 INDEXTSPORT T1 TABLESQL> exec dbms_tts.transport_set_check('TSPORT',true); PL/SQL procedure successfully completed.SQL> select * from transport_set_violations;no rows selectedSQL> alter tablespace tsport read only; Tablespace altered.SQL> !exp userid=\'/ as sysdba\' transport_tablespace=y tablespaces=tsport file=/home/oracle/exp_tsport.dmpExport: Release 10.2.0.1.0 - Production on Sat Jan 25 18:15:03 2014Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setNote: table data (rows) will not be exportedAbout to export transportable tablespace metadata...For tablespace TSPORT .... exporting cluster definitions. exporting table definitions. . exporting table T1. exporting referential integrity constraints. exporting triggers. end transportable tablespace metadata exportExport terminated successfully without warnings.SQL> !ls -l /home/oracle/exp_tsport.dmp-rw-r--r-- 1 oracle oinstall 16384 Jan 25 18:15 /home/oracle/exp_tsport.dmpSQL> !scp /home/oracle/exp_tsport.dmp oracle@even:~oracle@even's password: exp_tsport.dmp 100% 16KB 16.0KB/s 00:00 SQL> !scp /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/tsport01.dbf oracle@even:/home/oracle/oracle/oradata/LEO2/oracle@even's password: tsport01.dbf 100% 20MB 20.0MB/s 00:00 SQL> LEO2库的进行导入[oracle@even ~]$ sqlplus sys/oracle@LEO2 as sysdbaSQL> create user tsport identified by tsport; User created.SQL> grant connect,resource to tsport; Grant succeeded.SQL> !imp userid=\'/ as sysdba\' file=/home/oracle/exp_tsport.dmp fromuser=tsport touser=tsport transport_tablespace=y tablespaces=tsport datafiles=/home/oracle/oracle/oradata/LEO2/tsport01.dbfImport: Release 10.2.0.1.0 - Production on Sat Jan 25 18:21:39 2014Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining optionsExport file created by EXPORT:V10.02.01 via conventional pathAbout to import transportable tablespace(s) metadata...import done in US7ASCII character set and AL16UTF16 NCHAR character set. importing TSPORT's objects into TSPORT. . importing table "T1"Import terminated successfully without warnings.SQL> col tablespace_name for a15SQL> col segment_name for a15SQL> col segment_type for a15SQL> select tablespace_name,segment_name,segment_type from dba_segments where tablespace_name='TSPORT';TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE--------------- --------------- ---------------TSPORT T1 TABLETSPORT IDX_T1 INDEXSQL> conn tsport/tsport@LEO2Connected.SQL> select * from t1; A---------- 100SQL> conn sys/oracle@LEO2 as sysdbaConnected.SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSPORT';TABLESPACE_NAME STATUS--------------- ---------TSPORT READ ONLYSQL> alter tablespace tsport read write; Tablespace altered.SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSPORT';TABLESPACE_NAME STATUS--------------- ---------TSPORT ONLINESQL> conn sys/oracle@LEO1 as sysdbaConnected.SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSPORT';TABLESPACE_NAME STATUS--------------- ---------TSPORT READ ONLYSQL> alter tablespace tsport read write;Tablespace altered.SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSPORT';TABLESPACE_NAME STATUS--------------- ---------TSPORT ONLINESQL> --EOF--二 创建分区表和分区索引
[oracle@odd admin]$ sqlplus sys/oracle@LEO1 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 25 18:34:18 2014Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> alter system set db_16k_cache_size=80M;System altered.SQL> show parameter db_16k_cache_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_16k_cache_size big integer 80MSQL> set linesize 200SQL> col file_name for a100SQL> select file_name from dba_data_files where file_id=1;FILE_NAME----------------------------------------------------------------------------------------------------/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/system01.dbfSQL> create tablespace part1 datafile '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/part1_01.dbf' size 50M extent management local blocksize 16k; Tablespace created.SQL> create tablespace part2 datafile '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/part2_01.dbf' size 50M extent management local blocksize 16k; Tablespace created.SQL> create tablespace part3 datafile '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/part3_01.dbf' size 50M extent management local blocksize 16k; Tablespace created.SQL> create tablespace part4 datafile '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/part4_01.dbf' size 50M extent management local blocksize 16k; Tablespace created.SQL> select * from v$tablespace where name like 'PART%'; TS# NAME INC BIG FLA ENC---------- ------------------------------ --- --- --- --- 6 PART1 YES NO YES 7 PART2 YES NO YES 8 PART3 YES NO YES 9 PART4 YES NO YESSQL> conn tsport/tsport@LEO1Connected.SQL> drop table t2 purge;drop table t2 purge *ERROR at line 1:ORA-00942: table or view does not existSQL> create table t2 (itemid number(10),name varchar2(10),itemdate date);Table created.SQL> create index idx_t2 on t2(itemid);Index created.SQL> insert into t2 values (1,'apple1',to_date('2000-02-01','yyyy-mm-dd'));1 row created.SQL> insert into t2 values (2,'apple2',to_date('2000-03-01','yyyy-mm-dd'));1 row created.SQL> insert into t2 values (3,'apple3',to_date('2002-04-01','yyyy-mm-dd'));1 row created.SQL> insert into t2 values (4,'apple4',to_date('2002-05-01','yyyy-mm-dd'));1 row created.SQL> insert into t2 values (5,'apple5',to_date('2002-06-01','yyyy-mm-dd'));1 row created.SQL> insert into t2 values (6,'apple6',to_date('2010-07-01','yyyy-mm-dd'));1 row created.SQL> insert into t2 values (7,'apple7',to_date('2010-08-01','yyyy-mm-dd'));1 row created.SQL> insert into t2 values (8,'apple8',to_date('2012-09-01','yyyy-mm-dd'));1 row created.SQL> insert into t2 values (9,'apple9',to_date('2012-10-01','yyyy-mm-dd'));1 row created.SQL> insert into t2 values (10,'apple10',to_date('2013-11-01','yyyy-mm-dd'));1 row created.SQL> commit;Commit complete.SQL> select * from t2; ITEMID NAME ITEMDATE---------- ---------- --------- 1 apple1 01-FEB-00 2 apple2 01-MAR-00 3 apple3 01-APR-02 4 apple4 01-MAY-02 5 apple5 01-JUN-02 6 apple6 01-JUL-10 7 apple7 01-AUG-10 8 apple8 01-SEP-12 9 apple9 01-OCT-12 10 apple10 01-NOV-1310 rows selected.SQL> CREATE TABLE t2_part PARTITION BY RANGE (itemdate) ( PARTITION p1 VALUES LESS THAN (to_date('2002-01-01','yyyy-mm-dd')) TABLESPACE part1, PARTITION p2 VALUES LESS THAN (to_date('2010-01-01','yyyy-mm-dd')) TABLESPACE part2, PARTITION p3 VALUES LESS THAN (to_date('2012-01-01','yyyy-mm-dd')) TABLESPACE part3, PARTITION p4 VALUES LESS THAN (to_date('2013-01-01','yyyy-mm-dd')) TABLESPACE part4, PARTITION other VALUES LESS THAN (maxvalue) TABLESPACE part4) as select * from tsport.t2; 2 3 4 5 6 7 8 9 10 11 12 Table created.SQL> select * from t2_part partition (p1); ITEMID NAME ITEMDATE---------- ---------- --------- 1 apple1 01-FEB-00 2 apple2 01-MAR-00SQL> create unique index idx_t2_part on t2_part (name,itemid)global partition by hash (name) partitions 4 tablespace USERS parallel 4;Index created.SQL> select index_name,index_type,table_name from user_indexes where table_name='T2_PART';INDEX_NAME INDEX_TYPE TABLE_NAME------------------------------ --------------------------- ------------------------------IDX_T2_PART NORMAL T2_PARTSQL> alter table t2_part truncate partition p1 update global indexes;Table truncated.SQL> select * from t2_part partition (p1);no rows selectedSQL> select index_name,status,partitioned from user_indexes where table_name='T2_PART';INDEX_NAME STATUS PAR------------------------------ -------- ---IDX_T2_PART N/A YESSQL> --EOF--三 FGA细粒度审计
SQL> conn leo1/leo1@LEO1
Connected.SQL> create table t (x number(10),y varchar2(20)); Table created.SQL> conn sys/oracle@LEO1 as sysdbaConnected.SQL> begindbms_fga.add_policy (object_schema => 'leo1', --审计谁object_name => 't', --审计谁的表policy_name => 'audit_t', --审计策略的名字audit_condition => 'x >= 100', --触发审计的条件 x>=100audit_column => 'x', --审计表中的哪个列‘x,y’enable => TRUE, --审计立刻生效statement_types => 'INSERT,UPDATE,DELETE,SELECT'); --触发审计的语句对这些语句都启动审计end;/ 2 3 4 5 6 7 8 9 10 11 12 PL/SQL procedure successfully completed.SQL> col object_schema for a20SQL> col object_name for a15SQL> col policy_name for a13SQL> col enabled for a3SQL> select object_schema,object_name,policy_name,enabled from dba_audit_policies;OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENA-------------------- --------------- ------------- ---LEO1 T AUDIT_T YESSQL> conn leo1/leo1@LEO1Connected.SQL> insert into t values (10,'first');1 row created.SQL> insert into t values (100,'leo');1 row created.SQL> insert into t values (200,'leonarding'); 1 row created.SQL> insert into t values (300,'andy'); 1 row created.SQL> insert into t values (400,'anlan'); 1 row created.SQL> insert into t values (500,'tigerfish');1 row created.SQL> insert into t values (600,'666666666');1 row created.SQL> select * from t; X Y---------- -------------------- 10 first 100 leo 200 leonarding 300 andy 400 anlan 500 tigerfish 600 6666666667 rows selected.SQL> commit;Commit complete.SQL> set linesize 2000SQL> select OBJ$SCHEMA,OBJ$NAME,POLICYNAME,LSQLTEXT from SYS.FGA_LOG$;OBJ$SCHEMA OBJ$NAME POLICYNAME LSQLTEXT------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ --------------------------------------------------------------------------------LEO1 T AUDIT_T insert into t values (100,'leo')LEO1 T AUDIT_T insert into t values (200,'leonarding')LEO1 T AUDIT_T insert into t values (300,'andy')LEO1 T AUDIT_T insert into t values (400,'anlan')LEO1 T AUDIT_T insert into t values (500,'tigerfish')LEO1 T AUDIT_T insert into t values (600,'666666666')LEO1 T AUDIT_T select * from t7 rows selected.SQL> col sql_text for a35SQL> col object_schema for a15SQL> select object_schema,object_name,policy_name,sql_text from dba_common_audit_trail;OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT--------------- --------------- ------------- -----------------------------------LEO1 T AUDIT_T insert into t values (100,'leo')LEO1 T AUDIT_T insert into t values (200,'leonardi ng')LEO1 T AUDIT_T insert into t values (300,'andy')LEO1 T AUDIT_T insert into t values (400,'anlan')LEO1 T AUDIT_T insert into t values (500,'tigerfis h')LEO1 T AUDIT_T insert into t values (600,'66666666 6')OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT--------------- --------------- ------------- -----------------------------------LEO1 T AUDIT_T select * from t7 rows selected.SQL> --EOF--四 监控索引使用情况
SQL> conn leo1/leo1@LEO1
Connected.SQL> drop table t4;drop table t4 *ERROR at line 1:ORA-00942: table or view does not existSQL> create table t4 as select * from dba_objects;Table created.SQL> create index idx_t4 on t4(object_id);Index created.SQL> alter index leo1.idx_t4 monitoring usage; Index altered.SQL> select object_name from t4 where object_id=5000;OBJECT_NAME---------------ALL_MVIEW_REFRESH_TIMESSQL> set linesize 400SQL> col index_name for a10SQL> col table_name for a10SQL> col start_monitoring for a20SQL> col end_monitoring for a20SQL> select * from v$object_usage;INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING---------- ---------- --- --- -------------------- --------------------IDX_T4 T4 YES YES 01/25/2014 21:18:13SQL> alter index leo1.idx_t4 nomonitoring usage; Index altered.SQL> select * from v$object_usage;INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING---------- ---------- --- --- -------------------- --------------------IDX_T4 T4 NO YES 01/25/2014 21:18:13 01/25/2014 21:18:52SQL> --EOF--五 创建含特殊字段类型的表
SQL> conn leo1/leo1@LEO1
Connected.SQL> create table leonarding_text(text1 varchar2(10),text2 varchar2(10),text3 date,text4 varchar2(50));Table created.SQL> insert into leonarding_text values ('leo1','name',sysdate,'Leonarding');1 row created.SQL> insert into leonarding_text values ('leo2','name',sysdate,'LeonardingLeonarding');1 row created.SQL> insert into leonarding_text values ('leo3','name',sysdate,'LeonardingLeonardingLeonarding');1 row created.SQL> insert into leonarding_text values ('leo4','name',sysdate,'LeonardingLeonardingLeonardingLeonarding');1 row created.SQL> commit;Commit complete.SQL> select * from leonarding_text;TEXT1 TEXT2 TEXT3 TEXT4---------- ---------- --------- --------------------------------------------------leo1 name 25-JAN-14 Leonardingleo2 name 25-JAN-14 LeonardingLeonardingleo3 name 25-JAN-14 LeonardingLeonardingLeonardingleo4 name 25-JAN-14 LeonardingLeonardingLeonardingLeonardingSQL> create table leo1.leonarding_r (text rowid,insert_time timestamp with local time zone) tablespace users; Table created.SQL> insert into leo1.leonarding_r (text,insert_time) select rowid,current_timestamp from leo1.leonarding_text where length(text4)>=3*10;2 rows created.SQL> commit; Commit complete.SQL> select * from leo1.leonarding_r;TEXT INSERT_TIME------------------ ---------------------------------------------------------------------------AAACgmAAEAAAADnAAC 25-JAN-14 09.22.09.228392 PMAAACgmAAEAAAADnAAD 25-JAN-14 09.22.09.228392 PMSQL> drop table leo1.leonarding_r;Table dropped.SQL> --EOF--六 Flashback闪回技术
--闪回查询
SQL> create table t5 (x int);Table created.SQL> insert into t5 values(1);1 row created.SQL> insert into t5 values(2);1 row created.SQL> insert into t5 values(3);1 row created.SQL> commit;Commit complete.SQL> select * from t5; X---------- 1 2 3SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';Session altered.SQL> col date1 new_val savedateSQL> select sysdate date1 from dual;DATE1-------------------2014-01-25 21:38:16SQL> col scn1 new_val savescnSQL> select dbms_flashback.get_system_change_number scn1 from dual; SCN1---------- 418911SQL> delete from t5 where x=1;1 row deleted.SQL> commit;Commit complete.SQL> select * from t5; X---------- 2 3SQL> create view v_t5_timestamp as select * from t5 as of timestamp to_timestamp('&savedate','yyyy-mm-dd hh24:mi:ss');old 1: create view v_t5_timestamp as select * from t5 as of timestamp to_timestamp('&savedate','yyyy-mm-dd hh24:mi:ss')new 1: create view v_t5_timestamp as select * from t5 as of timestamp to_timestamp('2014-01-25 21:38:16','yyyy-mm-dd hh24:mi:ss')View created.SQL> create view v_t5_scn as select * from t5 as of scn '&savescn';old 1: create view v_t5_scn as select * from t5 as of scn '&savescn'new 1: create view v_t5_scn as select * from t5 as of scn ' 418911'View created.SQL> select * from v_t5_timestamp; X---------- 1 2 3SQL> select * from v_t5_scn; X---------- 1 2 3SQL> drop view v_t5_timestamp;View dropped.SQL> drop view v_t5_scn;View dropped.--闪回表SQL> purge recyclebin; Recyclebin purged.SQL> create table t6 (x int); Table created.SQL> insert into t6 values (1); 1 row created.SQL> commit;Commit complete.SQL> select * from t6; X---------- 1SQL> drop table t6; Table dropped.SQL> create table t6 (x int); Table created.SQL> insert into t6 values (1); 1 row created.SQL> insert into t6 values (2);1 row created.SQL> commit; Commit complete.SQL> select * from t6; X---------- 1 2SQL> drop table t6; Table dropped.SQL> show recyclebinORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------T6 BIN$8MxdRZ315cHgQKjAZwohEw==$0 TABLE 2014-01-25:21:40:39T6 BIN$8MxdRZ305cHgQKjAZwohEw==$0 TABLE 2014-01-25:21:40:18SQL> select * from "BIN$8MxdRZ315cHgQKjAZwohEw==$0"; X---------- 1 2SQL> select * from "BIN$8MxdRZ305cHgQKjAZwohEw==$0"; X---------- 1--恢复有1条记录的t6表SQL> flashback table "BIN$8MxdRZ305cHgQKjAZwohEw==$0" to before drop rename to t6_new;Flashback complete.SQL> select * from t6_new; X---------- 1SQL> --EOF--转载地址：http://grvai.baihongyu.com/