Oracle DBA Common Statement 5____oracle

###### Analysis log file LOGMNR ############## 1) in Init.ora, set utl_file_dir parameter 2) to restart the Oracle 3 Create directory file desc DBMS_LOGMNR

_d;

Dbms_logmnr_d.build;

4 Add log files add/remove log file Dhms_logmnr.add_logfile dbms_logmnr.removefile 5) Start LOGMNR DBMS_LOGMNR.START_LOGMNR

6) Analysis of the content query v$logmnr_content--sqlredo/sqlundo Practice: desc dbms_logmnr_d;

/* Do some operations on the datasheet, prepare for recovery operation/Update table set qty=10 where stor_id=6380;

Delete table where stor_id=7066; /***********************************/utl_file_dir Path Execute dbms_logmnr_d.build (' Foxdict.ora ', ' $ORACLE _home/

Oracle/admin/fox/cdump ');

Execute Dbms_logmnr.add_logfile (' $ORACLE _home/oracle/ora_log_file6.log ', dbms_logmnr.newfile);

Execute DBMS_LOGMNR.START_LOGMNR (dictfilename=> ' $ORACLE _home/oracle/admin/fox/cdump/foxdict.ora ');

######### tablespace ############## select * Form V$tablespace;

SELECT * from V$datafile;

/* table space and data file correspondence * * Select T1.name,t2.name from V$tablespace t1,v$datafile T2 where t1.ts#=t2.ts#; Alter TABLESPACE users ADD datafile ' path ' size 10M;

SELECT * from Dba_rollback_segs;

/* Limit the user's limit on the use of a table space */ALTER USER user_name quota 10m on tablespace_name; Create tablespace xxx [datafile ' path_name/datafile_name '] [size XXX] [extent management local/dictionary] [default Stora

GE (XXX)]; Exmple:create tablespace userdata datafile ' $ORACLE _home/oradata/userdata01.dbf ' size 100M autoextend on NEXT 5M MAXSIZE

200M; Create tablespace userdata datafile ' $ORACLE _home/oradata/userdata01.dbf ' size 100M extent management Dictionary default

Storage (initial 100k next 100k pctincrease) offline; After/*9i, Oracle recommends using local management instead of dictionary management, because local use of bitmap management table space does not generate a voluntary contention for system tablespace; * Create Tablespace UserData

DataFile ' $ORACLE _home/oradata/userdata01.dbf ' size 100M extent management local uniform size 1m; Create tablespace userdata datafile ' $ORACLE _home/oradata/userdata01.dbf ' size 100M extent management Local Autoallocate

; /* When creating a tablespace, set the segment space management mode in the table space, which is used for automatic management/create tablespace userdata datafile ' $ORACLE_HOME/ORADATA/USERDATA01.DBF ' size 100M extent management Local uniform size 1m segment spaces management auto;

Alter tablespace UserData mininum extent 10;

Alter tablespace userdata default storage (initial 1m next 1m pctincrease 20); /*undo tablespace (cannot be used under Dictionary admin mode)/create undo tablespace undo1 DataFile ' $ORACLE _home/oradata/undo101.dbf ' size 40M ext

ENT management Local;

Show parameter undo; /*temporary tablespace*/Create temporary tablespace userdata tempfile ' $ORACLE _home/oradata/undo101.dbf ' size 10m exten

T management local;

/* Set the database default temp table space * * ALTER DB default temporary tablespace tablespace_name;

/* System/Temporary/online undo tablespace cannot be offline/online by offline*/alter Tablespace Tablespace_name;

Alter Tablespace Tablespace_name Read only; /* Rename user table space/alter tablespace tablespace_name rename datafile ' $ORACLE _home/oradata/undo101.dbf ' to ' $ORACLE _home/orad

ATA/UNDO102.DBF '; /* Rename system tablespace, but must shutdown the database before renaming and restart to mount state/ALTER DATABASE rename file ' $ORACLE _Home/oradata/system01.dbf ' to ' $ORACLE _home/oradata/system02.dbf '; Drop tablespace UserData including contents and datafiles;---drop tablespce/*resize tablespace,autoextend datafile Space

* ALTER DATABASE datafile ' $ORACLE _home/oradata/undo102.dbf ' autoextend on next 10m maxsize 500M;

/*resize datafile*/ALTER DATABASE datafile ' $ORACLE _home/oradata/undo102.dbf ' resize 50m;

/* Extended space to tablespace/alter tablespace userdata add datafile ' $ORACLE _home/oradata/undo102.dbf ' size 10m;

/* Set table space to OMF state/alter system set db_create_file_dest= ' $ORACLE _home/oradata ';

Create tablespace userdata---Use OMF status to create tablespace;

Drop tablespace userdata---user OMF status to drop tablespace;

SELECT * from Dba_tablespace/v$tablespace/dba_data_files;

/* Move one partition of the table to another table space/ALTER TABLE TABLE_NAME moves partition partition_name tablespace tablespace_name; ###### ORACLE storage structure and relationships #########/* Manually allocate the partition (extend) Size of the table space section * * ALTER TABLE kong.test12 ALLOCA Te extent (SIZe 1m datafile ' $ORACLE _home/oradata/undo102.dbf '); ALTER TABLE kong.test12 deallocate unused;

---to release a partition that is not used in the table show parameter db; alter system set db_8k_cache_size=10m;

---Configure the memory space block parameter of 8k block select * from Dba_extents/dba_segments/data_tablespace;

SELECT * from Dba_free_space/dba_data_file/data_tablespace;

/* The number of bytes occupied by the data object */select sum (bytes) from dba_extents where onwer= ' kong ' and segment_name = ' table_name ';

############ undo Data ################ Show parameter undo;

Alter tablespace users offline normal;

Alter TABLESPACE users offline immediate;

Recover datafile ' $ORACLE _home/oradata/undo102.dbf ';

Alter tablespace users online;

SELECT * from Dba_rollback_segs;

alter system set UNDO_TABLESPACE=UNDOTBS1;

/* Ignore ROLLBACK SEGMENT ERROR hint * * ALTER system set undo_suppress_errors=true; /* In the Automatic management mode, will not really establish RBS1;

In the manual management mode can be established, and is a private rollback section * * Create rollback segment RBS1 tablespace Undotbs;

Desc Dbms_flashback; /* After submitting the modified data, 9i provides the old data back-flash operation, the modified data read to the user only, but this part of the data will not be restored in the table, but a map of the old data/exeCute dbms_flashback.enable_at_time (' 26-jan-04:12:17:00 pm ');

Execute dbms_flashback.disable;

/* Rollback Segment statistical information/select End_time,begin_time,undoblks from V$undostat; /*undo table space Size calculation formula: undospace=[UR * (UPS * DBS)] + (DBS *) Ur:undo_retention Retention time (sec) UPS: Rollback data block per second DBS: System EX TENT and FILE SIZE (i.e. db_block_size)/select * FROM dba_rollback_segs/v$rollname/v$rollstat/v$undostat/v$session/v$

Transaction

Show parameter transactions;

Show parameter rollback;

/* In the manual management mode, the establishment of a common rollback section * * Create public rollback segment PRBS1 tablespace Undotbs; Alter rollback segment RBS1 online;----in manual management mode/* in manual management mode, Initsid.ora specifies Undo_management=manual, rollback_segment= (' Rbs1 ', ' RBS2 ',...) , transactions=100, transactions_per_rollback_segment=10 and then shutdown immediate, startup Pfile=..../???. Ora *