<<OCM实验选讲>> 第七课 Oracle数据库管理实验

一 传输表空间

将LEO1库的tsport表空间迁移到LEO2库中
LEO1库的进行导出
[oracle@odd ~]$ sqlplus sys/oracle@LEO1 as sysdba
SQL> 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                     SYSTEM

SQL> 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@LEO1
Connected.
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                  TSPORT

SQL> conn sys/oracle@LEO1 as sysdba
Connected.
SQL> select tablespace_name,segment_name,segment_type from dba_segments where tablespace_name='TSPORT';

TABLESPACE_NAME            SEGMENT_NAME                                     SEGMENT_TYPE
------------------------------ --------------------------------------------------------------------------------- ------------------
TSPORT                   IDX_T1                                         INDEX
TSPORT                   T1                                         TABLE

SQL> exec dbms_tts.transport_set_check('TSPORT',true);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

SQL> alter tablespace tsport read only;

Tablespace altered.

SQL> !exp userid=\'/ as sysdba\' transport_tablespace=y tablespaces=tsport file=/home/oracle/exp_tsport.dmp

Export: Release 10.2.0.1.0 - Production on Sat Jan 25 18:15:03 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About 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 export
Export 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.dmp

SQL> !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 sysdba
SQL> 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.dbf

Import: Release 10.2.0.1.0 - Production on Sat Jan 25 18:21:39 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About 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 a15
SQL> col segment_name for a15
SQL> col segment_type for a15
SQL> select tablespace_name,segment_name,segment_type from dba_segments where tablespace_name='TSPORT';

TABLESPACE_NAME SEGMENT_NAME    SEGMENT_TYPE
--------------- --------------- ---------------
TSPORT        T1        TABLE
TSPORT        IDX_T1        INDEX

SQL> conn tsport/tsport@LEO2
Connected.
SQL> select * from t1;

     A
----------
       100

SQL> conn sys/oracle@LEO2 as sysdba
Connected.
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSPORT';

TABLESPACE_NAME STATUS
--------------- ---------
TSPORT        READ ONLY

SQL> alter tablespace tsport read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces  where tablespace_name='TSPORT';

TABLESPACE_NAME STATUS
--------------- ---------
TSPORT        ONLINE

SQL> conn sys/oracle@LEO1 as sysdba
Connected.
SQL> select tablespace_name,status from dba_tablespaces  where tablespace_name='TSPORT';

TABLESPACE_NAME STATUS
--------------- ---------
TSPORT        READ ONLY

SQL> alter tablespace tsport read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces  where tablespace_name='TSPORT';

TABLESPACE_NAME STATUS
--------------- ---------
TSPORT        ONLINE

SQL>


--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 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system set db_16k_cache_size=80M;

System altered.

SQL> show parameter db_16k_cache_size

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size             big integer 80M
SQL> set linesize 200
SQL> col file_name for a100
SQL> 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.dbf

SQL> 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  YES

SQL> conn tsport/tsport@LEO1
Connected.
SQL> drop table t2 purge;
drop table t2 purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 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-13

10 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-00

SQL> 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_PART

SQL> alter table t2_part truncate partition p1 update global indexes;

Table truncated.

SQL> select * from t2_part partition (p1);

no rows selected

SQL> select index_name,status,partitioned from user_indexes where table_name='T2_PART';

INDEX_NAME               STATUS    PAR
------------------------------ -------- ---
IDX_T2_PART               N/A    YES

SQL>

--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 sysdba
Connected.
SQL> begin

dbms_fga.add_policy (
object_schema      =>  'leo1',           --审计谁
object_name        =>  't',              --审计谁的表
policy_name        =>  'audit_t',        --审计策略的名字
audit_condition    =>  'x >= 100',       --触发审计的条件 x>=100
audit_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 a20
SQL> col object_name for a15
SQL> col policy_name for a13
SQL> col enabled for a3
SQL> select object_schema,object_name,policy_name,enabled from dba_audit_policies;

OBJECT_SCHEMA         OBJECT_NAME     POLICY_NAME   ENA
-------------------- --------------- ------------- ---
LEO1             T             AUDIT_T       YES

SQL> conn leo1/leo1@LEO1
Connected.
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 666666666

7 rows selected.

SQL> commit;

Commit complete.

SQL> set linesize 2000
SQL> 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 t

7 rows selected.

SQL> col sql_text for a35
SQL> col object_schema for a15
SQL> 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 t

7 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 exist


SQL> 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_REFRE
SH_TIMES


SQL> set linesize 400
SQL> col index_name for a10
SQL> col table_name for a10
SQL> col start_monitoring for a20
SQL> col end_monitoring for a20
SQL> 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:13

SQL> 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:52

SQL>

--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 Leonarding
leo2       name       25-JAN-14 LeonardingLeonarding
leo3       name       25-JAN-14 LeonardingLeonardingLeonarding
leo4       name       25-JAN-14 LeonardingLeonardingLeonardingLeonarding

SQL> 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 PM
AAACgmAAEAAAADnAAD 25-JAN-14 09.22.09.228392 PM

SQL> 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
     3

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> col date1 new_val savedate
SQL> select sysdate date1 from dual;

DATE1
-------------------
2014-01-25 21:38:16

SQL> col scn1 new_val savescn
SQL> select dbms_flashback.get_system_change_number scn1 from dual;

      SCN1
----------
    418911

SQL> delete from t5 where x=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from t5;

     X
----------
     2
     3

SQL> 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
     3

SQL> select * from v_t5_scn;

     X
----------
     1
     2
     3

SQL> 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
----------
     1

SQL> 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
     2

SQL> drop table t6;

Table dropped.

SQL> show recyclebin
ORIGINAL NAME     RECYCLEBIN NAME        OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T6         BIN$8MxdRZ315cHgQKjAZwohEw==$0 TABLE         2014-01-25:21:40:39
T6         BIN$8MxdRZ305cHgQKjAZwohEw==$0 TABLE         2014-01-25:21:40:18
SQL> select * from "BIN$8MxdRZ315cHgQKjAZwohEw==$0";

     X
----------
     1
     2

SQL> 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
----------
     1

SQL>

--EOF--