Common Oracle commands

Chapter 1: 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 1 m;

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: \ oracle \ oradb \ log ');

C. SQL> execute dbms_logmnr_add_logfile ('C: \ oracle \ oradata \ oradb \ redo01.log ',

SQL> dbms_logmnr.new );

D. SQL> execute dbms_logmnr.add_logfile ('C: \ oracle \ oradata \ oradb \ redo02.log ',

SQL> dbms_logmnr.addfile );

E. SQL> execute dbms_logmnr.start_logmnr (dictfilename => 'C: \ oracle \ oradb \ log \ oradb. 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 2: Table space management

1. Create tablespaces

SQL> Create tablespace tablespace_name datafile 'C: \ oracle \ oradata \ file1.dbf 'size 100 m,

SQL> 'C: \ oracle \ oradata \ file2.dbf' size 100 m minimum extent 550 K [logging/nologging]

SQL> default storage (initial 500 K next 500 k maxextents 500 pctinccease 0)

SQL> [online/offline] [Permanent/Temporary] [extent_management_clause]

2. Locally managed tablespace

SQL> Create tablespace user_data datafile 'C: \ oracle \ oradata \ user_data01.dbf'

SQL> size 500 m extent management local uniform size 10 m;

3. Temporary tablespace

SQL> create temporary tablespace temp tempfile 'C: \ oracle \ oradata \ temp01.dbf'

SQL> size 500 m extent management local uniform size 10 m;

4. Change the storage setting

SQL> alter tablespace app_data minimum extent 2 m;

SQL> alter tablespace app_data default storage (initial 2 m next 2 m 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: \ oracle \ oradata \ app_data01.dbf' size 200 m

SQL> autoextend on next 10 m maxsize 500 m;

9. Change the size fo data files manually

SQL> alter database datafile 'C: \ oracle \ oradata \ app_data.dbf' resize 200 m;

10. Moving data files: Alter tablespace

SQL> alter tablespace app_data rename datafile 'C: \ oracle \ oradata \ app_data.dbf'

SQL> to 'C: \ oracle \ app_data.dbf ';

11. Moving data files: Alter Database

SQL> alter database rename file 'C: \ oracle \ oradata \ app_data.dbf'

SQL> to 'C: \ oracle \ app_data.dbf ';

Chapter 3: tables

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 200 K next 200 K 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 500 k

SQL> minextents 2 maxextents 100 );

6. Manually allocating extents

SQL> ALTER TABLE table_name allocate extent (size 500 k 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 checkpoints 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 checkpoints 1000;

Alter table orders drop columns continue checkpoint 1000

Data_dictionary: dba_unused_col_tabs

Chapter 4: 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 200 K next 200 K 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 30 storage (initial 200 K

SQL> next 200 K pctincrease 0 maxextents 50) tablespace indx;

5. Create bitmap Index

SQL> Create bitmap index xay_id on xay (a) pctfree 30 storage (initial 200 K next 200 K

SQL> pctincrease 0 maxextents 50) tablespace indx;

6. Change storage parameter of Index

SQL> alter index xay_id storage (next 400 k maxextents 100 );

7. Allocating index space

SQL> alter index xay_id allocate extent (size 200 K datafile 'C:/Oracle/index. dbf ');

8. Alter index xay_id deallocate unused;

Chapter 5: Constraints

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 100 K next 100 k) 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;

Chapter 6: load data

1. Loading data using direct_load insert

SQL> insert/* + append */into EMP nologging

SQL> select * From emp_old;

2. Parallel direct-load insert

SQL> alter session enable parallel DML;

SQL> insert/* + parallel (EMP, 2) */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

Chapter 7: reorganizing data

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> If dbms_tts.isselfcontained is true, it indicates self-contained

Chapter 8: Managing password security and resources

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 (30), password in varchar2 (30 ),

Old_password in varchar2 (30) 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 30 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 60 password_grace_time 10;

5. Drop a profile

SQL> drop profile grace_5 [cascade];

6. Create a profile: resource limit

SQL> Create profile pai_prof limit sessions_per_user 2

SQL> cpu_per_session 10000 idle_time 60 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;

Chapter 9: management users

1. Create a user: Database Authentication

SQL> create user juncky identified by Oracle default tablespace users

SQL> temporary tablespace temp quota 10 m 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

Chapter 10: Managing privileges

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

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 privilege

SQL> revoke CREATE TABLE from Karen;

SQL> revoke create Session from Scott;

7. Grant Object Privilege

SQL> grant execute on dbms_pipe to public;

SQL> grant Update (first_name, salary) on employee to Karen with grant option;

8. Display object privilege: view => dba_tab_privs, dba_col_privs

9. Revoke Object Privilege

SQL> revoke execute on dbms_pipe from Scott [cascade constraints];

10. audit record view: => SYS. AUD $

11. Protecting the audit trail

SQL> Audit delete on SYS. AUD $ by access;

12. Statement Auditing

SQL> Audit user;

13. privilege Auditing

SQL> Audit select any table by Summit by access;

14. schema object Auditing

SQL> Audit lock on Summit. employee by access whenever successful;

15. View audit option: view => all_def_audit_opts, dba_stmt_audit_opts, dba_priv_audit_opts,

Dba_obj_audit_opts

16. View Audit Result: view => dba_audit_trail, dba_audit_exists, dba_audit_object,

Dba_audit_session, dba_audit_statement

Chapter 2: manager role

1. Create roles

SQL> Create role sales_clerk;

SQL> Create role hr_clerk identified by bonus;

SQL> Create role hr_manager identified externally;

2. Modify role

SQL> alter role sales_clerk identified by Commission;

SQL> alter role hr_clerk identified externally;

SQL> alter role hr_manager not identified;

3. Assigning roles

SQL> grant sales_clerto Scott;

SQL> grant hr_clerk to hr_manager;

SQL> grant hr_manager to Scott with admin option;

4. Establish default role

SQL> alter user Scott default role hr_clerk, sales_clerk;

SQL> alter user Scott default role all;

SQL> alter user Scott default role all privileges t hr_clerk;

SQL> alter user Scott default role none;

5. Enable and disable roles

SQL> set role hr_clerk;

SQL> set role sales_clerk identified by Commission;

SQL> set role all distinct T sales_clerk;

SQL> set role none;

6. Remove role from user

SQL> revoke sales_clerk from Scott;

SQL> revoke hr_manager from public;

7. Remove role

SQL> drop role hr_manager;

8. Display role information

View: => dba_roles, dba_role_privs, role_role_privs, dba_sys_privs,

Role_sys_privs, role_tab_privs, session_roles

Chapter 2: backup and recovery

1. V $ SGA, V $ instance, V $ process, V $ bgprocess, V $ database, V $ datafile, V $ sgastat

2. RMAN need set dbwr_io_slaves or backup_tape_io_slaves and large_pool_size

3. Monitoring parallel rollback

> V $ fast_start_servers, V $ fast_start_transactions

4. Perform a closed Database Backup (noarchivelog)

> Shutdown immediate

> CP files/backup/

> Startup

5. Restore to a different location

> Connect system/manager as sysdba

> Startup Mount

> Alter database rename File '/disk1/.../user. DBF' to '/disk2/.../user. dbf ';

> Alter database open;

6. Recover syntax

-- Recover a mounted Database

> Recover database;

> Recover datafile '/disk1/data/df2.dbf ';

> Alter database recover database;

-- Recover an opened database

> Recover tablespace user_data;

> Recover datafile 2;

> Alter database recover datafile 2;

7. How to Apply redo log files automatically

> Set autorecovery on

> Recover automatic datafile 4;

8. Complete recovery:

-- Method 1 (mounted databae)

> Copy c: \ backup \ User. DBF c: \ oradata \ User. DBF

> Startup Mount

> Recover datafile 'C: \ oradata \ User. DBF;

> Alter database open;

-- Method 2 (opened database, initially opened, not system or rollback datafile)

> Copy c: \ backup \ User. DBF c: \ oradata \ User. DBF (alter tablespace offline)

> Recover datafile 'C: \ oradata \ User. dbf' or

> Recover tablespace user_data;

> Alter database datafile 'C: \ oradata \ User. dbf' online or

> Alter tablespace user_data online;

-- Method 3 (opened database, initially closed not system or rollback datafile)

> Startup Mount

> Alter database datafile 'C: \ oradata \ User. dbf' offline;

> Alter database open

> Copy c: \ backup \ User. dbf d: \ oradata \ User. DBF

> Alter database rename file 'C: \ oradata \ User. dbf' to 'd: \ oradata \ User. dbf'

> Recover datafile 'e: \ oradata \ User. dbf' or recover tablespace user_data;

> Alter tablespace user_data online;

-- Method 4 (loss of data file with no backup and have all archive log)

> Alter tablespace user_data offline immediate;

> Alter database create datafile 'd: \ oradata \ User. dbf' as 'C: \ oradata \ User. dbf''

> Recover tablespace user_data;

> Alter tablespace user_data online

5. perform an Open Database Backup

> Alter tablespace user_data begin backup;

> Copy files/backup/

> Alter database datafile '/C:/../data. dbf' end backup;

> Alter system switch logfile;

6. Backup A Control File

> Alter Database Backup controlfile to 'control1. bkp ';

> Alter Database Backup controlfile to trace;

7. Recovery (noarchivelog Mode)

> Shutdown abort

> CP files

> Startup

8. Recovery of file in backup mode

> Alter database datafile 2 end backup;

9. Clearing redo log file

> Alter database clear Unarchived logfile group 1;

> Alter database clear Unarchived logfile group 1 unrecoverable datafile;

10. redo log recovery

> Alter database add logfile group 3 'C: \ oradata \ redo03.log' size 1000 K;

> Alter database drop logfile group 1;

> Alter database open;

Or> CP c: \ oradata \ redo02.log 'C: \ oradata \ redo01.log

> Alter database clear logfile 'C: \ oradata \ log01.log ';