Oracle Tips

1. How to Change the default Table Space for the User?

ALTER USER "SCOTT" DEFAULT TABLESPACE "USERS"

2. AUTO STARTING THE ORACLE DATABASE AT WINDOWS STARTUP

1. OPEN REGISTRY

2. HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->KEY_HOME_NAME->ORA_SID_AUTOSTART

3. set ORA_SID_AUTOSTART=TRUE

Note: By Default windows set this parameter to false.

3. Create Tablespace in Oracle

CREATE TABLESPACE TEST LOGGING DATAFILE 'F:\ORCL\DATAFILE\TEST.ora' SIZE 10M AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

4. Creating Control File in ORACLE.

ALTER DATABASE BACKUP CONTROLFILE TO TRACE (This create the backup in the trace file in UDUMP)

or

ALTER DATABASE BACKUP CONTROLFILE TO 'C:\SOMEWHERE\CONTROL01.bkp';

Then Open the trace file and copy the create control file line and run the script while the database in mount mode.

ALTER DATABASE OPEN RESETLOGS

5. This is the view which will so all the details regarding the database i.e. the database name, timezone, NLS_LANGUAGE, GLOBAL_DB_NAME etc from the view sys.props$.

Select * from GLOBAL_DB_NAME;

6. SQLLDR is the utility that load data from text to oracle database

Follow th instruction.

First create a table

create table upboardtest

(

Roll varchar2(20) PRIMARY KEY,

NAME varchar2(20),

SUB1 varchar2(10),

MARKS1 varchar2(10)

);

create a txt file for the records

e.g. Record.txt

12345,sandip,English,20

12347,sandy,Math,20

12347,Rajesh,English,20

12348,Raju,Math,20

Now create a control file

e.g. Record.ctl and write the following lines in it

LOAD DATA INFILE 'E:\Record.txt.txt' INTO TABLE upboardtest FIELDS TERMINATED BY ',' (ROLL,NAME,SUB1,marks1)

Now in the command prompt type the following command

sqlldr userid=oraivr/makeit2ez@reldb control=E:\record.ctl

7. JOB SCHEDULING IN ORACLE

-- Submit job to begin at 0600 and run every hour

-- ------------------------------------------------------------

dbms_job.submit(

:jobno, 'BEGIN statspack_alert_proc; END;',

trunc(sysdate)+6/24,

'trunc(SYSDATE+1/24,''HH'')',

TRUE,

:instno);

-- ------------------------------------------------------------

-- Submit job to begin at 0900 and run 12 hours later

-- ------------------------------------------------------------

dbms_job.submit(

:jobno,

'BEGIN statspack_alert_proc; END;',

trunc(sysdate+1)+9/24,

'trunc(SYSDATE+12/24,''HH'')',

TRUE,

:instno);

exec dbms_job.broken(120, NEXT_DATE=>TRUNC(SYSDATE) + 1, broken=>FALSE);

exec dbms_job.interval(120, 'TRUNC(SYSDATE) + 3');