Command Highlights for Oracle DBA (recommended)

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)