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 Dat Afile 'c: /oracle/index.dbf'); 8.alter index xay_id deallocate unused;
Chapter 5: 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 connection constraints SQL> ALTER TABLE XAY ENABLE; 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 $ ev 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 SQL> dbms_tts.isselfcontained is true in Table Transport_Set_violations, indicating that it is included in 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 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 9: Managing Uses
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 Chapter 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 commission; 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 h r_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 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> 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> 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 data/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 Rollba CK 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 data/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 ;>
