Common Oracle commands

Source: Internet
Author: User
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 ';

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.