Sfaturi Oracle / Tips and tricks OracleDBA

2011-04-19

how to unload a table to a flat text file

HOW TO UNLOAD A TABLE INTO SQL*LOADER FORMAT:

Aflați mai multe »

2011-04-15

fericire

"Noua zecimi din fericirea noastra se bazeaza pe sanatate." Schopenhauer.

"Fiecare imbatraneste asa cum a trait!"

datafile number not processed because file is read-only

Solution : http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/recov002.htm

6.2.1.1 Recovery of Databases with Read-Only Tablespaces

Read-only tablespaces may require special handling in a restore and recover operation. By default, the restore operation will skip read-only tablespaces. If a read-only tablespace is at the SCN where it became read-only after it is restored from backup, no redo will be applied to it when the rest of the database is recovered. You can force RMAN to restore any missing datafiles belonging to read-only tablespaces by using the CHECK READONLY option to the RESTORE command:

RMAN> RESTORE DATABASE CHECK READONLY;
RMAN> RECOVER DATABASE DELETE ARCHIVELOG;

2011-04-14

ORA-32696: HTI: No free slot

Bug 8394351 ORA-32696 from a parallel GROUP BY with no aggregates or COUNT(DISTINCT)

Aflați mai multe »

sql loader manual

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/part_ldr.htm for 10.2

how to use a sequence in sql loader ( sqlldr )

"The SEQUENCE function generates a unique value in the column LOADSEQ. This function finds the current maximum value in column LOADSEQ and adds the increment (1) to it to obtain the value for LOADSEQ for each row inserted."

http://www.pafumi.net/sql_loader.htm#3

example:

  LOAD DATA

   INFILE *

   APPEND

   INTO TABLE emp

   FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

   (empno, ename, job, mgr,

   hiredate DATE(20) "DD-Month-YYYY",

   sal, comm, deptno CHAR TERMINATED BY ':',

   projno,

loadseq SEQUENCE(MAX,1))

There is also a second workaround: http://www.orafaq.com/wiki/SQL*Loader_FAQ
One can also populate columns with static or derived values. However, this only applies for the conventional load path (and not for direct path loads)

LOAD DATA
  INFILE *
  INTO TABLE modified_data
  (  rec_no                      "my_db_sequence.nextval",
     region                      CONSTANT '31',
     time_loaded                 "to_char(SYSDATE, 'HH24:MI')",
     data1        POSITION(1:5)  ":data1/100",
     data2        POSITION(6:15) "upper(:data2)",
     data3        POSITION(16:22)"to_date(:data3, 'YYMMDD')"

how to skip the first and the last record, with sqlldr

Example: you have a text file ( fixed delimited ) and you want to load it with sql loader ( sqlldr command ) . The problem is that this file has a header and a footer

To skip the first line from the source text file is simple ( SKIP=1 ) .. for example you have a header in the file

To skip the last line from the source text file , use the following workaround :
http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/how-to-skip-the-trailor-line-using-sql-loader-635925

Usually, when there is a trailer, there is some sort of record type
indicator. Use this in a condition of loading (I believe it is a WHEN in
SQLLOADER). Suppose your record indicator is in the first position, and is
H[eader], D[etail], T[railer]. You already probably used SKIP to skip the
header, but you can accomplish both by

WHEN (1:1)='D'

There is a pseudocolumn in SQLLOADER that shows the record number; look in
the manual; I believe is it RECORD.

Warning : http://www.orafaq.com/wiki/SQL*Loader_FAQ
SQL*Loader does not allow the use of OR in the WHEN clause. You can only use AND as in the example above! To workaround this problem, code multiple "INTO TABLE ... WHEN" clauses. Here is an example:

LOAD DATA
  INFILE  'mydata.dat' BADFILE  'mydata.bad' DISCARDFILE 'mydata.dis'
  APPEND
  INTO TABLE my_selective_table
  WHEN (01) <> 'H' and (01) <> 'T'
  (
     region              CONSTANT '31',
     service_key         POSITION(01:11)   INTEGER EXTERNAL,
     call_b_no           POSITION(12:29)   CHAR
  )
  INTO TABLE my_selective_table
  WHEN (30:37) = '20031217'
  (
     region              CONSTANT '31',
     service_key         POSITION(01:11)   INTEGER EXTERNAL,
     call_b_no           POSITION(12:29)   CHAR