Chapter: 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: / Oracle / ORADB / LOG'); C. SQL> E xecute 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; II: space management 1.create tablespaces sql> create tablespace tablespace_name datafile 'c: /oracle/oradata/file1.dbf'
size 100m, sql> 'c: /oracle/oradata/file2.dbf' size 100m minimum extent 550k [logging / nologging] sql> default storage (initial 500k next 500k 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 500m extent management local uniform size 10m; 3.temporary tablespace sql> create temporary tablespace temp tempfile 'c: /oracle/oradata/temp01.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.enable ing automatic extension of data files sql> alter tablespace app_data add datafile 'c: /oracle/oradata/app_data01.dbf' size 200m sql> autoextend on next 10m maxsize 500m; 9.change the size fo data files manually sql> alter database datafile 'c: /oracle/oradata/app_data.dbf' Resize 200m; 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: 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 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 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 30 storage (initial 200k sql> next 200k pctincrease 0 maxextents 50) tablespace indx; 5.create bitmap index sql> create bitmap index xay_id on xay (a) pctfree 30 storage (initial 200k next 200k sql> pctincrease 0 maxextents 50) 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; 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 / reference 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 VI: 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 NOLGGING 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 T ransport set sql> DBMS_tts.transport_set_check (ts_list => 'sales_ts' .., incl_constraints => true); See sql> dbms_tts.isselfcontained is true that, in Chapter VIII represents a self-contained in Table transport_set_violations: 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 developer_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 IX: Managing users 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 Chapter X: 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 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 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 XI: 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 CommsSion; SQL>
alter role hr_clerk identified externally; sql> alter role hr_manager not identified; 3.assigning roles sql> grant sales_clerk to 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 except 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 except 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 XII: BACKUP and RECOVERY 1. v $ sga, v $ instance, v $ process, v $ bgprocess, V $ DATABASE, V $ DataFile, V $ SGASTAT 2. RMAN NEED SET D bwr_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/use; --Method 2 (Opened Database, Initial Or 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_dat a;> 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 ';>
