Chapter One: Log Management
1.forcing Log Switches
sql> alter system switch logfile;
2.forcing checkpoints
Sql> alter system checkpoint;
3.adding Online Redo Log groups
sql> ALTER DATABASE Add logfile [Group 4]
Sql> ('/disk3/log4a.rdo ', '/disk4/log4b.rdo ') size 1m;
4.adding Online Redo Log Members
sql> ALTER DATABASE Add logfile Member
Sql> '/disk3/log1b.rdo ' to group 1,
Sql> '/disk4/log2b.rdo ' to group 2;
5.changes the name of the online redo logfile
sql> ALTER DATABASE rename file ' C:/oracle/oradata/oradb/redo01.log '
sql> to ' C:/oracle/oradata/redo01.log ';
6.drop Online Redo Log groups
sql> ALTER DATABASE drop logfile Group 3;
7.drop Online Redo Log Members
sql> ALTER DATABASE drop logfile member ' C:/oracle/oradata/redo01.log ';
8.clearing Online redo log files
Sql> ALTER DATABASE clear [unarchived] logfile ' C:/oracle/log2a.rdo ';
9.using Logminer Analyzing Redo LogFiles
A. In the Init.ora specify Utl_file_dir = "
B. sql> execute Dbms_logmnr_d.build (' Oradb.ora ', ' c:oracleoradblog ');
C. sql> Execute Dbms_logmnr_add_logfile (' C:oracleoradataoradbredo01.log ',
Sql> dbms_logmnr.new);
D. sql> execute Dbms_logmnr.add_logfile (' C:oracleoradataoradbredo02.log ',
Sql> dbms_logmnr.addfile);
E. sql> execute DBMS_LOGMNR.START_LOGMNR
(dictfilename=> ' C:oracleoradblogoradb.ora ');
F. sql> select * FROM V$logmnr_contents (v$logmnr_dictionary,v$logmnr_parameters
Sql> v$logmnr_logs);
G. sql> execute DBMS_LOGMNR.END_LOGMNR;
Chapter II: Table Space Management
1.create tablespaces
sql> Create tablespace tablespace_name datafile ' c:oracleoradatafile1.dbf ' size 100m,
Sql> ' c:oracleoradatafile2.dbf ' size 100m minimum extent 550k [logging/nologging]
sql> default storage (initial 500k next 500k maxextents pctinccease 0)
sql> [Online/offline] [permanent/temporary] [Extent_management_clause]
2.locally managed Tablespace
sql> Create tablespace user_data datafile ' c:oracleoradatauser_data01.dbf '
Sql> Size 500m extent management local uniform size 10m;
3.temporary tablespace
sql> Create temporary tablespace temp tempfile ' c:oracleoradatatemp01.dbf '
Sql> Size 500m extent management local uniform size 10m;
4.change the storage setting
sql> alter tablespace App_Data minimum extent 2m;
sql> alter tablespace app_data default storage (initial 2m next 2m maxextents 999);
5.taking tablespace offline or online
sql> alter tablespace App_Data offline;
sql> alter tablespace App_Data online;
6.read_only tablespace
Sql> alter tablespace App_Data read only|write;
7.droping tablespace
Sql> drop tablespace app_data including contents;
8.enableing automatic extension of data files
Sql> alter tablespace app_data add datafile ' c:oracleoradataapp_data01.dbf ' size 200m
Sql> Autoextend on next 10m maxsize 500m;
9.change the size fo data files manually
sql> ALTER DATABASE datafile ' C:ORACLEORADATAAPP_DATA.DBF ' resize 200m;
10.Moving Data Files:alter tablespace
sql> alter tablespace app_data rename datafile ' c:oracleoradataapp_data.dbf '
sql> to ' c:oracleapp_data.dbf ';
11.moving Data Files:alter Database
sql> ALTER DATABASE rename file ' c:oracleoradataapp_data.dbf '
sql> to ' c:oracleapp_data.dbf ';
Chapter III: Table
1.create a table
Sql> CREATE TABLE table_name (column Datatype,column datatype] ...)
sql> tablespace tablespace_name [pctfree integer] [pctused integer]
sql> [Initrans integer] [maxtrans integer]
sql> Storage (initial 200k next 200k Pctincrease 0 maxextents 50)
sql> [logging|nologging] [Cache|nocache]
2.copy an existing table
sql> CREATE TABLE table_name [logging|nologging] as subquery
3.create temporary table
Sql> Create global temporary table Xay_temp as SELECT * from Xay;
On commit Preserve rows/on commit Delete rows
4.pctfree = (average row size-initial row size) *100/average Row Size
pctused = 100-pctfree-(average row size*100/available data space)
5.change Storage and Block utilization parameter
Sql> ALTER TABLE table_name PCTFREE=30 PCTUSED=50 storage (next 500k
Sql> minextents 2 maxextents 100);
6.manually Allocating extents
Sql> ALTER TABLE TABLE_NAME ALLOCATE extent (size 500k datafile ' c:/oracle/data.dbf ');
7.move tablespace
Sql> ALTER TABLE employee move tablespace users;
8.deallocate of unused space
Sql> ALTER TABLE table_name deallocate unused [keep integer]
9.truncate a table
Sql> TRUNCATE TABLE table_name;
10.drop a table
sql> DROP TABLE table_name [cascade constraints];
11.drop a column
Sql> ALTER TABLE table_name DROP COLUMN comments cascade constraints checkpoint 1000;
ALTER TABLE table_name DROP columns continue;
12.mark a column as unused
Sql> ALTER TABLE TABLE_NAME SET UNUSED column comments cascade constraints;
ALTER TABLE table_name drop unused columns checkpoint 1000;
ALTER TABLE orders drop columns continue checkpoint 1000
Data_dictionary:dba_unused_col_tabs
Fourth Chapter: Index
1.creating function-based Indexes
Sql> CREATE index summit.item_quantity on Summit.item (quantity-quantity_shipped);
2.create a B-tree index
Sql> create [unique] index index_name on table_name (column,.. Asc/desc) tablespace
sql> tablespace_name [Pctfree integer] [initrans integer] [maxtrans integer]
sql> [Logging | nologging] [NOSORT] Storage (initial 200k next 200k Pctincrease 0
Sql> maxextents 50);
3.pctfree (Index) = (maximum number of rows-initial number of rows) *100/maximum number of rows
4.creating Reverse Key indexes
Sql> Create unique index xay_id on Xay (a) reverse pctfree (storage 200k
Sql> next 200k pctincrease 0 maxextents) tablespace indx;
5.create Bitmap Index
Sql> Create bitmap index xay_id on Xay (a) pctfree (storage 200k next 200k
sql> pctincrease 0 maxextents) tablespace indx;
6.change Storage parameter of index
sql> ALTER INDEX XAY_ID storage (next 400k maxextents 100);
7.allocating Index Space
Sql> ALTER INDEX XAY_ID allocate extent (size 200k datafile ' c:/oracle/index.dbf ');
8.alter index xay_id deallocate unused;
The Fifth chapter: Constraint
1.define constraints as immediate or deferred
Sql> alter session set CONSTRAINT[S] = Immediate/deferred/default;
Set Constraint[s] Constraint_name/all immediate/deferred;
2. sql> DROP TABLE table_name CASCADE constraints
Sql> drop tablespace tablespace_name including contents cascade constraints
3. Define constraints while create a table
Sql> CREATE TABLE Xay (ID number (7) Constraint xay_id primary key deferrable
Sql> using index Storage (initial 100k next 100k) tablespace indx);
Primary key/unique/references table (column)/check
4.enable constraints
Sql> ALTER TABLE Xay enable Novalidate constraint xay_id;
5.enable constraints
Sql> ALTER TABLE Xay enable validate constraint xay_id;
Sixth chapter: Load data
1.loading Data using Direct_load Insert
sql> INSERT INTO EMP nologging
Sql> select * from Emp_old;
2.parallel direct-load Insert
Sql> alter session enable parallel DML;
sql> INSERT INTO EMP nologging
Sql> select * from Emp_old;
3.using Sql*loader
Sql> Sqlldr Scott/tiger
sql> control = Ulcase6.ctl
sql> log = Ulcase6.log direct=true
The seventh chapter: Data collation
1.using Expoty
$exp scott/tiger Tables (dept,emp) file=c:emp.dmp log=exp.log compress=n direct=y
2.using Import
$imp scott/tiger Tables (dept,emp) file=emp.dmp Log=imp.log ignore=y
3.transporting a tablespace
Sql>alter tablespace Sales_ts Read only;
$exp sys/. File=xay.dmp Transport_tablespace=y Tablespace=sales_ts
Triggers=n constraints=n
$copy datafile
$imp sys/. File=xay.dmp transport_tablespace=y datafiles= (/disk1/sles01.dbf,/disk2
/SLES02.DBF)
Sql> alter tablespace Sales_ts Read write;
4.checking Transport Set
Sql> Dbms_tts.transport_set_check (ts_list = Sales_ts ' ..., incl_constraints=>true);
View in Table Transport_set_violations
Sql> dbms_tts.isselfcontained is true, which represents the self-contained
The eighth chapter: password Security and resource management
1.controlling account Lock and password
sql> alter user Juncky identified by Oracle account unlock;
2.user_provided Password function
sql> function_name (userid in varchar2 (+), password in VARCHAR2 (30),
Old_password in Varchar2 (()) Return Boolean
3.create a Profile:password setting
Sql> Create profile Grace_5 limit failed_login_attempts 3
Sql> Password_lock_time Unlimited Password_life_time 30
Sql>password_reuse_time password_verify_function verify_function
Sql> Password_grace_time 5;
4.altering a profile
sql> Alter profile default Failed_login_attempts 3
sql> Password_life_time Password_grace_time 10;
5.drop a profile
sql> Drop Profile grace_5 [cascade];
6.create a profile:resource limit
Sql> Create profile Developer_prof limit Sessions_per_user 2
Sql> cpu_per_session 10000 idle_time connect_time 480;
7. View = Resource_cost:alter resource Cost
Dba_users,dba_profiles
8. Enable Resource limits
Sql> alter system set resource_limit=true;
The Nineth chapter: User Management
1.create a user:database authentication
Sql> create user Juncky identified by Oracle default Tablespace users
Sql> Temporary tablespace temp quota 10m on data password expire
sql> [Account Lock|unlock] [profile Profilename|default];
2.change User quota on Tablespace
sql> ALTER user juncky quota 0 on users;
3.drop a user
sql> drop User Juncky [cascade];
4. Monitor User
View:dba_users, Dba_ts_quotas
Tenth chapter: Privileged Management
1.system Privileges:view = System_privilege_map, Dba_sys_privs,session_privs
2.grant system Privilege
Sql> Grant create session,create table to managers;
Sql> Grant create session to Scott with admin option;
With admin option can grant or revoke privilege from any user or role;
3.SYSDBA and Sysoper privileges:
Sysoper:startup,shutdown,alter database Open|mount,alter database backup Controlfile,
Alter tablespace begin/end Backup,recover database
ALTER DATABASE archivelog,restricted session
Sysdba:sysoper privileges with admin option,create database,recover database until
4.password file members:view:=> v$pwfile_users
5.o7_dictionary_accessibility =true restriction access to view or tables in other
Schema
6.revoke system Privil
Command Highlights for Oracle DBA (recommended)