oracle redologs and logminer

• forcing log switches
  sql> alter system switch logfile;
  
  
• forcing checkpoints
  sql> alter system checkpoint;
  
  
• adding online redo log groups

sql> alter database add logfile [group 4]
sql> ('/disk3/log4a.rdo','/disk4/log4b.rdo') size 1m;


• 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;


• changes the name of the online redo logfile

sql> alter database rename file '/oracle/oradata/oradb/redo01.log'
sql> to '/oracle/oradata/redo01.log';


• drop online redo log groups

sql> alter database drop logfile group 3;


• drop online redo log members

sql> alter database drop logfile member '/oracle/oradata/redo01.log';


• clearing online redo log files

sql> alter database clear [unarchived] logfile '/oracle/log2a.rdo';


• using logminer analyzing redo logfiles

1. in the init.ora specify utl_file_dir = ' '
2. sql> execute dbms_logmnr_d.build('oradb.ora','/oracle/oradb/log');
3. sql> execute dbms_logmnr_add_logfile('/oracle/oradata/oradb/redo01.log',
   sql> dbms_logmnr.new);
4. sql> execute dbms_logmnr.add_logfile('/oracle/oradata/oradb/redo02.log',
   sql> dbms_logmnr.addfile);
5. sql> execute dbms_logmnr.start_logmnr(dictfilename=>'/oracle/oradb/log/oradb.ora');
6. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters
   sql> v$logmnr_logs);
7. sql> execute dbms_logmnr.end_logmnr;
   

Oracle tablespaces and datafiles

• create tablespaces

sql> create tablespace tablespace_name datafile '\oracle\oradata\file1.dbf' size 100m,
sql> '/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]


• locally managed tablespace

sql> create tablespace user_data datafile '/oracle/oradata/user_data01.dbf'
sql> size 500m extent management local uniform size 10m;


• temporary tablespace

sql> create temporary tablespace temp tempfile '/oracle/oradata/temp01.dbf'
sql> size 500m extent management local uniform size 10m;


• 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);


• taking tablespace offline or online

sql> alter tablespace app_data offline;
sql> alter tablespace app_data online;


• read_only tablespace

sql> alter tablespace app_data read onlywrite;


• droping tablespace

sql> drop tablespace app_data including contents;


• enableing automatic extension of data files
  sql> alter tablespace app_data add datafile '/oracle/oradata/app_data01.dbf'size 200m autoextend on next 10m maxsize 500m;
  
  
• change the size of data files manually

sql> alter database datafile '/oracle/oradata/app_data.dbf'resize 200m;


• Moving data files: alter tablespace

sql> alter tablespace app_data rename datafile '/oracle/oradata/app_data.dbf'
sql> to '/oracle/app_data.dbf';


• moving data files:alter database

sql> alter database rename file '/oracle/oradata/app_data.dbf'
sql> to '/oracle/app_data.dbf';

Oracle basics

• 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]


• copy an existing table

sql> create table table_name [logging|nologging] as subquery


• create temporary table

sql> create global temporary table abc_temp as select * from abc
sql>on commit preserve rows/on commit delete rows


• pctfree = (average row size - initial row size) *100 /average row size


• pctused = 100-pctfree- (average row size*100/available data space)



• change storage and block utilization parameter

sql> alter table table_name pctfree=30 pctused=50 storage(next 500k
sql> minextents 2 maxextents 100);


• manually allocating extents

sql> alter table table_name allocate extent(size 500k datafile '/oracle/data.dbf');


• move tablespace

sql> alter table employee move tablespace users;


• deallocate of unused space

sql> alter table table_name deallocate unused [keep integer]


• truncate a table

sql> truncate table table_name;


• drop a table

sql> drop table table_name [cascade constraints];


• drop a column

sql> alter table table_name drop column comments cascade constraints checkpoint 1000;

alter table table_name drop columns continue;


• 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