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 1M;
4.Adding Online Redo Log MEMBERS
SQL> ALTABASE 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 CLOAR [Unarchived] logfile
'c: /oacle/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 II: Table Space Management
CREATE TABLESPACES
SQL> CREATE TABLESPACE TABLESPACE_NAME DATAFILE 'C: / ORACLE/Ordata/file1.dbf
'Size 100m,
SQL>
'c: /oracle/oradata/file2.dbf
'Size 100m minimum eXtent 550K [logging / nologing]
SQL> Default Storage (Initial 500K Next 500K MaxExtents 500 Pctincce 0)
SQL> [Online / Offline] [Permanent / Temporary] [EXTENT_MANAGEMENT_CLASE]
2.locally management tablespace
SQL> CREATE TABLESPACE USER_DATA DATAFILE
'c: /oracle/oradata/user_data01.dbf
'
SQL> Size 500M Extent Management Local Uniform Size 10M;
3.Temportory 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 ORINE
SQL> ALTER TABLESPACE APP_DATA OFFLINE;
SQL> ALTER TABLESPACE APP_DATA online;
6.Read_only TableSpace
SQL> ALTER TABLESPACE APP_DATA READOTOMY | Write
7.Droping TableSpace
SQL> Drop tablespace app_data incrududing contents;
8.enableing 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 III: Table
1.create a table
SQL> CREATE TABLE TABLE_NAME (Column Datattype, Column Datattype] ....)
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 | NOLGGING] [cache | nocache]
2.copy an existing TABLE
SQL> CREATE TABLE TABLE_NAME [Logging | NOLOGGING] AS SUBQUERY
3.create Temporary Table
SQL> CREATE GLOBAL TEMPORY TABLE XAY_TEMP As SELECT *.
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 User
8.Deallocate of unused Space
SQL> ALTER TABLE TABLE_NAME DEAALLOCATE 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 Column Continue;
12.Mark a column as unused
SQL> ALTER TABLE TABLE_NAME SET Unuse Column Comments Cascade Constraints;
Alter Table Table_name Drop Unuse Column Checkpoint 1000; Alter Table Orders Drop Columns Continue Checkpoint 1000
Data_Dictionary: DBA_UNUSED_COL_TABS
