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 CLOGFILE '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;
Table space management
CREATE TABLESPACES
SQL> CREATE TABLESPACE TABLESPACE_NAME DATAFILE 'C: /Oracle/oradata/file1.dbf' size 100m, SQL> 'c: /oacle/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/Ordata/User_data01.dbf'
SQL> Size 500M Extent Management Local Uniform Size 10M;
3.Temportory TableSpace
SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'C: / ORACLE/Ordata/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 Read Only | 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/Ordata/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: /oacle/app_data.dbf ';
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_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 UNUSED COLUMNS CHECKPOINT 1000;
Alter Table ORDERS DROP Column Continue Checkpoint 1000Data_Dictionary: DBA_UNUSED_COL_TABS
