Create Trigger to Monitor Database Errors

First, we have to create a table where the errors are stored, but make sure it's a use who has global rights on the database:

CREATE TABLE error_log (
  server_error VARCHAR2(100),
  osuser VARCHAR2(30),
  username VARCHAR2(30),
  machine VARCHAR2(64),
  process VARCHAR2(24),
  program VARCHAR2(48),
  stmt VARCHAR2(4000),
  msg VARCHAR2(4000),
  date_created DATE  
);

After we've created the table, we simply add a trigger with is fired by "AFTER SERVERERROR ON DATABASE":

CREATE OR REPLACE
TRIGGER error_log_trigger 
 AFTER SERVERERROR ON DATABASE
DECLARE
 username_  error_log.username%TYPE;
 osuser_    error_log.osuser%TYPE;
 machine_   error_log.machine%TYPE;
 process_   error_log.process%TYPE;
 program_   error_log.program%TYPE;
  stmt_      VARCHAR2(4000);
  msg_       VARCHAR2(4000);
  sql_text_  ora_name_list_t;
BEGIN
   FOR i IN 1..NVL(ora_sql_txt(sql_text_), 0) LOOP  
    stmt_ := SUBSTR(stmt_ || sql_text_(i) ,1,4000);
  END LOOP;
   FOR i IN 1..ora_server_error_depth LOOP
    msg_ := SUBSTR(msg_ || ora_server_error_msg(i) ,1,4000);
  END LOOP;
   SELECT osuser, username, machine, process, program
  INTO   osuser_, username_, machine_, process_, program_
  FROM   sys.v_$session
  WHERE  audsid = USERENV('SESSIONID');
   INSERT INTO error_log VALUES (dbms_standard.server_error(1), osuser_, username_, machine_, process_, program_, stmt_, msg_, SYSDATE);
END;

Tablespace and datafile monitoring

To check Tablespace free space:

• SELECT TABLESPACE_NAME, SUM(BYTES/1024/1024) "Size (MB)"  FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

To check Tablespace by datafile:

• SELECT tablespace_name, File_id, SUM(bytes/1024/1024)"Size (MB)" FROM DBA_FREE_SPACE group by tablespace_name, file_id;

To Check Tablespace used and free space %:

• SELECT /* + RULE */  df.tablespace_name "Tablespace",df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes  FROM dba_data_files GROUP BY tablespace_name) df WHERE fs.tablespace_name (+)  = df.tablespace_name GROUP BY df.tablespace_name,df.bytes UNION ALL SELECT /* + RULE */ df.tablespace_name tspace,fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes) FROM dba_temp_files fs, (SELECT tablespace_name,bytes_free,bytes_used FROM v$temp_space_header GROUP BY tablespace_name,bytes_free,bytes_used) df WHERE fs.tablespace_name (+)  = df.tablespace_name GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used ORDER BY 4 DESC;

--or--

• Select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used", round((fs.freespace/t.totalspace)*100,2) as "% Free" from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t, (select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;

Tablespace (File wise) used and Free space

• SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) -  NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name ORDER BY file_name;

To check Growth rate of  Tablespace
 
Note: The script will not show the growth rate of the SYS, SYSAUX Tablespace. T
he script is used in Oracle version 10g onwards.

• SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days, ts.tsname , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id AND ts.tsname = dt.tablespace_name AND ts.tsname NOT IN ('SYSAUX','SYSTEM') GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname ORDER BY ts.tsname, days;

List all Tablespaces with free space < 10% or full space> 90%

• Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add from (select tablespace_name,0 tots,sum(bytes) sumb from dba_free_space a group by tablespace_name union Select tablespace_name,sum(bytes) tots,0 from dba_data_files group by tablespace_name) a group by a.tablespace_name having sum(a.sumb)*100/sum(a.tots) < 10 order by pct_free;

Script to find all object Occupied space for a Tablespace

• Select OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024 "SZIE IN MB" from dba_segments where TABLESPACE_NAME = 'SDH_HRMS_DBF' group by OWNER, SEGMENT_NAME;

Which schema are taking how much space

• Select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB" from (select owner, count(*) obj_cnt from dba_objects group by owner) obj, (select owner, ceil(sum(bytes)/1024/1024) seg_size  from dba_segments group by owner) seg  where obj.owner  = seg.owner(+)  order    by 3 desc ,2 desc, 1; 

 To Check Default Temporary Tablespace Name:

• Select * from database_properties where PROPERTY_NAME like '%DEFAULT%'; 

 To know default and Temporary Tablespace for particualr User:

• Select username,temporary_tablespace,default_tablespace from dba_users where username='HRMS'; 

 To know Default Tablespace for All User:

• Select default_tablespace,temporary_tablespace,username from dba_users; 

To Check Datafiles used and Free Space:  

• SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb,((df.bytes / 1024 / 1024) -  NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name ORDER BY file_name;  

To check Used free space in Temporary Tablespace: 

• SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE FROM   V$temp_space_header GROUP  BY tablespace_name; 
• SELECT   A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM     v$sort_segment A, ( SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM   v$tablespace B, v$tempfile C WHERE    B.ts#= C.ts#  GROUP BY B.name, C.block_size ) D WHERE    A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;  

Sort (Temp) space used by Session

• SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,T.tablespace, COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial; 

 Sort (Temp) Space Usage by Statement

• SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,T.sqladdr address, Q.hash_value, Q.sql_text FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_nameORDER BY S.sid;  

Who is using which UNDO or TEMP segment?  

• SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,NVL(s.username, 'None') orauser,s.program, r.name undoseg,t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter   x WHERE s.taddr = t.addr AND r.usn   = t.xidusn(+) AND x.name  = 'db_block_size';  

Who is using the Temp Segment?  

• SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",a.sid||','||a.serial# SID_SERIAL, a.username, a.program FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p WHERE p.name  = 'db_block_size' AND a.saddr = b.session_addr ORDER BY b.tablespace, b.blocks;  

Total Size and Free Size of Database:

• Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",round(free.p / 1024 / 1024/1024) || ' GB' "Free space" from (select bytes from v$datafile  union all select bytes from v$tempfile   union all  select bytes from v$log) used,  (select sum(bytes) as p from dba_free_space) free group by free.p;  

To find used space of datafiles:

• SELECT SUM(bytes)/1024/1024/1024 "GB" FROM dba_segments; 

IO status of all of the datafiles in database:

• WITH total_io AS  (SELECT SUM (phyrds + phywrts) sum_io FROM v$filestat) SELECT   NAME, phyrds, phywrts, ((phyrds + phywrts) / c.sum_io) * 100 PERCENT,  phyblkrd, (phyblkrd / GREATEST (phyrds, 1)) ratio  FROM SYS.v_$filestat a, SYS.v_$dbfile b, total_io c WHERE a.file# = b.file# ORDER BY a.file#;  

Displays Smallest size the datafiles can shrink to without a re-organize.

• SELECT a.tablespace_name, a.file_name, a.bytes AS current_bytes, a.bytes - b.resize_to AS shrink_by_bytes, b.resize_to AS resize_to_bytes FROM   dba_data_files a, (SELECT file_id, MAX((block_id+blocks-1)*&v_block_size) AS resize_to  FROM   dba_extents  GROUP by file_id) b  WHERE  a.file_id = b.file_id ORDER BY a.tablespace_name, a.file_name; 

Scripts to Find datafiles increment details:

• Select  SUBSTR(fn.name,1,DECODE(INSTR(fn.name,'/',2),0,INSTR(fn.name,':',1),INSTR(fn.name,'/',2))) mount_point,tn.name   tabsp_name,fn.name   file_name,ddf.bytes/1024/1024 cur_size, decode(fex.maxextend,NULL,ddf.bytes/1024 1024,fex.maxextend*tn.blocksize/1024/1024) max_size,nvl(fex.maxextend,0)*tn.blocksize/1024/1024 - decode(fex.maxextend,NULL,0,ddf.bytes/1024/1024)   unallocated,nvl(fex.inc,0)*tn.blocksize/1024/1024  inc_by from  sys.v_$dbfile fn,    sys.ts$  tn,    sys.filext$ fex,    sys.file$  ft,    dba_data_files ddf where    fn.file# = ft.file# and  fn.file# = ddf.file_id and    tn.ts# = ft.ts# and    fn.file# = fex.file#(+) order by 1;

How to solve shutdown hangs oracle

In order to check reason why shutdown immediate hangs

   SQL>connect / as SYSDBA

   SQL>Select * from x$ktuxe where ktuxecfl = 'DEAD';

This shows dead transactions that SMON is looking to rollback.

Now Plan to shutdown again and gather some information. Before issuing the shutdown immediate command set some events as follows:

   SQL>alter session set events '10046 trace name context forever, level 12';

   SQL>alter session set events '10400 trace name context forever, level 1';

   SQL>shutdown immediate;

10046 turns on extended SQL_TRACE for the shutdown process.

10400 dumps a system state every 5 minutes.

The trace files should show where the time is going. To check the progress of SMON is very important in this case. You can find it with the below query.

SQL>SELECT r.NAME "RB Segment Name", dba_seg.size_mb,
DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ')   || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, v$session.SID, v$session.SERIAL#, p.SPID, v$session.process,
v$session.USERNAME, v$session.STATUS, v$session.OSUSER, v$session.MACHINE,
v$session.PROGRAM, v$session.module, action
FROM v$lock l, v$process p, v$rollname r, v$session,
(SELECT segment_name, ROUND(bytes/(1024*1024),2) size_mb FROM dba_segments
WHERE segment_type = 'TYPE2 UNDO' ORDER BY bytes DESC) dba_seg
WHERE l.SID = p.pid(+) AND v$session.SID = l.SID AND
TRUNC (l.id1(+)/65536)=r.usn
-- AND l.TYPE(+) = 'TX' AND
-- l.lmode(+) = 6
AND r.NAME = dba_seg.segment_name
--AND v$session.username = 'SYSTEM'
--AND status = 'INACTIVE'
ORDER BY size_mb DESC;

Reason: Shut down immediate may hang because of various reasons.

• Processes still continue to be connected to the database and do not terminate.
• SMON is cleaning temp segments or performing delayed block cleanouts.
• Uncommitted transactions are being rolled back.

Debugging a hung database in oracle version 11g

Back in oracle 10g a hung database was real problem, especially could not connect via SQL*plus release the source of the hanging. There is a new feature in Oracle 11g SQL*Plus called the “prelim” option. This option is very useful for running oradebug and other utilities that do not require a real connection to the database.

C:\>sqlplus –prelim

-or- in SQL you can set

SQL>Set _prelim on

SQL>connect / as sysdba

Now you are able to run oradebug commands to diagnose a hung database issue: 

SQL> oradebug hanganalyze 3

Wait at least 2 minutes to give time to identify process state changes.

SQL>oradebug hanganalyze 3

Open a separate SQL session and immediately generate a system state dump.

SQL>alter session set events 'immediate trace name SYSTEMSTATE level 10';

How to Check why shutdown immediate taking longer time to shutdown?

 

Ref. 1076161.6: Shutdown immediate or shutdown Normal hangs. SMON disabling TX recovery

Ref. Note 375935.1: What to do and not to do when shutdown immediate hangs.

Ref. Note 428688.1: Shutdown immediate very slow to close database.

When shutdown immediate taking longer time as compare to the normal time usually it is taking. You must perform following task before performing actual shutdown immediate.

• All active session.
• Temporary Tablespace Recover.
• Long Running Query in Database.
• Large Transaction.
• Progress of the Transaction that oracle is recovering.
• Parallel Transaction Recovery.

• SQL> Select sid, serial#, username, status, schemaname, logon_time from v$session where status='ACTIVE' and username is not null;

If Active session is exist then, try to find out what is doing in the database by this session. Active session makeshutdown slower

• SQL> Select f.R "Recovered", u.nr "Need Recovered" from (select count(block#) R , 1 ch from sys.fet$ ) f,(selectcount(block#) NR, 1 ch from sys.uet$) u where f.ch=u.ch;

Check to see any long query is running into the database while you are trying to shutdown the database.

• SQL> Select * from v$session_longops where time_remaining>0 order by username;

Check to ensure large transaction is not going on while you are trying to shutdown the database.

• SQL>Select sum(used_ublk) from v$transaction;

Check the progress of the transaction that oracle is recovering.

• SQL>Select * from v$fast_start_transactions;

Check to ensure that any parallel transaction recovery is going on before performing shutdown immediate.

• SQL>Select * from v$fast_start_servers;

Finally if you do not understand the reason why the shutdown is hanging or taking longer time to shutdown then try to shutdown your database with ‘abort’ option and startup with ‘restrict’ option and try shutdown with ‘immediate’ option.

Check the alert.log, if you find any error related ‘Thread 1 cannot allocate new log, sequence’ then you need to enable your archival process. Your archival is disable due to any reason.

Process:

1. In command prompt set the oracle_sid first

• $ SET ORACLE_SID = ‘your db_name’

2. Now start the SQL*plus:

• $ sqlplus /nolog
• SQL>connect sys/***@instance_name
• SQL>Select instance_name from v$instance;

3. Try to checkpoint before shutdown abort 

• SQL>alter system checkpoint;
• SQL> shutdown abort;

4. Start the database with ‘restrict’ option so that no other user is able to connect you in the mean time.

• SQL>startup restrict;
• SQL>select logins from v$instance;

RESTRICTED

• SQL>shutdown immediate;

5. Mount the database and ensure archive process is enabling by using archive log list command. If it is disabling then enable it.

• SQL>startup mount;
• SQL> archive log list;  --if disable then enable it
• SQL>Alter database archivelog;
• SQL> Alter system archive log start;

Note: If your archivelog destination and format is already set no need to set again. After setting check with the ‘archive log list’ command archival is enable or not. 

• SQL> alter database open;

Now check if your database is still in restricted mode then remove the restriction.

• SQL>select logins from v$instance;
• SQL>alter system disable restricted session;

Note: Now try to generate archivelog with any command

• SQL>alter system archivelog current;
• SQL>alter system switch logfile;

Now try to check or perform normal shutdown and startup the database!!

How to Change DBTIMEZONE after Database Creation

DBTIMEZONE is a function which returns the current value of Database Time Zone. It can be queried using the example below:

SELECT DBTIMEZONE FROM DUAL;

DBTIME

------

-07:00

Please note the return type of function is Time Zone Offset. The format ([+|-] TZH: TZM) contains the lead (+) and lag (-) with hour and minutes specifications.
 
Notes:

1. Database Time zones can be queried from V$TIMEZONE_NAMES dictionary view.
2. A Time zone can be converted into Time Zone offset format using TZ_OFFSET function.

Example:

SELECT TZ_OFFSET('America/Menominee') FROM DUAL;

TZ_OFFS

--------

-06:00

3. Time zone is set during database creation or using CREATE DATABASE. It can be altered using ALTER DATABASE command. Database time zone cannot be altered if a column of type TIMESTAMP WITH [LOCAL] TIMEZONE exists in the database because TIMESTAMP with LOCAL TIMEZONE columns are stored to normalize the database. Time zone can be set in Location zone format or [+|-]HH:MM format.

In the case when you have any column with TIMESTAMP LOCAL TIMEZONE (TSLTZ) then you have to follow the backup–drop that table–change the timezone then restore that table. To check run the below query and notice the output:

Select u.name || '.' || o.name || '.' || c.name "Col TSLTZ"     

from sys.obj$ o, sys.col$ c, sys.user$ u     

where c.type# = 231 and o.obj# = c.obj# and u.user# = o.owner#;

Col TSLTZ

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

ASSETDVP.TEST.TSTAMP

For Example follow the below steps:

1- Backup the table that contains this column (ASSETDVP.TEST.TSTAMP Table).

2- Drop the table or the column only

3- Issue again the alter database to change the DB time Zone

4- Add the dropped column and restore the data OR restore the table if it's dropped

Example:

SQL> SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE   DBTIMEZONE

+06:00            -07:00

SQL> ALTER DATABASE SET TIME_ZONE='America/Menominee';

Database altered.

SQL> ALTER DATABASE SET TIME_ZONE='-06:00';

Database altered.

SQL> Shutdown;

SQL> Startup;

SQL> SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE   DBTIMEZONE

+06:00            +06:00

Note: Once the time zone is set, database must be bounced back to reflect this changes because alter database didn’t change the init.ora parameter.

4. Difference between SYSDATE and DBTIMEZONE- SYSDATE shows the date-time details provided by the OS on the server. It has nothing to do with TIMEZONE of the database.

5. DBTIMEZONE and SESSIONTIMEZONE are different in their operational scope. DBTIMEZONE shows the database time zone, while SESSIONTIMEZONE shows it for the session. This implies that if the time zone is altered at session level, only SESSIONTIMEZONE will change and not the DBTIMEZONE.

Oracle database silent-mode installation

You can automate the installation and configuration of Oracle software, either fully or partially, by specifying a response file when you start the Oracle Universal Installer. The Installer uses the values contained in the response file to provide answers to some or all of the Installer prompts:

If you include responses for all of the prompts in the response file and specify the -silent option when starting the Installer, then the Installer runs in silent mode. During a silent-mode installation, the Installer does not display any screens. Instead, it displays progress information in the terminal that you used to start it.

PREREQUEST
Adding dba group
[root@carol root]# groupadd -g 200 dba
oracle User creation
[root@carol root]# useradd -g dba -d /home/oracle -s /bin/bash -c Oracle Software Owner -m -u 300 oracle
Kernel Settings
vi /etc/sysctl.conf
SET THE ORACLE RECOMENDED KERNEL PARAMETERS IN THIS FILE
OraInst file
[root@carol root]# mkdir -p /var/opt/oracle
[root@carol root]# cd /var/opt/oracle
[root@carol oracle]# vi oraInst.loc
and enter the values
inventory_loc=home/oracle/oraInventory
inst_group=
(save and exit)
[root@carol root]# cat /var/opt/oracle/oraInst.loc
inventory_loc=home/oracle/oraInventory
inst_group=
[root@carol oracle]# chown oracle:dba oraInst.loc
[root@carol oracle]# chmod 664 oraInst.loc
[root@carol oracle]# su - oracle
[oracle@carol oracle]$ cd database/response/
[oracle@carol response]$ vi enterprise.rsp
Modify the Below Three Values for SOFTWARE ONLY INSTALLATION
ORACLE_HOME=/home/oracle/product/10.2.0.1
ORACLE_HOME_NAME=orcl
n_configurationOption=3
[oracle@carol database]$ ./runInstaller -silent -responsefile /home/oracle/database/response/enterprise.rsp
Starting Oracle Universal Installer...
...............
skipped ....
Installation in progress (Thu May 15 23:54:45 IST 2008)
............................................................... 18% Done.
............................................................... 36% Done.
............................................................... 54% Done.
............................................................... 73% Done.
............ 76% Done.
Install successful
Linking in progress (Thu May 15 23:59:36 IST 2008)
Link successful
Setup in progress (Fri May 16 00:06:30 IST 2008)
.............. 100% Done.
Setup successful

The following configuration scripts
/home/oracle/product/10.2.0.1/root.sh
need to be executed as root for configuring the system. If you skip the execution of the configuration tools, the configuration will not be complete and the product wont function properly. In order to get the product to function properly, you will be required to execute the scripts and the configuration tools after exiting the OUI.

Open A new Window with root user and execute the below script
[root@carol ]# sh /home/oracle/product/10.2.0.1/root.sh
Running Oracle10 root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /home/oracle/product/10.2.0.1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
[root@carol ]#
Test Oracle Installation From Oracle User
[oracle@carol oracle]$ source .bash_profile
[oracle@carol oracle]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 16 00:10:19 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL>

Steps to create a database manually on Linux

This article shows you steps to create a database manually on Linux.
Step 1:

First create all the necessary directories. Followings are my directories:
NB:Make sure you create I have them already and therefore I will just list them

testdb1]$ ls
admin backup archive
admin]$ ls
adump bdump cdump udump

Step 2:

Next prepare the database creation script. Following is my script  "testdb1.sql"

CREATE DATABASE "testdb1"
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/d02/carol/testdb1/redo1.log' SIZE 10M,
GROUP 2 '/d02/carol/testdb1/redo2.log' SIZE 10M,
GROUP 3 '/d02/carol/testdb1/redo3.log' SIZE 10M
DATAFILE
'/d02/carol/testdb1/system.dbf' size 100m,
'/d02/carol/testdb1/usr04.dbf' size 10m
sysaux datafile '/d02/carol/testdb1/sysaux.dbf' size 100m
undo tablespace undotbs
datafile '/d02/carol/testdb1/undo.dbf' size 50m
CHARACTER SET US7ASCII
;

Step 3:

Prepare the init file. Like this one [inittestdb1.ora]

*.audit_file_dest='/d02/carol/testdb1/admin/adump'
*.background_dump_dest='/d02/carol/testdb1/admin/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/d02/carol/testdb1/control01.ctl',
'/d02/carol/testdb1/control02.ctl','/d02/carol/testdb1/control03.ctl'
*.core_dump_dest='/d02/carol/testdb1/admin/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='testdb1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=indiaXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/d02/carol/testdb1/archive'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=200278016
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=601882624
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='/d02/carol/testdb1/admin/udump'
*.db_recovery_file_dest='/d02/carol/testdb1/backup'
*.db_recovery_file_dest_size=2147483648

Step 4:

Now perform the following steps:

$ export ORACLE_SID=testdb1

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 22 17:35:28 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.

SQL> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/inittestdb1.ora nomount

ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1263176 bytes
Variable Size 167774648 bytes
Database Buffers 427819008 bytes
Redo Buffers 7122944 bytes

SQL> @testdb1.sql
Database created.

Step 5:

So your database is create. Now just run the catalog.sql and catproc.sql scripts.
You will find the in $ cd $ORACLE_HOME/rdbms/admin

SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql
SQL> select name from v$database;

NAME
---------
TESTDB1

Okay, now your database is ready to use.

Invisible indexes oracle 11g

11g allows indexes to be marked as invisible. Invisible indexes are maintained or structured like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level.
It can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.
CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;
A query using the indexes column in the WHERE clause ignores the index and does a full table scan.
Create a table and execute select commands
SET AUTOTRACE ON
SELECT * FROM invisible_table WHERE id = 9999;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INVISIBLE_TABLE | 1 | 3 | 7 (0)| 00:00:01 |
Change the OPTIMIZER_USE_INVISIBLE_INDEXES parameter makes the index available to the optimizer.
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
SELECT * FROM invisible_table WHERE id = 9999;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| INVISIBLE_TABLE_ID | 1 | 3 | 1 (0)| 00:00:01 |
The index visible means it is still available to the optimizer when the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is reset.
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;
ALTER INDEX invisible_table_id VISIBLE;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| INVISIBLE_TABLE_ID | 1 | 3 | 1 (0)| 00:00:01 |
II (Invisible Indexes) can be useful for processes with specific indexing needs, where the presence of the indexes may adversely affect other functional areas. They are also useful for testing the impact of dropping an index.
The visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.

Oracle database requirements and install

Oracle Database 11g R2 Preinstallation Requirements on Linux and Install

a) The first thing we need to verify is, if the hardware we have is okay for an Oracle 11g Installation
======================================================

There are a number of parameters to be set on Linux. In this article we will prepare Oracle Enterprise Linux to the installation of Oracle Database 11gR2.Unless otherwise specified all of the following process steps must be made with user “root” .

The first thing we need to verify is, if the hardware we have is okay for an Oracle 11g Installation.
===========================================================
run script:-

[oracle@localhost ~]$ ./check_mem_cpu.sh 

-- Check Physical RAM.
=======================
# grep MemTotal /proc/meminfo
MemTotal:      2075424 kB


 We need at least 1GB of physical RAM.
 In my case I have 2GB.

-- Check Swap Space.
===========================
# grep SwapTotal /proc/meminfo
SwapTotal:     3148732 kB


 RAM between 1GB and 2GB then Swap = 1.5 times the size of RAM
 RAM between 2GB and 16GB then Swap = equal to the size of RAM
 RAM more than 16GB then Swap Size = 16GB
 Since my RAM is 2GB, so I have 3GB of Swap Size.

Check disk
=============
# df -h /dev/shm/
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                1014M     0 1014M   0% /dev/shm


 What ever you are going to use your Automatic Memory Management
 Targets later on this database, you need to have shared memory
 file system size at lease equal to those settings. In my case I
 have plans to use memory management targets lower then 1000M
 so 1014 M of shared memory file system is sufficient for me.
 But if you would like to have a bigger shared memory (/dev/shm)
 size then do the following:

Alter/change the size of /dev/shm mount if necessary
=====================================
# mount -t tmpfs tmpfs -o size=1500m /dev/shm

# df -h /dev/shm/
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 1.5G     0  1.5G   0% /dev/shm

Check space available in /tmp
=============================================
# df -h /tmp
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda5             1.5G   35M  1.4G   3% /tmp


  You need to have at least 1GB of space in the /tmp directory.
  Make sure you have 1GB in the column "Avail" in the above output.
  In my case I have 1.4G space available in /tmp.

-- Check space for Oracle Software and pre configured database.
-- I have created a separate partition "/u01" for Oracle Software
-- and database files
=========================================
# df -h /u01
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              15G  166M   14G   2% /u01

            Important factors to consider
***********************************************
I have 14G available space on the partition /u01.

  Space requirement for Oracle 11g Release 2 Software:
     Enterprise Edition      3.95G
     Standard Edition          3.88G
 
  Space requirement for Oracle Database Files:
     1.7 G
    
  creating a database is always optional with oracle installation.Can be done later.
   
***********************************************************************************

To install Oracle 11g Release 2 your system should be running on at least 1024x768 screen resolution.

Check the Resolution
==================
# xdpyinfo | grep 'dimensions:'
 
dimensions:    1280x720 pixels (433x244 millimeters)

To install Oracle 11g Release 2 on RHEL 5 you should be running a kernel version 2.6.18 or later.
==================================================
# uname -r
2.6.18-194.el5

Make sure that there is an entry in /etc/hosts file for your machine like this:
=================================================
1. Edit hosts file and add specifications as per your machine.eg if as root user do not sudo

$ sudo vim /etc/hosts
# vim /etc/hosts

<IP-address>     <fully-qualified-machine-name>     <machine-name>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
192.168.1.74      ora11gr2.localdomain          ora11gr2
192.168.1.121     localhost.localdomain          localhost

2. Set kernel parameters in sysctl.conf file. These values are suggested by Oracle. According to your server, you can change some values.Save after editing :wq

$ sudo vim /etc/sysctl.conf
# vim /etc/sysctl.conf

I added the following Oracle settings
~~~~~~~~~~~~~~~~~~~~~~~~~
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
net.ipv4.tcp_wmem = 262144 262144 262144
net.ipv4.tcp_rmem = 4194304 4194304 4194304

Make the kernel parameters changes effective immediately:
==============================================================
#sudo /sbin/sysctl -p

Verify the parameters are changed or not?
================================================================
As root or oracle USER

# /sbin/sysctl -a | grep name_of_kernel_parameter 

e.g.  # /sbin/sysctl -a | grep shmall

$ /sbin/sysctl –p

kernel.shmall  paramter must be equal to page size.
====================================
pagesize
$ getconf PAGESIZE

Run the script check_limits.sh script to set limit values in limits.conf file.
=============================================
Now set the shell limits for the user Oracle.Open /etc/security/limits.conf copy and paste
to add the lines below (oracle/root user).

$ sudo vim /etc/security/limits.conf or # vim /etc/security/limits.conf

oracle     soft     nproc     2047   can go upto****65536
oracle     hard     nproc     16384  can go upto****65536
oracle     soft     nofile    1024   can go upto****65536
oracle    hard     nofile    65536
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NB:The shell limits can be more than the above settings but not less
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Open /etc/pam.d/login file and add following line.
==================================
$ sudo vim /etc/pam.d/login

add:  session required  pam_limits.so

You must disable SELINUX. Edit /etc/selinux/config file to disable it.
===========================================
Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.
$vim /etc/selinux/config

$ SELINUX=disable

Alternatively, this alteration can be done using the GUI tool (Applications > System Settings > Security Level). Click on the SELinux tab and disable the feature. If SELinux is disabled after installation, the server will need a reboot for the change to take effect.

*****If you have the Linux firewall enabled, you will need to disable or configure it

Run the scripts for checking packages:- check_packages.py
==========================================
$ ./check_packages.py

RPM packages which are required by Oracle must be installed .  You can check needed rpm packages with following command;

$rpm -q binutils compat-db compat-libstdc++-33 libgcc glibc glibc-devel glibc-headers gcc gcc-c++ libgcc libstdc++ cpp make libaio ksh elfutils-libelf make sysstat libaio libaio-devel setarch libXp –qf “%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n”|sort

If a rpm is not installed then you can install it from Oracle DVD/ISO and if connected to the internet u can YUM
===========================================================
$cd /media/cdrom/Server
$rpm -ivh binutils-2.*

******If connected to the internet

$ yum install package name

******NB:Packages that are not installed will indicate:eg, package compat-libstdc++ is not installed.SO

$ yum install compat-libstdc++ 

Allow the user oracle to use X server, which it will need to run Oracle Universal Installer.
======================================================

# xhost +SI:localuser:oracle

Switching to the oracle user to see which shell is being used by user Oracle
================================================
# su - oracle   password:

$ echo $SHELL
/bin/bash

Create oracle operating system user and groups.
==================================

First create groups
~~~~~~~~~~~~~~~~
$ groups
$ id
$ /usr/sbin/groupadd -g 500 oracle
$ /usr/sbin/groupadd -g 501 dba

**Create directory which will be used for oracle software installation. I create default directory. You can change it.
    $ mkdir -p /u01/app/oracle/product/11.2.0/db_1

**Create oracle operating system user.
    $ /usr/sbin/useradd -m -u 501 -g oracle -G dba -d /u01/app/oracle/product/11.2.0/db_1 oracle

**Set owner of oracle base directory as oracle user .
    $ chown -R oracle:dba /u01

**Give permissions to this directory.
    $ chmod -R 775 /u01

**Specify password of oracle user.
    $ passwd oracle

As oracle user,edit .bash_profile file and add following lines
======================================
$ vim .bash_profile

# User specific environment and startup programs

TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
PATH=$PATH:$HOME/bin
export PATH
umask 022
set SYSTEM_PASS=SYSTEM/root
set -o vi
EDITOR=vim
export EDITOR
ORAENV_ASK=NO
ORACLE_SID=orcl
. oraenv
ORAENV_ASK=YES

After that save the bash profile file and run it to set operating system envimronment variables.

$ source ~/.bash_profile

Add oracle to /etc/sudoers. This will give oracle super user privilege.
===========================================
oracle    ALL=(ALL) ALL

To avoid MAKE Error problems during installation
===========================================
Ensure that gcc is installed on your system by executing:

$ which gcc
/usr/bin/gcc

Here is the command to find the RPM package name for /usr/bin/gcc:
======================================================================
$ rpm -qf /usr/bin/gcc
gcc-2.96-98

To avoid MAKE Error problems invoking target install of makefile /u01/app/oracle/product/11.2.0/ctx/lib/ins_ctx.mk
===================================================================================================================
[root@localhost 11.2.0]# mkdir -p /ctx/lib/

#vim ins_ctx.mk

Edit the file $ORACLE_HOME/ctx/lib/env_ctx.mk, add "$(LDLIBFLAG)dl" to the "INSO_LINK =" line. The updated line should with the added "$(LDLIBFLAG)dl" flag,

 should look like this:
~~~~~~~~~~~~~~~~~~~~~~~~~~~
INSO_LINK = -L$(CTXLIB) $(LDLIBFLAG)m $(LDLIBFLAG)dl $(LDLIBFLAG)sc_ca $(LDLIBFLAG)sc_fa $(LDLIBFLAG)sc_ex $(LDLIBFLAG)sc_da $(LDLIBFLAG)sc_ut $(LDLIBFLAG)sc_ch $(LDLIBFLAG)sc_fi $(LLIBCTXHX) $(LDLIBFLAG)c -Wl,-rpath,$(CTXHOME)lib $(CORELIBS) $(COMPEOBJS)

If this did not fix the problem, try the following solution:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Edit the file $ORACLE_HOME/ctx/lib/env_ctx.mk again, go to "INSO_LINK =", remove the above entry you made and add "`cat $(LIBHOME)/sysliblist`" to the line and save it. This is what the updated line, with the added "`cat $(LIBHOME)/sysliblist`" string,

should look like this:
~~~~~~~~~~~~~~~~~~~~~~~~~~~

INSO_LINK = -L$(CTXLIB) $(LDLIBFLAG)m `cat $(LIBHOME)/sysliblist` $(LDLIBFLAG)sc_ca $(LDLIBFLAG)sc_fa $(LDLIBFLAG)sc_ex $(LDLIBFLAG)sc_da $(LDLIBFLAG)sc_ut $(LDLIBFLAG)sc_ch $(LDLIBFLAG)sc_fi $(LLIBCTXHX) $(LDLIBFLAG)c -Wl,-rpath,$(CTXHOME)lib $(CORELIBS) $(COMPEOBJS)

You may see the following errors in $ORACLE_HOME/install/make.log:

*****Oracle Enterprise Linux server is ready to install Oracle database*****


Install Oracle 11g Release 2 on RHEL 5:

The environment is ready for oracle installation. Its time to prepare the oracle installation media now.
Download or Copy the oracle media to the oracle user home directory i.e. /home/oracle.
Once Download/Copy is done unzip the media as follows:

====================================================
 b) INSTALL ORACLE 11g Release 2 on RHEL 5/Centos:
====================================================

Download linux_11gR2_database_1of2.zip & linux_11gR2_database_2of2.zip and copy them to home directoty

Then:
$ cd /home/oracle
$ ls -rtl
linux_11gR2_database_1of2.zip  linux_11gR2_database_2of2.zip

Unzip the files
=================
$ unzip linux_11gR2_database_1of2.zip
$ unzip linux_11gR2_database_2of2.zip

Once the unzip is finished go to the "database" directory unzipped in the previous step and start the Oracle Universal Installer.

$ cd /home/oracle/database/
$ ./runInstaller

NB:
*****Incase you encounter the missing packages,open a terminal as root and yum install listed packages
*****Incase of the MAKE problem,check on; To avoid MAKE Error problems above
*****If the missing packges are not found by yum,click ignore all and continue with the installation

postinstallation settings
------------------------------------------
As a last step you will be asked to execute some configuration scripts as root.

Execute Configuration Scripts

Open another terminal as root and copy/paste on the terminal

# /u01/app/oraInventory/orainstRoot.sh

    Changing permissions of /u01/app/oraInventory.
    Adding read,write permissions for group.
    Removing read,write,execute permissions for world.

    Changing groupname of /u01/app/oraInventory to oinstall.
    The execution of the script is complete.

# /u01/app/oracle/product/11.2.0/db_1/root.sh
    Running Oracle 11g root.sh script...

    The following environment variables are set as:
        ORACLE_OWNER= oracle
        ORACLE_HOME=  /u01/app/oracle/product/11.2.0/db_1

    Enter the full pathname of the local bin directory: [/usr/local/bin]:
       Copying dbhome to /usr/local/bin ...
       Copying oraenv to /usr/local/bin ...
       Copying coraenv to /usr/local/bin ...


    Creating /etc/oratab file...
    Entries will be added to the /etc/oratab file as needed by
    Database Configuration Assistant when a database is created
    Finished running generic part of root.sh script.
    Now product-specific root actions will be performed.
    Finished product-specific root actions.

SQLPLUS NOT STARTING AFTER INSTALL?do the following;
==========================================
[oracle@localhost ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl  (your SID)
[oracle@localhost ~]$ sqlplus / as sysdba

OR
======

[oracle@localhost ~]$ su - oracle
Password:
[oracle@localhost ~]$ sqlplus / as sysdba

setting database to ARCHIVELOG MODE
=====================================================
SQL> startup mount
SQL> alter database archivelog;
SQL> select log_mode,open_mode from v$database;

LOG_MODE     OPEN_MODE
------------ --------------------
ARCHIVELOG   MOUNTED
SQL> alter database open;
SQL> select log_mode,open_mode from v$database;

LOG_MODE     OPEN_MODE
------------ --------------------
ARCHIVELOG   READ WRITE

TO run DBCA,NETMRG and RMAN you need to set the path to ~$ORACLE_HOME/bin as below
========================================================== 
[oracle@localhost ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_2/bin

DBCA
======
[oracle@localhost bin]$ ./dbca

NETMRG
=======
[oracle@localhost bin]$ ./netmrg

RMAN
======
[oracle@localhost bin]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 26 02:43:50 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1365549684)

Starting the Database
===========================
$ sqlplus / as sysdba

SQL>startup

Invalid username and/or password when trying to login to the EM console
==============================================

SQL> alter user sys identified by oracle;
 
User altered.

SQL> alter user system identified by oracle;

User altered.

Then on the login form enter the username and password for user sys or system as above(example)
============================================================
        Username:sys
        Password:oracle
        As    :SYSDBA

Some SQLPlus Querries to test
=============================================

SQL>show parameter v$database
SQL>show parameter v$option
SQL>show parameter db_name
SQL>show parameter service
SQL>show parameter db_unique_name

SQL> alter system register;

sorting library set issues
==============================

[oracle@localhost bin]$ set | fgrep ORA----  check if you have errors



YOU NOW HAVE A DATABASE SET !!!

create a soft link

To create a symbolic link, at the shell prompt

            ln -s {target-filename} {symbolic-filename}

For example create softlink for /u01/app/oracle/flash_recovery_area as /data/backup

will be:

            ln -s /u01/app/oracle/flash_recovery_area  /data/backup

import a CSV datafile using Oracle Loader

# This script will import a CSV datafile

# Remove any previous failure logs
#
rm -f /home/oracle/impt_data/*.log
rm -f /home/oracle/impt_data/*.bad

sqlplus / as sysdba <<-!
set lines 2000
set pages 0
create or replace directory imp_data as '/home/oracle/imp_data';
grant read,write on directory impx_data to myschema;
connect myschema/xxxxx;
alter session set NLS_DATE_FORMAT = "MM/DD/YY";
drop table external_impdata_201255321;
drop table impdata_201255321;

create table external_data_test
("X_NR" NUMBER(*,0),
"X_ORG_FK" NUMBER(*,0),
"X_JAHR" NUMBER(*,0),
"X_MONAT" NUMBER(*,0),
"X_TAG" NUMBER(*,0),
"X_DATUM" NUMBER(*,0),
"X_JAHR_MONAT" NUMBER(6,0),
"X_DATUM_KURZ" NUMBER(8,0),
"X_WOCHE" NUMBER,"DAT_WAIVER" NUMBER(1,0),
"X_OLD_PLAN" NUMBER(10,2),
"X_OVER_PLAN" NUMBER(10,2),
"X_AUTO_WAIVER" NUMBER(1,0))organization  external
(type ORACLE_LOADER default directory mycsv access parameters ( RECORDS DELIMITED BY newline FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"')location ('export.csv')) reject limit unlimited;