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 / dec) TABLESPACE
SQL> TableSpace_name [PctFree Integer] [initrans integer] [MAXTRANS INTEGER]
SQL> [Logging | NOLGGING] [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 5: Constraint
1.Define constraints as immediate or defriet
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;
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> dbms_tts.isselfcontained is true, indicating that it is included
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 varcha2 (30), Password in varcha2 (30),
Old_password in varchar2 (30)) Return Boolean
3.Create a profile: Password settingsql> 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 DEVEL_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 SESTEM SET RESOURCE_LIMIT = True;
Chapter 9: Managing Uses
1.create a user: Database Authentication
SQL> CREATE User Juncky Identified by Oracle Default TableSpace Uses
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 Use;
3.Drop a User
SQL> DROP User Juncky [cascade];
4. Monitor User
View: DBA_USERS, DBA_TS_QUOTAS
