Scheduling export of tables or database

Create a bat file like startBkp.bat

• Contents of startBkp.bat:

@echo off
echo Generated command to run:
echo.
exp parfile=c:\myparfile.par userid=%*
echo.

• Contents of myparfile.par:
  file c:\myparfile.par
  file=c:\mydumpfile.dmp
  log=C:\mydumpfile.log
  direct=y
  statistics=none
  compress=n
  consistent=n
  

Run it using:
startBkp.bat scott/tiger@database_name
or
startBkp.bat scott/tiger@database_name tables=t1,t2,t3

The same may be scheduled by windows scheduler:
start menu->Programs->Accessories->System tools

Oracle user creation

Create user <name>identified by <passwd> default tablespace <tablespacename> temporary tablespace <temp>;


grant <sysdba> to <username>;

Altering User

SQL> -- with SQL*Plus
SQL> alter user scott identified by ***;

User altered.
SQL> -- with PL/SQL
SQL> begin
2 execute immediate 'alter user scott identified by tiger';
3 end;
4 /PL/SQL procedure successfully completed.
SQL>

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