Oracle Database Management Labs
A Transport table space
Two create partition table and partition index
Fine-grained audit of three FGA
Four Monitoring index usage
Five create a table with a special field type
Six flashback flash back technology
A transport tablespace that migrates the Tstrans table space of the Ocmdb library to the OCMDB02 library
Occasion: 1. For OLAP data Warehouse applications 2. Data migration is faster than EXP/IMP, migrating metadata while migrating data
Principle: Only the metadata of the table space (i.e. structure information) is exported, the target database is imported, the table space is set to read-only, the corresponding data file is copied to the target directory, the imported table space is mounted, and the table space is set to read and write.
Official document: Administrator ' s guide-> 8 managing Tablespace, Transport tablespace between databases
CREATE DIRECTORY, SQL Reference
Clean up the environment
Drop user Tstrans cascade;
Drop tablespace Tstrans including contents and datafiles;
1. Initializing the experimental environment
Sqlplus Sys/[email protected] as Sysdba
Create tablespace tstrans datafile '/u01/oracle/oradata/ocmdb/tsport01.dbf ' size 20m autoextend off;
Create user Tstrans identified by Tstrans default Tablespace Tstrans;
Grant Connect,resource to Tstrans;
Conn Tstrans/tstrans
CREATE TABLE T1 (a int) tablespace Tstrans;
INSERT into T1 values (100);
Commit
CREATE index idx_t1 on T1 (a) tablespace Tstrans;
Select Index_name,table_name,tablespace_name from user_indexes where table_name= ' T1 ';
Conn/as SYSDBA
Select Tablespace_name,segment_name,segment_type from dba_segments where tablespace_name= ' Tstrans '; See what objects are in the Tstrans table space
2. Check if the Tstrans table space violates the constraint condition
exec dbms_tts.transport_set_check (' Tstrans ', true);
For example, the table in the A-table space, the index in the B-table space, if only a, it will violate the self-contained constraints, the index on the table will be invalidated, the transmission is incomplete, the solution: simultaneous transfer of A and B two table space.
SELECT * from Transport_set_violations; View the list of violated constraints, not recorded as normal
3. Set Tstrans to read-only table space, that is, all data files are set to read-only state.
Alter Tablespace Tstrans Read only;
4. Export table space Metadata as structure information using the Transport_tablespace=y parameter of exp or EXPDP
Exp-help Open the Help document to see its options description
Exp userid=\ '/as Sysdba\ ' Transport_tablespace=y Tablespaces=tstrans file=/home/oracle/exp_tstrans.dmp
Use the SYS user to export only the metadata (structure information) of the Tstrans table space, rather than the real data, so the capacity is smaller
SCP exp_tstrans.dmp ocm02:/home/oracle Transfer to the second machine
Data pump export Mode select Do
"Expdp-help
Create directory Dir_home as '/home/oracle ';
Grant Read,write on directory dir_home to public;
EXPDP system/oracle directory=dir_home dumpfile=expdp_tstrans.dmp Transport_tablespaces=tstrans Transport_full_ Check=y
】
5. Copy data files (real data in tablespace) only the data files are read-only and can be copied without stopping the library
SCP tstrans01.dbf ocm02:/u01/oracle/oradata/ocmdb02/
Transport table Space Import for 6.imp or IMPDP
Preparations for the LEO2 library
Create user Tstrans identified by Tstrans;
Grant Connect,resource to Tstrans;
Insert Table space Metadata + data files by default by using the Imp import method
Imp userid=\ '/as Sysdba\ ' file=/home/oracle/exp_tstrans.dmp Fromuser=tstrans touser=tstrans transport_tablespace=y Tablespaces=tstrans datafiles=/u01/oracle/oradata/ocm02/tstrans01.dbf
"Select with IMPDP import Method"
Create directory Dir_home as '/home/oracle ';
Grant Read,write on directory dir_home to public;
IMPDP system/oracle directory=dir_home dumpfile=expdp_tstrans.dmp remap_schema= (Tstrans:tstrans) object is loaded from one schema to another schema
TRANSPORT_DATAFILES=/U01/ORACLE/ORADATA/OCM02/TSTRANS01.DBF which data file to import
Check if the table space is imported successfully
Col Tablespace_name for A15
Col segment_name for A15
Col Segment_type for A15
Select Tablespace_name,segment_name,segment_type from dba_segments where tablespace_name= ' Tstrans ';
Conn Tstrans/tstrans
SELECT * from T1;
7. Adjust the Ocmdb instance and the OCMDB02 instance tablespace to read-write status
Sqlplus Sys/[email protected] as Sysdba
Conn/as SYSDBA
Select Tablespace_name,status from Dba_tablespaces;
Alter Tablespace Tstrans Read write;
Select Tablespace_name,status from Dba_tablespaces;
Sqlplus Sys/[email protected] as Sysdba
Conn/as SYSDBA
Select Tablespace_name,status from Dba_tablespaces;
Alter Tablespace Tstrans Read write;
Select Tablespace_name,status from Dba_tablespaces;
Two create partition table and partition index
Official documents:
Administrator ' s guide–> managing partitioned Tables and Indexs
Data Warehousing Guide, 5 partitioning in Data warehouses
Occasions: large data volume, requiring a small search range, high efficiency
Pros: High DBA management flexibility, partition-based deletion, insert
Cons: Cross-partition retrieval is inefficient, but you can create a global index to improve performance
Global index globally: Default delete partition, global index is invalidated, one partition table has only one global index
Native Index Local: One partition, one index, several partitions with several indexes
Requirements: We create a partitioned table with 4 partitions, each using a single table space
Using non-standard blocks, block size 16k
1. Setting non-standard blocks
Ocm01
alter system set db_16k_cache_size=80m; Set non-standard block 16K buffers for non-standard blocks
Show Parameter Db_16k_cache_size
Function: Used to reduce physical I/O read and write times, originally read 100 times can complete data, now read 50 times to complete
Create user ocm01 identified by OCM01;
Grant DBA to OCM01;
2. Create 4 tablespace, one partition for one table space
Conn OCM01/OCM02
drop table T2_part;
Drop index Idx_t2_part;
Drop tablespace part1 including contents and datafiles;
Drop tablespace part2 including contents and datafiles;
Drop tablespace part3 including contents and datafiles;
Drop tablespace part4 including contents and datafiles;
Create tablespace part1 datafile '/u01/oracle/oradata/ocmdb/disk1/part1_01.dbf ' size 50M
Extent Management Local
BlockSize 16k;
Create tablespace part2 datafile '/u01/oracle/oradata/ocmdb/disk2/part2_01.dbf ' size 50M
Extent Management Local
BlockSize 16k;
Create tablespace part3 datafile '/u01/oracle/oradata/ocmdb/disk3/part3_01.dbf ' size 50M
Extent Management Local
BlockSize 16k;
Create tablespace part4 datafile '/u01/oracle/oradata/ocmdb/disk4/part4_01.dbf ' size 50M
Extent Management Local
BlockSize 16k;
SELECT * from V$tablespace;
2. Constructing partitioned table data
Conn Tstrans/tstrans
drop table T2 Purge;
CREATE TABLE t2 (Itemid number), name VARCHAR2 (ten), itemdate date);
Create index idx_t2 on T2 (itemid);
INSERT into T2 values (1, ' Apple1 ', to_date (' 2000-02-01 ', ' yyyy-mm-dd '));
INSERT into T2 values (2, ' Apple2 ', to_date (' 2000-03-01 ', ' yyyy-mm-dd '));
INSERT into T2 values (3, ' Apple3 ', to_date (' 2002-04-01 ', ' yyyy-mm-dd '));
INSERT into T2 values (4, ' Apple4 ', to_date (' 2002-05-01 ', ' yyyy-mm-dd '));
INSERT into T2 values (5, ' Apple5 ', to_date (' 2002-06-01 ', ' yyyy-mm-dd '));
INSERT into T2 values (6, ' Apple6 ', to_date (' 2010-07-01 ', ' yyyy-mm-dd '));
INSERT into T2 values (7, ' Apple7 ', to_date (' 2010-08-01 ', ' yyyy-mm-dd '));
INSERT into T2 values (8, ' Apple8 ', to_date (' 2012-09-01 ', ' yyyy-mm-dd '));
INSERT into T2 values (9, ' Apple9 ', to_date (' 2012-10-01 ', ' yyyy-mm-dd '));
INSERT into T2 values (' Apple10 ', to_date (' 2013-11-01 ', ' yyyy-mm-dd '));
Commit
select * from T2; Insert 10 records and show them
Create a partitioned table under the 3.OCM01 user
Conn OCM01/OCM01
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 Tstrans.t2;
viewing partitioned table Data
Alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';
SELECT * from T2_part;
View a section
SELECT * FROM T2_part partition (P1);
4. Create a hash global partition index
Hash partition index: After evenly scattered storage data, take a column for hash, evenly distributed on 4 partitions, each partition in the different table space record number is similar, concurrent read concurrent write
Official document: Create TABLE and create INDEX, SQL Reference
Conn OCM01/OCM01
Note: The Global Partition index (hash broken by the name column) is evenly divided into 4 parts, each saved in the Leoindex table space.
Create unique index Idx_t2_part on T2_part (name,itemid) global partition by hash (name) partitions 4 tablespace Leoindex p Arallel 4;
Select Index_name,index_type,table_name from user_indexes where table_name= ' T2_part ';
Truncate a partition to ensure that the global index is available
1. By default, add, remove Partitions > Global index invalidation
2.truncate operation affects global index delete operation does not affect global index
ALTER TABLE T2_part TRUNCATE partition p1 update global indexes;
SELECT * FROM T2_part partition (P1);
"Update global indexes this keyword can be rebuilt after operation."
Check whether the global index is valid
Select index_name,status,partitioned from dba_indexes where table_name= ' T2_part ';
Fine-grained audit of three FGA
Occasion: can find out who when to use what statement to delete the table
Oracle Fine-grained auditing is a branch of security that traces the history of the database, ensuring that all operations are safe, reliable, and controlled, and that FGA is a package-based implementation
Official document: Security Guide, configuring and administering Auditing, fine-grained Auditing
PL/SQL Packages and Types reference–> DBMS_FGA
Requirements: Audit the table using FGA technology
1. Clean up the environment
Only administrators can delete audits
Dbms_fga. Drop_policy (
Object_schema VARCHAR2, user name (if empty default current login user)
object_name VARCHAR2, name of object to be audited (table name)
Policy_name VARCHAR2); Audit name (must be a unique value)
Execute DBMS_FGA. Drop_policy (object_schema=> ' ocm01 ',object_name=> ' t ',policy_name=> ' audit_t ');
Conn OCM01/OCM01
drop table T Purge; Delete the table to be audited
2. Create the T-table to be audited
Conn OCM01/OCM01
CREATE table T (x number (ten), Y Varchar2 (20)); CREATE table to audit t
3. Create an Audit policy
Conn/as SYSDBA
Begin
Dbms_fga.add_policy (
Object_schema = ' ocm01 ', audit who
object_name = ' t ', Audit who's table
Policy_name = ' audit_t ', the name of the audit strategy
Audit_condition = ' x >= 100 ', triggering the condition of the audit x>=100
Audit_column = ' x ', which column in the Audit table ' x, y '
Enable = TRUE, Audit takes effect immediately
Statement_types = ' insert,update,delete,select '); The statement that triggers the audit initiates an audit of these statements
End
/
4. Query confirms FGA policy is in effect
Col Object_schema for A20
Col object_name for A15
Col Policy_name for A13
Col enabled for A3
Select object_schema,object_name,policy_name,enabled from Dba_audit_policies;
5. Insert test records to meet the conditions of the triggering audit
Conn Leo1/leo1
INSERT into T values (' first ');
INSERT INTO T values ("DFS");
INSERT into t values (+, ' DSF ');
INSERT into T values (' SDF ');
INSERT INTO T values ("SDGDG");
INSERT INTO T values ("SDG");
INSERT into t values (' Sdgsdgs ');
select * from T; View the contents of an audit table
6. Review the audit results by default and place the audit results in the sys.fga_log$ base table
Note: The audit will check the four statements of Insert,update,delete,select, and record what statements are executed by which table who operates
Select Obj$schema,obj$name,policyname,lsqltext from SYS. fga_log$;
Set Lines 200
Col Sql_text for A35
Col Object_schema for A15
Select Object_schema,object_name,policy_name,sql_text from Dba_common_audit_trail;
Show All audit results
Four Monitoring index usage
Occasions: monitoring tables in the useless index delete
Official document: Administrator ' s Guide, Managing Indexes, monitoring Index Usage
Conn OCM01/OCM01
drop table T4;
CREATE TABLE T4 as select * from Dba_objects;
CREATE index idx_t4 on t4 (object_id);
Enable monitoring of the IDX_T4 index under LEO1
ALTER index IDX_T4 monitoring usage;
Stop monitoring of IDX_T4 indexes under LEO1
Alter index IDX_T4 nomonitoring usage;
SELECT * from T4 where object_id=5000;
View V$object_usage view for index usage
Set Linesize 400 Setting environment
Col index_name for A10
Col table_name for A10
Col start_monitoring for A20
Col end_monitoring for A20
SELECT * from V$object_usage;
Description: The Monitoring field Yes indicates that the index has been monitored and no is not monitored
Used field Yes indicates that the index is already in use and no is not used
Start_monitoring and end_monitoring indicate the last monitoring interval
Five create a table with a special field type
Create a table with rowID and timestamp type fields and insert data
Official document: SQL Reference, 2 Basic Elements of Oracle SQL, datatypes, search "ROWID" and "TIMESTAMP" with LOCAL time ZONE Da Tatype
1. Create a leonarding_r table and initialize the data
Conn OCM01/OCM01
Constructing the Data Environment
CREATE TABLE Ocm01_text
(
Text1 VARCHAR2 (10),
Text2 VARCHAR2 (10),
Text3 date,
Text4 VARCHAR2 (50)
);
Insert records containing ' Hugh ' keywords
INSERT into ocm01_text values (' Hugh ', ' name ', Sysdate, ' Hugh ');
INSERT into Ocm01_text values (' hugh2 ', ' name ', Sysdate, ' Hugh ');
INSERT into Ocm01_text values (' Hugh3 ', ' name ', Sysdate, ' Hugh ');
INSERT into Ocm01_text values (' Hugh4 ', ' name ', Sysdate, ' Hugh ');
Commit
SELECT * from Ocm01_text;
CREATE table Ocm01_r (text rowid,insert_time timestamp with local time zone) tablespace users;
rowID field type
Timestamp with local time zone timestamp and native time zone field type
2. Insert a record into the Leonarding_r table
Retrieve records in the Leonarding_text table, and if 1 records contain 3 or more "leonarding" keywords, insert this record rowid and timestamp into the Leonarding_r table
Insert into Ocm01_r (text,insert_time) select Rowid,current_timestamp from Ocm01_text;
Note: The Current_timestamp function: Returns the "date" and "time" converted according to the time zone, the seconds returned is the system's
Sysdate function: Returns the date and time of the operating system
Length string lengths function: Take field length
Commit
SELECT * from Ocm01_r;
drop table Ocm01_r;
Six flashback flash back technology
Scenario: How to recover data when mistakenly deleted
Official document: Application Developer ' s guide-fundamentals, developing Flashback application, Using Flashback Query (s Elect ... As of)
1.Flashback Query Flashback data
Principle: The flashback query uses the pre-image stored in the Undo table space
Tectonic environment
drop table T5 Purge;
CREATE table t5 (x int);
INSERT into T5 values (1);
INSERT into T5 values (2);
INSERT into T5 values (3);
Commit
SELECT * FROM T5;
2. Query the current time and SCN number for constructing a subsequent flashback query
Alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';
Querying the current system time
Select Sysdate from dual;
Sysdate
-------------------
2014-01-14 21:27:23
Querying the current system SCN number
Select Dbms_flashback.get_system_change_number from dual; 331299
3. Delete data
Delete from T5 where x=1;
Commit
SELECT * FROM T5;
4. Two ways to create a view construct a flashback query delete previous data
1) First method: Use timestamp to construct Flashback query view
CREATE VIEW V_t5_timestamp as SELECT * FROM T5
As of timestamp to_timestamp (' 2014-01-14 21:27:23 ', ' yyyy-mm-dd hh24:mi:ss ');
2) Second method: Use SCN constructs flashback query view
CREATE VIEW V_T5_SCN as SELECT * from T5 as of SCN 331299;
Note: SCN is more accurate than timestamp
Query view Flash back content
SELECT * from V_t5_timestamp;
SELECT * from V_T5_SCN;
Drop View V_t5_timestamp;
Drop View V_T5_SCN;
In this case, both methods of constructing the view have successfully obtained the data of the flashback query.
2. A table is repeatedly deleted, requiring recovery to a specified version of the data
Principle: Emptying the Recycle Bin
Purge RecycleBin;
CREATE table T6 (x int);
INSERT into T6 values (1);
Commit
SELECT * from T6;
drop table T6;
CREATE table T6 (x int);
INSERT into T6 values (1);
INSERT into T6 values (2);
Commit
SELECT * from T6;
drop table T6;
Querying the Recycle Bin data dictionary
Select Object_name,original_name,type from RecycleBin;
Show RecycleBin
Get the two versions of the drop of the T6 table which is the object we need to recover and restore the T6 table with 1 records
SELECT * from "bin$7+8kshbfhopgqkjazruk9w==$0";
SELECT * from "bin$7+8kshbehopgqkjazruk9w==$0";
Flash back to the specified version, flash back and rename simultaneously
Flashback table "Bin$7+8kshbehopgqkjazruk9w==$0" to before drop rename to t6_new;
drop table t6_new;
Summary of self-OCM experiment selection and talk
