Very good example.
November 25, 2002 - 10am Central time zone
Reviewer: Leo from Boston, MA
external tables
May 11, 2003 - 7pm Central time zone
Reviewer: A reader from USA
My understanding is that external tables are read only and cannot permit any DML. We cannot create
any indexes or constraints. How external tables will be useful (only for queries that too without
any indexes)? If I used sqlldr, I can load the data into a table that I can use for further DML in
my database. am i missing anything?
apologize for my ignorance. Could you please clarify? thanks.
Followup May 11, 2003 - 8pm Central time zone:
you are using external tables *instead of* sqlldr.
with external tables you can
o merge a flat file with an existing table in one statement.
o sort a flat file on the way into a table you want compressed nicely.
o do a parallel direct path load -- without splitting up the input file, writing
umpteen scripts and so on
o run sqlldr in effect from a stored procedure or trigger (insert is not sqlldr)
o do multi-table inserts
o flow the data through a pipelined plsql function for cleansing/transformation
and so on. they are *instead of* sqlldr -- to get data into the database without having to use
sqlldr in the first place.
You would not normally query them day to day in an operational system, you use them to load data.
Thanks a lot!
May 11, 2003 - 8pm Central time zone
Reviewer: A reader from USA
My 2.5 cents...
May 11, 2003 - 10pm Central time zone
Reviewer: kashif from Houston, TX
Hi Tom,
I've noticed that timings on both SQL Loader and External Table loads (using insert /*+ append */)
are roughly comparable, but the real boost I get is when I need to update data post-load, from
different tables. For example, recently I modified a process which was as follows:
Use sqlldr to upload file to table A.
Update table A using codes from table B and table C.
Now, I simply do:
Rename file to name of external table source (e.g. 123.txt).
Insert into table A from external table. Use previous updates as embedded selects in insert
statements.
The real difference I get is when I can simply select into the target table's column values I would
otherwise have updated after the load, using selects. This way, I have one insert statement, with
all the updates I need to do as part of the same statement, in the form of selects. To give you
some timing numbers, the sqlldr and updates took almost an hour and a half. With the external
tables and the embedded selects the process finishes in about 15 minutes. And it is so much easier
to use, compared to sqlldr.
Kashif
Sqlldr vs Temprary Table
May 12, 2003 - 4am Central time zone
Reviewer: Reaz/Dhaka from Bangladesh
I am using it in a real time project which is underway. Frankly , this new feature of 9i has made
our ETL process lot simpler then it was.
For example, the whole of filtering/cleansing could now be done in flat file without loading it in
database.
This is definetley a powerful feature.
Excellent Example
May 12, 2003 - 7am Central time zone
Reviewer: Abubaker Khered from Jeddah, Saudi Arabia
Some difficulties in doing the example
May 13, 2003 - 4am Central time zone
Reviewer: Abubaker from Jeddah, Saudi Arabia
Hi Tom,
I liked the example above very much, because it is going to make my live a lot easier. But when I
am about to do it I was stuck-up at:
SQL> !wc /tmp/emp.dat
SP2-0734: unknown command beginning "!wc /tmp/e..." - rest of line ignored.
Also is there anyway to check the result of the following command:
SQL> host flat / emp_view > /tmp/emp.dat
Although I have copied the files you mentioned in the above link (flat, sqldr_exp) to the working
directory where I run the sqlplus from.
I am having oracle 92 on NT server.
Would you please explain what I was doing wrong.
Regards,
Abubaker
Followup May 13, 2003 - 7am Central time zone:
windoze does not do "wc", windows does not do !, you must use HOST.
I do have flat and sqlldr_exp for windows here:
http://asktom.oracle.com/~tkyte/flat/index.html
you would of course use windoze file names, not /tmp/emp.dat
External tables and zoned data
June 25, 2003 - 3pm Central time zone
Reviewer: Bob Rich from IN, USA
Tom,
We have an existing application that uses sqlloader to load zoned data from a mainframe. There
seems to be a bug with 9.2 (bug 3 3022089) that is delaying an upgrade. Can an external table be
used for this application?
Followup June 25, 2003 - 8pm Central time zone:
did you try? i have no "zoned data" with which to do so myself.
bug with external tables and ltrim (or lrtrim)
June 26, 2003 - 4am Central time zone
Reviewer: Freek from Belgium
Tom,
When I use the lrtrim or the ltrim keyword in an external table definition,then I get the field too
long for datatype error when the value of the field are spaces.
When I use no trim or rtrim then I don't get this error.
Is this a bug or am I doing something wrong?
thanks
Freek
system@dev01> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
system@dev01> create directory external_table_dir as 'd:\arch\debug';
Directory created.
system@dev01> grant read, write on directory external_table_dir to freek;
Grant succeeded.
system@dev01> @connect freek@dev01
freek@dev01> create table test
2 (
3 test1 varchar2(50),
4 test2 varchar2(50)
5 )
6 organization external
7 (
8 type oracle_loader
9 default directory external_table_dir
10 access parameters
11 (
12 records delimited by newline
13 skip 1
14 fields terminated by 0x'09'
15 lrtrim
16 missing field values are null
17 (
18 test1 char(50),
19 test2 char(50)
20 )
21 )
22 location ('test.txt')
23 )
24 /
Table created.
freek@dev01> select * from test;
select * from test
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
freek@dev01> drop table test;
Table dropped.
freek@dev01> create table test
2 (
3 test1 varchar2(50),
4 test2 varchar2(50)
5 )
6 organization external
7 (
8 type oracle_loader
9 default directory external_table_dir
10 access parameters
11 (
12 records delimited by newline
13 skip 1
14 fields terminated by 0x'09'
15 rtrim
16 missing field values are null
17 (
18 test1 char(50),
19 test2 char(50)
20 )
21 )
22 location ('test.txt')
23 )
24 /
Table created.
freek@dev01> column test1 format a30
freek@dev01> column test2 format a30
freek@dev01> select * from test;
TEST1 TEST2
------------------------------ ------------------------------
test test
test
content test.txt
----------------
test1 test2
test test
test
output from log file
--------------------
LOG file opened at 06/24/03 16:57:29
Field Definitions for table TEST
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
TEST1 CHAR (50)
Terminated by "09"
Trim whitespace from left and right
TEST2 CHAR (50)
Terminated by "09"
Trim whitespace from left and right
KUP-04021: field formatting error for field TEST1
KUP-04026: field too long for datatype
KUP-04101: record 3 rejected in file d:\arch\debug\test.txt
LOG file opened at 06/24/03 16:58:49
Field Definitions for table TEST
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
TEST1 CHAR (50)
Terminated by "09"
Trim whitespace from right
TEST2 CHAR (50)
Terminated by "09"
Trim whitespace from right
content of bad file:
--------------------
test
Followup June 26, 2003 - 9am Central time zone:
appears to be an issue in 9201, cannot reproduce it in 9203. it is caused by the null field
apparently.
suggestion until you get to 9203:
o use a view
organization external
(
type oracle_loader
default directory external_table_dir
access parameters
(
records delimited by newline
skip 1
fields terminated by 0x'09'
missing field values are null
(
test1 char(50),
test2 char(50)
)
)
location ('test.txt')
)
/
create or replace view v
as
select trim( test1 ) test1, trim( test2 ) test2 from test;
to trim.....
Not bad, but there's better ways of doing this
June 30, 2003 - 12pm Central time zone
Reviewer: Nigel Heffernan from ULondon, England
The lowly MS-Access programmers have a slower, but much, much easier method. The Oracle target
table and the source file can both be declared as 'Linked' tables - definitions are created, but no
data is imported in an Access database - and then its just a case of writing (or visually building)
a single JET-SQL 'INSERT...' query.
MS-Access was designed for botched, bodged and cobbled-together data operations. Oracle wasn't, and
it's unreasonable to expect a 'professional' RDBMS to do anything more than simple flat file
imports.
The 9i external table is cool, because data in external files is now part of your data 'world' and
accessible to SQL sorting and searching - but you're never going to get the Oracle database engine
performance until its in a real Oracle table.
For large-scale operations - a million records and upwards - some kind of GREP or VB File I/O and
string-handling should be used to concatenate 'INSERT INTO' statements. These can be sent directly
to the Database using an OracleInProcServer object, and you'll find it's an order of magnitude
faster than the Oracle 8 Loader.
Here's an example:
Option Explicit
Public Function TransferCSV( _
CsvFileName As String, _
HostName As String, _
DbName As String, _
DbPassword As String, _
DestinationTable As String, _
ParamArray DestinationFields()) _
As Long
'Loads comma-separated data to an Oracle table.
'Works faster than JET text ISAM into an ODBC-Linked table
'And can load more than the Excel 65535 row limit
'Timed at 1200 records per second
'SAMPLE USAGE:
'lngRet = TransferCSV( _
' "D:\Program Files\Develop\LoadData.csv", _
' "**HOST**", "**USER**", "**PASSWORD**", _
' "tblPrice", _
' "PriceID", "StockID", "TradingDay", "ClosingPrice", "logChangeOnDay" _
' )
' DestinationFields can be a VBA array, a parameter set, or the boolean value
' TRUE denoting that the first line of the output file specifies the field names
'Author: Nigel Heffernan, Credit Lyonnais Securities Feb 2003
' Heffernann@YAH00.com
'Thanks to Denis Duchene for Oracle OO40 sample VBA code
Dim SQL As String
Dim SQLstub As String
Dim SQLdata As String
Dim iField As Integer
Dim lngProgress As Long
Dim lngFile As Long
Dim strData As String
Const ORADB_NO_REFETCH As Long = &H4&
TransferCSV = False
'Early binding code for performance: requires VBE reference to \Orant\Bin\OIP.tlb
Dim oraSession As OracleInProcServer.OraSessionClass
Dim oraDatabase As OracleInProcServer.oraDatabase
Set oraSession = New OracleInProcServer.OraSessionClass
'Late-Binding code for compatibility across all Oracle clients:
'Dim oraSession As Object
'Dim oraDatabase As Object
'Set oraSession = CreateObject("OracleInProcServer.XOraSession")
'VBA File I/O
lngFile = FreeFile
Open CsvFileName For Input As #lngFile
'SQL stub String-handling: the target is this type of SQL statement:
'SQL = "INSERT INTO tblPrice ( PriceID, StockID, TradingDay, ClosingPrice, logChangeOnDay)
Values ( -2147483641, 1194, 36795, 310, -0.009631 )"
SQLstub = ""
SQLstub = SQLstub & "INSERT INTO " & DestinationTable & " ("
If InStr(TypeName(DestinationFields(0)), "(") > 0 Then
'Its an array of field Names
For iField = LBound(DestinationFields(0)) To UBound(DestinationFields(0))
SQLstub = SQLstub & " " & DestinationFields(0)(iField) & ","
Next iField
ElseIf IsNumeric(DestinationFields(0)) Then
'Use the first row of the file
If DestinationFields(0) = True Then
strData = ""
Line Input #lngFile, strData
strData = ReplaceString(strData, Chr(34), "")
strData = ReplaceString(strData, Chr(39), "")
strData = strData & ","
SQLstub = SQLstub & strData
End If
Else
'It's an element parameter list array
For iField = LBound(DestinationFields) To UBound(DestinationFields)
SQLstub = SQLstub & " " & DestinationFields(iField) & ","
Next iField
End If
'Trim trailing comma
SQLstub = Left(SQLstub, Len(SQLstub) - 1)
SQLstub = SQLstub & " ) Values ( "
Set oraDatabase = oraSession.OpenDatabase(HostName, DbName & "/" & DbPassword,
ORADB_NO_REFETCH)
oraSession.BeginTrans
Do While Not EOF(lngFile)
SQL = ""
strData = ""
Line Input #lngFile, strData
SQL = SQLstub & strData & " )"
oraDatabase.ExecuteSQL SQL
lngProgress = lngProgress + 1
If lngProgress Mod 1000 = 0 Then
oraSession.CommitTrans
oraSession.BeginTrans
'***** Comment this out if your VB host app doesn't support .Statusbar
Application.StatusBar = "Loading to Oracle... " & lngProgress & " records"
'*****
End If
Loop 'EOF(lngFile)
ExitFunction:
Close #lngFile
oraSession.CommitTrans
oraDatabase.Close
Set oraSession = Nothing
TransferCSV = lngProgress
'**** Comment this out if your VB host app doesn't support .Statusbar
Application.StatusBar = "Ready ...Loaded " & lngProgress & " records to Oracle."
'**** MS-Access 97 code is syscmd acsyscmdsetstatus, "Ready ...Loaded " & lngProgress & "
records to Oracle."
End Function
Public Function ReplaceString(InputString, SearchString, InsertString) As String
On Error Resume Next
'Replaces all instances of search string in input string with an insert string
'Regardless of relative lengths of search and input string
Dim i As Integer
Dim iPosition As Integer
Dim strTemp As String
Dim strOutput As String
strOutput = ""
strTemp = ""
iPosition = 1
If SearchString = InputString Then
ReplaceString = InsertString
Exit Function
End If
If InStr(InputString, SearchString) < 1 Then
ReplaceString = InputString
Exit Function
End If
If SearchString = "" Then
ReplaceString = InputString
Exit Function
End If
Do Until iPosition > VBA.Len(InputString)
i = InStr(iPosition, InputString, SearchString)
If i < 1 Then
strOutput = strOutput & VBA.Right(InputString, 1 + VBA.Len(InputString) - iPosition)
Exit Do
End If
strOutput = strOutput & VBA.Mid(InputString, iPosition, i - iPosition) & InsertString
iPosition = i + VBA.Len(SearchString)
Loop
If VBA.Len(strOutput) < 1 Then
strOutput = InputString
End If
ReplaceString = strOutput
End Function
Followup June 30, 2003 - 1pm Central time zone:
i have to believe (hope anyway) that you are being tongue in cheek sarcastic?
Not that I can benchmark your code as it just refused to be compiled on open systems...
But, I see not a bind variable anywhere. Very scary, I wouldn't let that code near my database.
How this is easier then
Insert into T select * from external_table;
I just cannot fathom.
Nigel Heffernan - Benchmarks to prove it?
June 30, 2003 - 2pm Central time zone
Reviewer: Kamal Kishore from New Jersey, USA
<quote>
and you'll find it's an order of magnitude faster
than the Oracle 8 Loader.
</quote>
But, I do NOT see any benchmark test(s) to prove that this load mechanism is "magnitude" faster???
We would like to hear more from the poster on this statement before this can be taken any seriously
or it should be kept aside as a slower and bulky alternative (I do not think this can be
categorized as an alternative, but, for the sake of argument...).
To Nigel...pls take your VB stuff.....
June 30, 2003 - 2pm Central time zone
Reviewer: Robert from PA, USA
>> Not bad, but there's better ways of doing this
Wa..ha..ha..ha...And then there you go entertaining us
with your bit of VB "magic" (got another word for it).
Well, chap you're not entirely hopeless, at least you're doing OO4O - but do it via st. procedure
and use BV for crying out loud.
Tom is too nice a bloke to say this. So I will be bold & say: VB/Acce$$ stuff don't belong here.
Take it to the M$ newsgroups or put it where the sun don't shine.
We dig open source here.
Followup June 30, 2003 - 2pm Central time zone:
I dig open systems.
Yeah, that's what i mean......sorry
June 30, 2003 - 3pm Central time zone
Reviewer: Robert from PA
>..I dig open systems.
Got a bit brainwashed by this GNU/FSF/"Open whatever"/"Peace on Earth"
these days...they all began to mean the same thing to me...
an order of magnitude faster
July 1, 2003 - 7am Central time zone
Reviewer: Nigel Heffernan from London, England
Hmmm... I post an easy fix and I get grief from the 'open source' guys. Me, I think ease-of-use is
more valuable than ideological purity. It's also the reason this board exists: nobody comes here
because of problems with Oracle performance.
Anyway, in answer to the more serious questions on speed, here's the file, fields delimited by
comma, records separated by Chr(10):
"PriceID","StockID","TradingDay","ClosingPrice","logChangeOnDay"
-2147483581,1027,33379,375.90,0.01
-2147483189,1834,35384,10.65,-.00292356
-2147480993,1299,36780,968.00,-0.01
-2147480914,505,36928,80.00,0.03
-2147480587,1021,36879,823.01,0.00120362
You'll need another 3,500,000 records to replicate the live operation and I have no plans to post
them.
PriceID is a primary key, long integer; StockID is a foreign key, indexed, long int; TradingDay is
long int, indexed; the other two fields are single-precision floats.
I had to do this four times: the Oracle 8 loader, when I finally got it working, took 3:35 then
4:10 hours to load it all. Two VB runs took 44 and 46 minutes.
...On NT 4.0. I suspect it would be faster still on any UNIX system, as the file i/o functions are
better than anything available in VB and NT.
Viewed purely from an Oracle perspective, maybe my code doesn't belong here. Viewed from the
perspective of a developer who works with a mix of RDBMS systems, and is constantly asked to deal
with file imports from legacy systems, the answer is: what works, works.
People come here for easy fixes, not for lectures on the evils of MS applications.
I've uploaded similar files to an IBM mainframe, a proprietary trading system and NT SQL Server
databases. I used the same VB script (albeit with ADO, which is slower and less stable than OO40)
and it ran faster than the native text-file import drivers two of those databases, too.
Given the headaches I get dealing with all those proprietary systems, I might have a complete
sense-of-humour failure when told how much people in academia and single-vendor environments dig
'Open Source' solutions. The real world is locked into a balkanised mess, all of us lowly
programmers would like the authority and the budget to replace it, and you can't get there from
here, even by digging.
Followup July 1, 2003 - 9am Central time zone:
people don't come here for performance? kidding right.
anyway - an order of magnitude is 10x, but the math using your numbers is 5x - NOT that sqlldr
would ever take that long -- not even close.
I will say this -- you messed something up hugely in your test. I've loaded millions of records
using sqlldr (in a similar format) and it takes MINUTES, not hours.
Minutes -- not hours. Really, minutes.
To me, the 44minute VB run is way too slow (about 2 orders of magnitude too slow). That should not
take more then a couple of minutes to load up. (and since vb doesn't run on unix, it would be hard
to "test")
You did something seriously big time *wrong*, I would find 44minutes to be about 42 minutes too
long. Here we are -- on a small machine (my desktop PC -- intel based, IDE drives, running RedHat
Linux). I created a flat file:
tkyte@tkyte-pc Desktop]$ head test.dat
1,17602,17602,17602,17602
2,7516,7516,7516,7516
3,23098,23098,23098,23098
4,13926,13926,13926,13926
5,22663,22663,22663,22663
6,10130,10130,10130,10130
loaded it:
[tkyte@tkyte-pc Desktop]$ sqlldr / test data=test.dat direct=y
and the timing is
Table V:
3817192 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 3817192
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 799
Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Tue Jul 01 08:27:37 2003
Run ended on Tue Jul 01 08:30:00 2003
Elapsed time was: 00:02:22.17
CPU time was: 00:00:10.19
The table V was:
ps$tkyte@ORA920> @desc v
Name Null? Type
------------------------------------ -------- -------------------------
C1 NOT NULL NUMBER
C2 NUMBER
C3 NUMBER
C4 NUMBER
C5 NUMBER
Indexes on v
Index Is
Name Unique COLUMNS
------------------------------ ------ --------------------------------
V_IDX1 No C3
V_IDX2 No C5
SYS_C005924 Yes C1
So, very similar to your setup (since numbers are numbers are numbers in Oracle -- everything is a
NUMBER)
4 hours -- not a chance, not a chance at all. Even if I go with the slower conventional path load
(which one would not really do in the real world), the times are no where near 45 minutes.
and it is about open SYSTEMS, not source really. Your VB implementation is something I would avoid
like the plague as I cannot use it anywhere (really -- I could not)
I live in the real world -- but in my real world, that sqlldr would take 4 hours to load a measely
3.5 million records would be a huge red flag that "I've done something horribly wrong" -- not
motivation to write code that can only be slower.
I'm not even sure your machine could parse 3.5 million UNIQUE sql statements in 44minutes -- and if
it did, 99% of your run time was spent KILLING the server -- forcing it to hard parse like that.
Single row inserts
No binds
Ugh
Other part of the load?
July 1, 2003 - 9am Central time zone
Reviewer: Kamal Kishore from New Jersey, USA
Maybe he can post for all of us to see as to how the alternative load approach (that took 4
Hours!!!) was actually performed.
We would like to see that approach as an example to be able to take the posted run times
differences seriously.
I like to see the DBA's face...really....
July 1, 2003 - 5pm Central time zone
Reviewer: Robert from PA
>>......3.5 Mil records
>> Single row inserts
>> No binds
>> Ugh
Nigel, does (any of) your DBA has ANY CLUE that you're doing this ??? Very curious...reall
Maybe you should show & impress him/her like you're trying to convince us "unbelivers".
Go knock her socks off ! :)
Humble pie...
July 2, 2003 - 8am Central time zone
Reviewer: Nigel Heffernan from London
> but in my real world, that sqlldr would take 4 hours
to load a measely 3.5 million records would be a huge red flag that "I've done something horribly
wrong"
I work to a different benchmark: it works and its faster than MS-Access, so I must be doing
something right.
(I look forward to reading the responses to that)
On a more serious note, what are the most common errors in importing data into Oracle?
All replies appreciated
Nigel Heffernan
And finally, a sour note:
> I'm not even sure your machine could parse 3.5 million UNIQUE sql statements in 44minutes -
I doubt it even raised a sweat. Companies nowadays - especially banks - spend a fortune on
high-performance hardware because its cheaper than efficient code. Until it eventually breaks
down...
...Which is why there are so many MS-Access systems out there, fine for the dozen users they
were designed for, until until you're told the extra memory they bought isn't working and discover
50 to 100 users on three continents generating data volumes in the millions, rather than thousands
of records that were originally anticipated.
And that is a matter of commercial interest to Oracle Corporation.
Nowadays more systems are upsized than designed from scratch, and M$ made it almost a single-click
operation to move the back end of an Access system up to SQL Server. Which is unfortunate, because
a well-designed Access system can act as the client to any server and performs significantly better
with Oracle.
Let's face it, the task isn't made easy for us. Years ago I found Sybase a lot more helpful. The
learning curve is very steep and every VB or Access developer who do tries to work out how to do it
is left wondering whether anybody, anywhere, has ever upsized to Oracle before.
Most database administrators have even less knowledge of client design than us front-end developers
have of Oracle coding. The constant complaint is: "Oracle people live in the server room" and
you're lucky if they'll build the tables for you. I've worked in two companies where the DBAs
flatly deny that 'parameter queries' - functions returning a cursor - can be implemented in Oracle
and I suspect that those who *do* know how its done are unaware of the importance of parameter
queries in small desktop databases.
We'll ignore the vocal minority who are resolutely 'open source' and disdain all effluvia from
Redmond - which goes down a treat when most developers have to write the client apps in VB and run
it on NT.
Why complain?
I hate reinventing the wheel. I particularly hate discovering, months later, that square wheels
worked great for sliding things downhill but now reveal fundamental conceptual flaws. It's all very
well saying: "read the introductory manuals again, bozo" but I am beginning to suspect that there
are no part-time Oracle developers, only dedicated experts.
I'll be spending most of next year separating out the back-end and front-end operations of Access
databases, turning them into proper client-server applications. Plus a set of Excel spreadsheets
that act as front ends to MS-Access 'servers' that are approaching the golden gigabyte that marks
complete collapse.
The current situation is that I can get dedicated servers but not an Oracle developer or a training
budget: hardware 'fixes' really are that cheap. Luckily the benchmark is that if it works and runs
faster than Access, nobody's going to complain. Least of all the DBA, given the workload they get.
Lucky too, that Oracle's a lot more stable, even under adverse conditions, than its next-biggest
rival.
Lucky for you, too, that you won't read many gripes like this: most VB developers just give up on
Oracle and choose an intellectually-undemanding solution.
creating external sequence not right?
July 9, 2003 - 12pm Central time zone
Reviewer: David from Dallas
Hi Tom,
Following your example given, I was trying to practice this by myself and there are some problems
that I encountered. I am not sure if your answer really was what you did.
1. creating external_table before creating emp_view will not load emp.dat data into the external
table.
2. The host flat / emp_view > /tmp/emp.dat generated file is a tab diliminated file rather than a
comma diliminated file as it showed in your create external_table syntax. So when create
external_table the data will not be loaded because the flat generate data file is not compabible as
your FIELDS DILIMINATED BY ',' syntax
It took me quite a bit time to figure out the correct sequence and get the correct results. So here
is my question
Was that the exact sequence and syntax in your responds for the question?
Best Regards,
Followup July 9, 2003 - 1pm Central time zone:
that was a straight cut and paste from a sqlplus session, it is exactly what I did.
1) that is correct but so? I didn't query the external table until after I filled up the file.
2) the flat I used myself:
[tkyte@tkyte-pc Desktop]$ flat scott/tiger emp
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,09-DEC-82,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,12-JAN-83,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10
uses commas. the one on the download site does use tabs, you are correct -- sorry about that.
All you needed was a ',' delimited file. sorry about that, the sequence of steps however, is
correct.
Thanks Tom
July 9, 2003 - 5pm Central time zone
Reviewer: David from Dallas
Thanks Tom for clarifying my concern. Now I understand that we don't have to have the emp.dat data
ready when we create external_table.
External tables and EBCDIC
July 10, 2003 - 5am Central time zone
Reviewer: Adrian from UK
Tom
Is there any way of using external tables with EBCDIC files? Our database is ASCII-7. Furthermore,
the files are littered with COMP-3 (packed decimal) fields. So far I've tried the "dd" UNIX command
to convert to ASCII but the COMP-3's get screwed. Our source system is outsourced so to get them to
batch to ASCII rather than EBCDIC is a non-starter (just getting them to make a coffee would cost
us hundreds of thousands, be 9 months late and be wrong anyway).
Any assistance you could offer on this would be invaluable...
Followup July 10, 2003 - 9am Central time zone:
it might look something like this:
CREATE table....
(
X NUMBER,
Y VARCHAR2(25),
Z DATE
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY ....
ACCESS PARAMETERS
(
RECORDS FIXED 80 CHARACTERSET WE8EBCDIC500
READSIZE 1048576
FIELDS LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
....
)
)
location
(
'test.dat'
)
)
if you have that character set installed...
Couldn't find that WE8EBCDIC500 anywhere...
July 10, 2003 - 12pm Central time zone
Reviewer: Adrian Billington from UK
Tom thanks. Used that WE8EBCDIC500 in SQL*Loader all over the place in my last job but couldn't
find the charset name anywhere on the web or in the docs at tahiti/otn. I knew it wasn't simply
"EBCDIC" but just couldn't crack it. Thanks again.
External Table Load - From a file on a different Host
September 9, 2003 - 9am Central time zone
Reviewer: HB from Canada
Hi Tom,
Your explanation was super. I have one simple question, - can we use ET to load data from a
file that is on different machine? If answer is "no" can this is done in future Oracle release.
We currently use SQL*Loader to load data from a VMS machine to Oracle server on Windows 2000 and
would like to use ET?
FTping the files would not be an option as files are big and have do every time there is data
change in the base file.
Thanks
Followup September 9, 2003 - 11am Central time zone:
the file sort of needs to be ACCESIBLE to the server -- think about it, how can Oracle reach out
and grab a file from "just anywhere"???
the files need to be moved over the network regardless right -- (yes, rhetorical question).
whether you do that via FTP or via an nfs mount or a samba share -- doesn't matter. they'll be
going over the network regardless.
Why isn't it working for me ?
October 24, 2003 - 11am Central time zone
Reviewer: Sonali from waltham, ma
Do I need any init.ora parameters ? Am I missing something ?
I have a tab delimited text file and here is the script I wrote after reading this ...
Connect system @emerald;
create directory external_table_dir as 'd:\dumps\test';
grant read, write on directory external_table_dir to sak;
connect sak@emerald;
Create table test_mweborg
(
ORG_ID NUMBER ,
ORG_ITEM_CODE VARCHAR2(15),
ORG_DESCRIPTION VARCHAR2(80),
ORG_LEVEL NUMBER,
ORG_COMPANY_NUMBER NUMBER,
ORG_PAR1 NUMBER,
ORG_PAR2 NUMBER,
ORG_PAR3 NUMBER,
ORG_PAR4 NUMBER,
ORG_PAR5 NUMBER,
ORG_PAR6 NUMBER,
ORG_PAR7 NUMBER,
ORG_PAR8 NUMBER,
ORG_PAR9 NUMBER,
ORG_PAR_ITEM_CODE1 VARCHAR2(15),
ORG_PAR_ITEM_CODE2 VARCHAR2(15),
ORG_PAR_ITEM_CODE3 VARCHAR2(15),
ORG_PAR_ITEM_CODE4 VARCHAR2(15),
ORG_PAR_ITEM_CODE5 VARCHAR2(15),
ORG_PAR_ITEM_CODE6 VARCHAR2(15),
ORG_PAR_ITEM_CODE7 VARCHAR2(15),
ORG_PAR_ITEM_CODE8 VARCHAR2(15),
ORG_PAR_ITEM_CODE9 VARCHAR2(15),
ORG_STATUS NUMBER,
ORG_CREATE_DATE DATE,
ORG_EFFECTIVE_DATE DATE,
ORG_MODIFY_DATE DATE,
ORG_MANAGER NUMBER,
ORG_HISTORY_ID NUMBER
)
organization external
(
type oracle_loader
default directory external_table_dir
access parameters
(
records delimited by newline
skip 1
fields terminated by 0x'09'
lrtrim
)
location ('test_revised_tab.txt')
)
/
I get this error why ?
SQLWKS> select count(*) from test_mweborg
2>
COUNT(*)
----------
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file TEST_MWEBORG_1376_3900.log
OS error The data is invalid.
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
Thanks
Followup October 24, 2003 - 11am Central time zone:
is d:\dumps\test
a) on the windows server the database runs on
b) accessible to oracle itself
at the very least, it seems that the directory is not writable at the OS level for Oracle.
forgot to mention we have 9.2.0.1.0 version
October 24, 2003 - 11am Central time zone
Reviewer: sonali from waltham, ma
Now I get this error
October 24, 2003 - 11am Central time zone
Reviewer: sonali from waltham, ma
SQLWKS> create or replace view v
2> as
3> select * from test_mweborg;
Statement processed.
SQLWKS>
SQLWKS> select * from v where rownuM<2;
ORG_ID ORG_ITEM_CODE ORG_DESCRIPTION
ORG_LEVEL ORG_COMPAN ORG_PAR1 ORG_PAR2 ORG_PAR3 ORG_PAR4 ORG_PAR5 ORG_PAR6
ORG_PAR7 ORG_PAR8 ORG_PAR9 ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO
ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_STATUS
ORG_CREATE_DATE ORG_EFFECTIVE_DATE ORG_MODIFY_DATE ORG_MANAGE ORG_HISTOR
---------- ---------------
-------------------------------------------------------------------------------- ----------
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
---------- --------------- --------------- --------------- --------------- ---------------
--------------- --------------- --------------- --------------- ---------- --------------------
-------------------- -------------------- ---------- ----------
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
I figured out the other error, but then I hit
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
this error, so I created view like you said, but still can't select from that view...Sorry about
the ignorence, but trying this for the 1st time.
Followup October 24, 2003 - 12pm Central time zone:
check out your logs -- by default they are generated into the same directory.
you probably have some bad dates or numbers in there.
I like to start with all VARCHAR2 columns just to see whats where and then add the "real" types in
after the fact.
Thanks..
October 24, 2003 - 12pm Central time zone
Reviewer: sonali from waltham, ma
I think you are right, it has to with data, the thing is I can't figure out what it is ??
drop table test_mweborg;
Create table test_mweborg
(
ORG_ID varchar2(10) ,
ORG_ITEM_CODE VARCHAR2(15),
ORG_DESCRIPTION VARCHAR2(80),
ORG_LEVEL varchar2(10),
ORG_COMPANY_NUMBER varchar2(10),
ORG_PAR1 varchar2(10),
ORG_PAR2 varchar2(10),
ORG_PAR3 varchar2(10),
ORG_PAR4 varchar2(10),
ORG_PAR5 varchar2(10),
ORG_PAR6 varchar2(10),
ORG_PAR7 varchar2(10),
ORG_PAR8 varchar2(10),
ORG_PAR9 varchar2(10),
ORG_PAR_ITEM_CODE1 VARCHAR2(15),
ORG_PAR_ITEM_CODE2 VARCHAR2(15),
ORG_PAR_ITEM_CODE3 VARCHAR2(15),
ORG_PAR_ITEM_CODE5 VARCHAR2(15),
ORG_PAR_ITEM_CODE6 VARCHAR2(15),
ORG_PAR_ITEM_CODE7 VARCHAR2(15),
ORG_PAR_ITEM_CODE8 VARCHAR2(15),
ORG_PAR_ITEM_CODE9 VARCHAR2(15),
ORG_STATUS varchar2(10),
ORG_MANAGER varchar2(10),
ORG_HISTORY_ID varchar2(10)
)
organization external
(
type oracle_loader
default directory external_table_dir
access parameters
(
records delimited by newline
skip 1
fields terminated by 0x'09'
missing field values are null
(ORG_ITEM_CODE VARCHAR2(15),
ORG_DESCRIPTION VARCHAR2(80),
ORG_LEVEL varchar2(10),
ORG_COMPANY_NUMBER varchar2(10),
ORG_PAR1 varchar2(10),
ORG_PAR2 varchar2(10),
ORG_PAR3 varchar2(10),
ORG_PAR4 varchar2(10),
ORG_PAR5 varchar2(10),
ORG_PAR6 varchar2(10),
ORG_PAR7 varchar2(10),
ORG_PAR8 varchar2(10),
ORG_PAR9 varchar2(10),
ORG_PAR_ITEM_CODE1 VARCHAR2(15),
ORG_PAR_ITEM_CODE2 VARCHAR2(15),
ORG_PAR_ITEM_CODE3 VARCHAR2(15),
ORG_PAR_ITEM_CODE5 VARCHAR2(15),
ORG_PAR_ITEM_CODE6 VARCHAR2(15),
ORG_PAR_ITEM_CODE7 VARCHAR2(15),
ORG_PAR_ITEM_CODE8 VARCHAR2(15),
ORG_PAR_ITEM_CODE9 VARCHAR2(15),
ORG_STATUS varchar2(10),
ORG_MANAGER varchar2(10),
ORG_HISTORY_ID varchar2(10)
)
)
location ('test_revised_tab.txt')
)
/
I changed all the numbers to varchar as you suggested.. I keep getting this error now, which does
not generate logs anymore
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "comma, char, date, defaultif,
decimal, double, float, integer, (, nullif, oracle_date, oracle_number, position, raw, recnum, ),
unsigned, varrawc, varchar, varraw, varcharc, zoned"
KUP-01008: the bad identifier was: varchar2
KUP-01007: at line 6 column 35
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
It was generating the logs before, till I changed all NULL ( spelled out words) in the text file to
nothing doing find and replace, because number datatype did not like NULL (word)..
How do I make it work ? What am I missing now ?
Thanks
Sonali
Followup October 24, 2003 - 12pm Central time zone:
don't use varchar2 down at the bottom -- in fact, don't use anything, just list the column names.
There is a very easy way to use External Table--Read Tom Book
October 24, 2003 - 9pm Central time zone
Reviewer: Ajeet from OH.USA
Guys,
If you have used SQLLOADER earlier --Tom has made External table very simple for you --in his
latest book , he has told how to convert sqlloader script to external table..as we all have been
using sqlloader since long--just use that commnad...
it is something like
sqlldr userid/password name.ctl external_table=generate_only...
and you are done..it tells you allmost all you need to know.Also you don;t have to worry about data
types etc..
Just a thought..however.I felt it alot easir than writing a script for External tables specially if
you have file having alot of fields in it...
Thanks
Very good example to help us to understand
October 25, 2003 - 12am Central time zone
Reviewer: Pauline from NY, U.S.A.
Tom,
In order to follow your sample to practice create external
table, I use one of our 9i database to test. Also in order
to create external user, I edit the pfile and then shutdown
db, create spfile to pfile.
The exact things I did :
<dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> export ORACLE_SID=gp9i
<dbdev1:gp9i:/appl/oracle/product/9.2.0.1.0/dbs> sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Oct 24 15:29:50 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 320309648 bytes
Fixed Size 731536 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.
SQL> select name from v$database;
NAME
---------
GP9I
SQL> create or replace directory data_dir as '/tmp/';
Directory created.
SQL> create table external_table
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
( fields terminated by ',' )
location ('emp.dat')
);
Table created.
SQL> sho parameter os_authent_prefix
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
os_authent_prefix string ops$
Now I want to change the parameter for os_authent_prefix and then create
an ORACLE external user, so I do
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
At this moment, I see:
<dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> ls -l *gp9i*
-rw-r--r-- 1 oracle dba 1024 Sep 12 10:40 initgp9i.ora
-rwSr----- 1 oracle dba 1536 Oct 24 15:33 orapwgp9i
-rw-r----- 1 oracle dba 2560 Jul 16 19:08 spfilegp9i.ora
Then I edit initgp9i.ora to add one more line below.
<dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> vi initgp9i.ora
*.os_authent_prefix=""
SQL> create spfile from pfile;
File created.
<dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> ls -l *gp9i*
-rw-r--r-- 1 oracle dba 1047 Oct 24 16:11 initgp9i.ora
-rwSr----- 1 oracle dba 1536 Oct 24 15:33 orapwgp9i
-rw-r----- 1 oracle dba 3584 Oct 24 16:12 spfilegp9i.ora
SQL> startup
ORACLE instance started.
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-03113: end-of-file on communication channel
SQL> shutdown abort
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-03114: not connected to ORACLE
SQL> conn / as sysdba
ERROR:
ORA-03113: end-of-file on communication channel
Now I run into big problem. I can't conn sys to start or shutdown database. The db is generating
gp9i_pmon_N.trc (800k size) every 3-4 minutes and core dump is generating every 5 minutes.
The content for gp9i_pmon_N.trc is something like:
Fri Oct 24 16:38:47 2003
Errors in file /appl/oracle/admin/gp9i/bdump/gp9i_pmon_25647.trc:
ORA-00445: background process "QMN0" did not start after 120 seconds
Fri Oct 24 16:41:56 2003
Restarting dead background process QMN0
By read oracle trace file and see:
/appl/oracle/admin/gp9i/udump/gp9i_ora_6276.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /appl/oracle/product/9.2.0.1.0
System name: SunOS
Node name: dbdev1
Release: 5.8
Version: Generic_108528-13
Machine: sun4u
Instance name: gp9i
Redo thread mounted by this instance: 0 <none>
Oracle process number: 0
6276
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x64, PC:
[0x1027b25b0, 00000001027B25B0]
*** 2003-10-24 17:39:25.526
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [00000001027B25B0] [SIGSEGV] [Address not mapped to
object] [0x000000064] [] []
Current SQL information unavailable - no session.
Now I only can guess the another DBA created this instance from 9.2.0.1.0. But she moved all other
instances to 9.2.0.4.0 and this instance uses spfilegp9i.ora in same $ORACLE_HOME/dbs with other
instance and same environment variables for 9.2.0.4.0 software. So the instance first starup
without problem. Once create spfile from pfile,
the problem happened.
Please help how to stop this instance running. Now I could
not connect sys as sysdba to shutdown. So many trace and core dump files will fill up file system
soon.
As always, thanks for your help.
Pauline
Followup October 25, 2003 - 8am Central time zone:
just exit sqlplus, go back in, shutdown abort, exit sqlplus and startup.
it'll be fine.
you did do this "the hard way"
ops$tkyte@ORA920> show parameter os
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
os_authent_prefix string ops$
....
ops$tkyte@ORA920> alter system set os_authent_prefix='' scope=spfile;
System altered.
sys@ORA920> startup force
ORACLE instance started.
Total System Global Area 190386792 bytes
Fixed Size 451176 bytes
Variable Size 117440512 bytes
Database Buffers 67108864 bytes
Redo Buffers 5386240 bytes
Database mounted.
Database opened.
sys@ORA920>
Very good example to help us to understand
October 25, 2003 - 12am Central time zone
Reviewer: Pauline from NY, U.S.A.
Tom,
In order to follow your sample to practice create external
table, I use one of our 9i database to test. Also in order
to create external user, I edit the pfile and then shutdown
db, create spfile to pfile.
The exact things I did :
<dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> export ORACLE_SID=gp9i
<dbdev1:gp9i:/appl/oracle/product/9.2.0.1.0/dbs> sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Oct 24 15:29:50 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 320309648 bytes
Fixed Size 731536 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.
SQL> select name from v$database;
NAME
---------
GP9I
SQL> create or replace directory data_dir as '/tmp/';
Directory created.
SQL> create table external_table
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
( fields terminated by ',' )
location ('emp.dat')
);
Table created.
SQL> sho parameter os_authent_prefix
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
os_authent_prefix string ops$
Now I want to change the parameter for os_authent_prefix and then create
an ORACLE external user, so I do
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
At this moment, I see:
<dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> ls -l *gp9i*
-rw-r--r-- 1 oracle dba 1024 Sep 12 10:40 initgp9i.ora
-rwSr----- 1 oracle dba 1536 Oct 24 15:33 orapwgp9i
-rw-r----- 1 oracle dba 2560 Jul 16 19:08 spfilegp9i.ora
Then I edit initgp9i.ora to add one more line below.
<dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> vi initgp9i.ora
*.os_authent_prefix=""
SQL> create spfile from pfile;
File created.
<dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> ls -l *gp9i*
-rw-r--r-- 1 oracle dba 1047 Oct 24 16:11 initgp9i.ora
-rwSr----- 1 oracle dba 1536 Oct 24 15:33 orapwgp9i
-rw-r----- 1 oracle dba 3584 Oct 24 16:12 spfilegp9i.ora
SQL> startup
ORACLE instance started.
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-03113: end-of-file on communication channel
SQL> shutdown abort
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-03114: not connected to ORACLE
SQL> conn / as sysdba
ERROR:
ORA-03113: end-of-file on communication channel
Now I run into big problem. I can't conn sys to start or shutdown database. The db is generating
gp9i_pmon_N.trc (800k size) every 3-4 minutes and core dump is generating every 5 minutes.
The content for gp9i_pmon_N.trc is something like:
Fri Oct 24 16:38:47 2003
Errors in file /appl/oracle/admin/gp9i/bdump/gp9i_pmon_25647.trc:
ORA-00445: background process "QMN0" did not start after 120 seconds
Fri Oct 24 16:41:56 2003
Restarting dead background process QMN0
By read oracle trace file and see:
/appl/oracle/admin/gp9i/udump/gp9i_ora_6276.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /appl/oracle/product/9.2.0.1.0
System name: SunOS
Node name: dbdev1
Release: 5.8
Version: Generic_108528-13
Machine: sun4u
Instance name: gp9i
Redo thread mounted by this instance: 0 <none>
Oracle process number: 0
6276
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x64, PC:
[0x1027b25b0, 00000001027B25B0]
*** 2003-10-24 17:39:25.526
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [00000001027B25B0] [SIGSEGV] [Address not mapped to
object] [0x000000064] [] []
Current SQL information unavailable - no session.
Now I only can guess the another DBA created this instance from 9.2.0.1.0. But she moved all other
instances to 9.2.0.4.0 and this instance uses spfilegp9i.ora in same $ORACLE_HOME/dbs with other
instance and same environment variables for 9.2.0.4.0 software. So the instance first starup
without problem. Once create spfile from pfile,
the problem happened.
Please help how to stop this instance running. Now I could
not connect sys as sysdba to shutdown. So many trace and core dump files will fill up file system
soon.
As always, thanks for your help.
Pauline
October 25, 2003 - 9am Central time zone
Reviewer: Pauline from NY,U.S.A.
Tom,
Thanks very much for the response.
Now if I can conn sys in sqlplus then I can do what you said . But now the problem is I can't
connect sys in sqlplus.
<dbdev1:dev3:/appl/oracle/admin/gp9i/bdump> export ORACLE_SID=gp9i
<dbdev1:gp9i:/appl/oracle/admin/gp9i/bdump> sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Oct 25 09:44:14 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> sho user
USER is ""
SQL> conn / as sysdba
ERROR:
ORA-01041: internal error. hostdef extension doesn't exist
This instance generate gp9i_qmn0_N.trc per 5 minutes with 800k and core dump per 5 minutes.
I have no way to stop this instance inside oracle. Can I kill all processes for this instance on
O/S level? We may recreate it later.
Please help me to stop it.
Thank you again.
Followup October 25, 2003 - 10am Central time zone:
i see a mixture of 9204 and 9201 here -- whats going on with that? are you using the RIGHT
software here? I'm thinking "no" maybe.
verify your path, make sure your ORACLE_HOME is set to the 9204 code tree. Make sure your PATH
references that as well.
Yes, you can kill it -- but you've done something wacky with your environments here -- I believe
you are mixing 9201 and 9204 code together with bad results.
Tablespace for External Tables
October 25, 2003 - 10am Central time zone
Reviewer: A reader from OH,USA
Tom:
I have seen that external tables are being created in system tablespace.Can we specify a tablespace
for external tables?
Thanks
Ajeet
Followup October 25, 2003 - 11am Central time zone:
umm, they consume no space IN the database whatsoever (they are external)
They don't "have" a tablespace really. The have no storage inside at all.
But no, you cannot -- but it is 1000% OK since they do not really consume any storage, anywhere
inside of the database
Tom's insight is always helpful
October 25, 2003 - 9pm Central time zone
Reviewer: Pauline from NY,U.S.A.
Tom,
Thanks for your continuing response over the weekend. The another DBA created this instance when
install 9.2.0.1.0.
The $ORACLE_HOME is /appl/oracle/product/9.2.0.1.0/dbs.
Then used same $ORACLE_HOME to install 9.2.0.4.0. and upgraded other instances from 8i to
9.2.0.4.0. except left this one with 9.2.0.1.0 software. So its spfile, pfile all sitting in
/appl/oracle/product/9.2.0.1.0/dbs which is really pointing to 9.2.0.4.0 software. That is why it
used mixture code from 9.2.0.1 and 9.2.0.4 and once we touched
spfile, we ran into trouble. Now I only can stop it by kill -9 for all processes of this instance.
Thanks very much for your help.
One BIG difference is missed
October 26, 2003 - 9am Central time zone
Reviewer: Olesksandr Alesinskyy from Geramny
Ther IS one really big difference between SQL*Loader abd external tables - with SQL*Loader files
with data may be loaded to a DB directly from client but to be used as exteranal tables they have
to reside on the server (or on network share, mounted on the server).
Import Just Data
November 4, 2003 - 8pm Central time zone
Reviewer: Fred from irvine, ca
As merely a data loader, how does import stack up against external tables? Or is this comparing
apples and oranges?
Followup November 5, 2003 - 8am Central time zone:
apples to toaster ovens.
import can only process a dmp file created by export.
external tables can handle regular old fashioned flat files (but not an export dmp file in 9i and
before)
External tables and EBCDIC
December 18, 2003 - 5pm Central time zone
Reviewer: David Piazza from San Mateo, CA
I have a file transferred from a db2 db on the mainframe. The transfer was selected as ASCII. The
data file is as follows:
212^ASTANDARD
I created an external table with:
drop table test_load;
CREATE TABLE test_load
(ZIP3_CODE VARCHAR2(3)
,FAC_ID INTEGER
,BOX_TYPE_CODE VARCHAR2(10))
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY test_etl
ACCESS PARAMETERS
( records delimited by newline
CHARACTERSET WE8EBCDIC500
FIELDS LDRTRIM
missing field values are null
(ZIP3_CODE CHAR(3)
,FAC_ID CHAR(1)
,BOX_TYPE_CODE CHAR(10)))
LOCATION ('TEST.DAT')) nologging;
The table created sucessfully, but when I select from it, I get:
SQL> select * from test_load;
select * from test_load
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
The log file says:
error processing column FAC_ID in row 1 for datafile /data/TEST.DAT ORA-01722: invalid number
What did I do wrong??
Followup December 18, 2003 - 6pm Central time zone:
the data isn't in ebcdic but your control file says it is?
also "^" doesn't seem to be a number?
Loading Numbers
January 15, 2004 - 2pm Central time zone
Reviewer: Martin Guillen from Buenos Aires, Argentina.
Tom:
I'm trying to load some data in the db from a flat file.
I've done some tests:
Test 1:
ordbas01 > connect load_test/load_test;
Connected.
ordbas01 > create table t (a number);
Table created.
oracle@martin:/opt/oracle/dba/load_test> cat data.dat
3,640
Here ',' is the decimal separator (the character used in spanish languages)
oracle@martin:/opt/oracle/dba/load_test> cat load_test.ctl
load data
infile '/opt/oracle/dba/data.dat'
into table t
fields terminated by whitespace
(
a
)
oracle@martin:/opt/oracle/dba/load_test> sqlldr load_test/load_test control=load_test.ctl
SQL*Loader: Release 9.2.0.1.0 - Production on Thu Jan 15 12:44:55 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 1
And from the sqlldr log:
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
A FIRST * WHT CHARACTER
Record 1: Rejected - Error on table T, column A.
ORA-01722: invalid number
Table T:
0 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
So:
oracle@martin:/opt/oracle/dba/load_test> export NLS_NUMERIC_CHARACTERS=',.'
oracle@martin:/opt/oracle/dba/load_test> sqlldr load_test/load_test control=load_test.ctl
SQL*Loader: Release 9.2.0.1.0 - Production on Thu Jan 15 12:45:48 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 1
And now the log says:
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
A FIRST * WHT CHARACTER
Table T:
1 Row successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
ordbas01 > select * from t;
A
----------
3,64
Test 2:
ordbas01 > l
1 create table t_ext
2 (
3 a number
4 )
5 organization external
6 (
7 type oracle_loader
8 default directory load_test_data
9 access parameters
10 (
11 records delimited by newline
12 badfile load_test_data:'load_test_ext.bad'
13 logfile load_test_data:'load_test_ext.log'
14 fields terminated by whitespace
15 missing field values are null
16 (
17 a
18 )
19 )
20 location ('data.dat')
21 )
22* reject limit unlimited
ordbas01 > /
Table created.
ordbas01 > select * from t_ext;
no rows selected
And load_test_ext.log says:
A CHAR (255)
Terminated by whitespace
Trim whitespace same as SQL Loader
error processing column A in row 1 for datafile /opt/oracle/dba/load_test/data.dat
ORA-01722: invalid number
ordbas01 > select * from nls_session_parameters where parameter='NLS_NUMERIC_CHARACTERS';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NUMERIC_CHARACTERS ,.
1* select * from nls_instance_parameters where parameter='NLS_NUMERIC_CHARACTERS'
ordbas01 > /
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NUMERIC_CHARACTERS ,.
Whats wrong with the external table?
I think that the value of NLS_NUMERIC_CHARACTERS being ',.' in instance/session should be fine but
look:
1* select * from nls_database_parameters where parameter='NLS_NUMERIC_CHARACTERS'
ordbas01 > /
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NUMERIC_CHARACTERS .,
Could the value of NLS_NUMERIC_CHARACTERS in nls_database_parameters be the problem?
From the docs (Oracle 9i Globalization Support Guide Page 3-9):
"NLS Data Dictionary Views
(...)
NLS_DATABASE_PARAMETERS shows the values of the NLS parameters that were used when the database was
*created*."
So the only way to change the values in nls_database_parameters is recreating the db?
Thank you tom for your time,
Martin.
PS: My system is UnitedLinux 1.0 (SuSE Linux Enterprise Server) with Oracle 9i Release 2 (9.2.0.1).
Followup January 16, 2004 - 12am Central time zone:
the docs for external tables do say:
.....
Default Character Sets and Date Masks
For fields in a datafile, it is the client s NLS environment that determines the default character
set and date masks. For fields in external tables, it is the server s NLS environment that
determines the default character set and date masks.
......
one option would be to use a varchar2(n) field instead of number and using "to_number()" in the
select list.
Loading dates using External tables
March 15, 2004 - 3am Central time zone
Reviewer: Duncan
Tom. Thanks for all the advice on the new 9i external table feature.
I just ahve one quick question regarding the loading of dates.
In my control file (.csv) the date field exists in the format of dd/mm/yy. I set up my external
table as follows:
CREATE TABLE External_Table_HR
(
S_NUMBER VARCHAR2(8 BYTE),
NAME VARCHAR2(100 BYTE),
SEX VARCHAR2(2 BYTE),
DEPTID VARCHAR2(6 BYTE),
DEPT_DESCR VARCHAR2(100 BYTE),
GEO_LOCATION VARCHAR2(100 BYTE),
DESCR VARCHAR2(100 BYTE),
FULL_PART VARCHAR2(1 BYTE),
STD_HRS_WK NUMBER,
GRADE VARCHAR2(30 BYTE),
GRADE_DESCR VARCHAR2(30 BYTE),
DATA_DATE DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATA_DIR
ACCESS PARAMETERS
( records delimited by newline
NOLOGFILE
BADFILE DATA_DIR:'EXTHR.bad'
skip 1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
)
LOCATION (DATA_DIR:'EXTHR.csv')
)
REJECT LIMIT 0
LOGGING
NOCACHE
NOPARALLEL;
With the date in the control file in the format it is, it will not load into the external table and
an exception is generated. When i change the date in the control file to an Oracle standard format
it works fine.
The question i have is this. When using SQl loader you could specify the date mask to be used which
would solve my problem. I am having a bit of difficulty getting a similar thing to work using
external tables. could you possibly provide me with an example of how I could set a date mask to
get round this problem I am having.
Thanks for any help
Followup March 15, 2004 - 7am Central time zone:
Say you had this ctl file t.ctl:
load data
INFILE x.dat
INTO TABLE t
APPEND
FIELDS TERMINATED BY ',' trailing nullcols
(a date "dd/mm/yy",
b,
c,
d,
ID "s.nextval")
and you wanted "external table" from it.. just run
$ sqlldr u/p t.ctl external_table=generate_only
you'll find in t.log:
CREATE TABLE "SYS_SQLLDR_X_EXT_T"
(
A DATE,
B NUMBER(38),
C NUMBER(38),
D NUMBER(38),
ID VARCHAR(255)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'x.bad'
LOGFILE 't.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
A CHAR(255)
TERMINATED BY ","
DATE_FORMAT DATE MASK "dd/mm/yy",
B CHAR(255)
TERMINATED BY ",",
C CHAR(255)
TERMINATED BY ",",
D CHAR(255)
TERMINATED BY ",",
ID CHAR(255)
TERMINATED BY ","
)
)
location
(
'x.dat'
)
)REJECT LIMIT UNLIMITED
which also shows you how to do what you ask. I find that using external_table with sqlldr is an
easy way to take working ctl files and make them into external table creates...
Nice Topic
March 31, 2004 - 5am Central time zone
Reviewer: Srinivas from India
Hi Tom,
Can you Please tell me how to load Data in Excel which is spread across different sheets into
oracle tables.
For Example Sheet1 needed to be loaded into emp,sheet2 needed to be Loaded into Dept.
Please Help me and Thanks in Advance.
Followup March 31, 2004 - 8am Central time zone:
sorry - i know next to nothing about excel, specially when it comes to multiple sheets.
External tables in 10g
April 19, 2004 - 11pm Central time zone
Reviewer: A reader from UK
Tom,
Is there anything new to know for external tables in 10g?
Thanks
Followup April 20, 2004 - 8am Central time zone:
sure, they go the other way:
ops$tkyte@ORA10G> create or replace directory tmp as '/tmp'
2 /
Directory created.
ops$tkyte@ORA10G> pause
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create table all_objects_unload
2 organization external
3 ( type oracle_datapump
4 default directory TMP
5 location( 'allobjects.dat' )
6 )
7 as
8 select * from all_objects
9 /
Table created.
ops$tkyte@ORA10G> pause
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> !head /tmp/allobjects.dat
c��!g���~ݩ����0BA ʏ
33; F�p"".""Linuxi386/Linux-2.0.34-8.1.0WE8ISO8859P1LBB EMB GHC JWD SD EBE WMF DDG JG SJH
SRH JGK CL EGM BJM RAP RLP RP KR PAR MS MRS JLS CET HLT��$$
�F��!F�h <?xml version="1.0"?>
<ROWSET>
<ROW>
<STRMTABLE_T>
<VERS_MAJOR>1</VERS_MAJOR>
<VERS_MINOR>0 </VERS_MINOR>
<VERS_DPAPI>3</VERS_DPAPI>
<ENDIANNESS>0</ENDIANNESS>
<CHARSET>WE8ISO8859P1</CHARSET>
ops$tkyte@ORA10G> pause
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select count(*)
2 from all_objects_unload
3 /
COUNT(*)
----------
47304
you can pick up that file and plop it down on another system and there you go. the defining query
can be pretty much anything (joins, aggregates, etc).
External table placed in another machine
April 21, 2004 - 1am Central time zone
Reviewer: RK from India
Tom,
If oracle runs on a unix server, it is possible to create a directory which resides in a windows
machine in the same network?
To be clear once again, should the external table be in the database server?
If need not be, how will you make the oracle server to know that the external table is in another
server in a unix database server + windows file server in the same network?
Followup April 21, 2004 - 7pm Central time zone:
if the windows filesystem is mounted to the unix box sure.
the external table, read by the server, needs to be VISIBLE to the server..
mount the windows disk on the unix machine.
ET from different Host
June 3, 2004 - 4pm Central time zone
Reviewer: A reader
Hi Tom,
Can we use ET to extract data from a differnt Host for an example let say the database is on
Host1(nt) and feeder file to load is on Host2 (VMS)
I tried to use NFS but without luck?
Also heard that in 10g ET can do write besides read ?
why do i get the error
June 14, 2004 - 11am Central time zone
Reviewer: Rahul from Delhi
Hi Tom,
I was trying the external table feature.
I first created the directorys and granted the appropriate read/write permissions on the dir to the
user. Then i created the table it gives me a reply table created but when i try to query the table
i get the following error.
The following error has occurred:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "badfile": expecting one of: "exit, fields, records"
KUP-01007: at line 1 column 1
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
my create table stabe ment is as follows.
create table load_recon
( oid number(16),
ticket_number varchar2(10),
pnr_number varchar2(10))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATA_DIR
ACCESS PARAMETERS
(
badfile bad_dir:'loader.bad'
logfile log_dir:'loader.log'
records delimited by newline character set us7ascii
fileds terminated by ' '
missing filed values are null
(
oid,ticket_number,pnr_number
)
)
LOCATION ('data.dat')
)
PARALLEL
REJECT LIMIT UNLIMITED;
I have checked the previous responces and i have not made any of the mistakes which were listed.
Now my question is if the table is created then why do i get this error only while querying it.
Is it not able to detect the end of records in the file? if so why should i do?
Please help.
Thank you.
Rahul.
Followup June 14, 2004 - 12pm Central time zone:
there are quite a few obvious typos in there.
fileds, filed.
character set is to be a single word characterset
records delimited by comes before badfile
OID is a reserved word to sqlldr....
so, after fixing that:
ops$tkyte@ORA9IR2> create table load_recon
2 ( oid number(16),
3 ticket_number varchar2(10),
4 pnr_number varchar2(10)
5 )
6 ORGANIZATION EXTERNAL
7 (TYPE ORACLE_LOADER
8 DEFAULT DIRECTORY DATA_DIR
9 ACCESS PARAMETERS
10 (
11 records delimited by newline characterset us7ascii
12 badfile bad_dir:'loader.bad'
13 logfile log_dir:'loader.log'
14 fields terminated by ' '
15 missing field values are null
16 (
17 "OID",
18 ticket_number,
19 pnr_number
20 )
21 )
22 LOCATION ('data.dat')
23 )
24 PARALLEL
25 REJECT LIMIT UNLIMITED;
Table created.
ops$tkyte@ORA9IR2> select * from load_recon;
no rows selected
Thanks a lot
June 14, 2004 - 1pm Central time zone
Reviewer: rahul from Delhi
What to say i made some awesome mistakes but simple typos but thanks for showing it.
But i didn't knew i have to give the 'records delimited ' before badfile and why do we have to give
oid in double quotes? is it because its a number field.
Thank you very much for your fast and accurate responce.
Rahul.
Followup June 14, 2004 - 1pm Central time zone:
OID is a reserved word is why, you have to quote it to say "ignore the reserved word, this is my
column name"
Why do CTAS is FAST
July 21, 2004 - 12pm Central time zone
Reviewer: Js from India
hi,
Sir, Why do CTAS is Fast than /*+APPEND*/ in first
post by you ? My assumption is /*+APPEND*/ should
same [ in noarchive ] and fast in [ archive mode if
CTAS using with logging ].
Thanks,
Js
Followup July 21, 2004 - 2pm Central time zone:
append doesn't affect logging really -- only if the TABLE itself were nologging.
in archivelog mode, both CTAS and insert /*+ append */ generate redo normally.
it is UNDO they skip.
CTAS, not having a pre-existing table to deal with, worry about, work with, can take a short cut or
two that insert /*+ append */ does not. realize -- this is a very very small example here -- you'd
have to scale this way up to see if "CTAS is 2x as fast as append" or "CTAS saves a second or two
over append". Ultimately, you'll find them to be "about the same"
give it a go, the entire test case is read to run and easily scaled up.
Re:
July 22, 2004 - 5am Central time zone
Reviewer: A reader
Thanks for your reply. Does it mean .. There is no
Diff. in CTAS and APPEND in any case.
Thanks,
Js
Followup July 22, 2004 - 7am Central time zone:
there are subtle differences you cannot see but as far as undo and redo generation go - they are
pretty much the same.
Thanks Sir...
July 22, 2004 - 12pm Central time zone
Reviewer: Js from India
:)
SQL functions
July 22, 2004 - 3pm Central time zone
Reviewer: A reader
I currently have SQL functions applied to my columns to say divide a number by 1000 or something.
This way my staging table gets the data I want.
How can I do this with external tables? Load everything as varchar2 and do all the conversions in
the SQL that selects from this table?
Ideally, I want to keep these trivial transformations as part of my external table DDL itself so
that no one else has to know about them.
For example, one of my legacy input files sends us float numbers without the decimal point, typical
Cobol stuff. In my controlfile I have a ":field/10000" function to convert it on the way into the
staging table
When I move to using external tables, is there a way I can put this field/10000 division right in
the external table DDL?
Thanks
Followup July 23, 2004 - 8am Central time zone:
think "views"
create your external table.
create a view that performs f(x) on anything you want.
use the view.
Thanks for all your help on external tables!!
July 23, 2004 - 7am Central time zone
Reviewer: Duncan
Tom
firstly i would like to thank you for all your help so far on external tables!
We use them pretty extensivly on a day to day basis and the problem i am having is one of
performance.
One procedure we have is:
insert into table 1 (select * from ext_tab)
which is a very common practice but we perform a lot of data transformation on the data we select
from the external tables. We are using several DECODES on concatanated columns and joins(+) to
stored tables etc.
Some of these queries can take a while to run and i was just curious if you had and rules of thumb
when performing a lot of data transformation from external tables before loading it into a table in
the database?
Thanks for any help
Followup July 23, 2004 - 9am Central time zone:
use them as a table that will be full scanned. use generous hash area sizes if you are doing lots
of joins, use dbms_stats to tell oracle about how large the external table is (or use dbms_stats to
actually analyze the external table!)
Direct path
August 1, 2004 - 11pm Central time zone
Reviewer: A reader
In earlier versions, direct path load using sqlldr was the fastest way to load data into a table,
but the restriction was that SQL functions couldnt be applied to the columns in the controlfile.
This was because direct path load created pre-formatted data blocks and wrote them directly to the
datafile, bypassing the SQL layer.
This is no longer a restriction, both insert /*+ append */ and external tables perform as fast or
faster than the old direct path load and can do SQL functions.
What changed to allow this? If direct path load now invokes the SQL engine, how is it still faster
than non-direct path load?
Thanks
Followup August 2, 2004 - 7am Central time zone:
insert /*+ append */ was always a direct path load running in the sql engine. until external
tables -- insert append could only go table to table so the only way to LOAD data using direct path
operations was -- well -- sqlldr.
so, the addition of external tables opened up the ability to use SQL to load data, and SQL could
always direct path -- so you get a direct path load using sql and all of it's functionality.
Direct path load
August 2, 2004 - 10am Central time zone
Reviewer: A reader
Didnt think about it that way, you are right of course.
But my question was really, what makes direct path load (via /*+ append */, or sqlldr direct=y or
external tables, whatever) faster than non-direct path load?
If direct path load is always faster than non-direct path and there are no
restrictions/limitations of the former over the latter, why isnt direct-path load the default
mechanism?
If both of them go thru the SQL engine, what makes the direct path faster than the regular path?
[Earlier, the direct path load skipped the SQL engine and wrote pre-formatted blocks, yadda, yadda]
Thanks
Followup August 2, 2004 - 10am Central time zone:
direct path is faster because:
o you bypass the buffer cache
o we format database blocks directly
o we bypass traditional space mgmt and do it faster
o we bypass undo generation
o it can bypass redo
direct path loads don't use existing free space (that is one major reason they are not the
"default")
direct path loads don't let you read the table until you commit (another major reason). since we
don't have undo, the consistent read mechanism is somewhat "defeated"
direct path loads can skip redo generation as well, that is *bad* by default.
access to oracle
August 3, 2004 - 4pm Central time zone
Reviewer: A reader
Hi Tom,
can we use external tables for exporting an access database to oracle? can u pls explain with an
example?
Thanks.
Staging tables
August 6, 2004 - 11pm Central time zone
Reviewer: A reader
Currently, we use sqlldr to load files into a staging table, validate them, upsert into the main
table and log errors into a error table. The error table has a FK pointing back to the staging
table so that the full context of the error can be seen.
Since the staging area is purged with some frequency, say 10 days, I have access to all the
good/bad records, errors, for 10 days.
If I conver this to use external tables and get rid of my persistent staging tables, then I lose
the ability to query previously loaded files for troubleshooting. I only have access to the
file/table that is there right now.
Any way to have my cake and eat it too?!
Followup August 7, 2004 - 10am Central time zone:
purge your file area with the same frequency i suppose?
not sure why this is any different?
Let me take this paragraph and change words:
If I conver this to use REAL tables and get rid of my persistent staging
FILES, then I lose the ability to query previously loaded TABLES for
troubleshooting. I only have access to the table/file that is there right now.
Not quite
August 7, 2004 - 11am Central time zone
Reviewer: A reader
"If I conver this to use REAL tables and get rid of my persistent staging FILES, then I lose the
ability to query previously loaded TABLES for troubleshooting. I only have access to the table/file
that is there right now. "
Thats not true, if I use REAL tables, I DONT lose the ability to query previously loaded tables,
everything is right there in the tables.
But instead if I keep my files lying around on the filesystem, everytime I need to generate an
error report, I would be implicitly loading the file again, just for the silly error report.
Also, sometimes the staging record errors out because it has a "as yet unknown account" for
example. Since the staging table is persistent, I just mark that record as 'processed=N' and the
next run (possibly with a different file) picks that up too. I CANNOT do this with external tables
since my "staging" table only points to the current file.
Thanks
Followup August 7, 2004 - 1pm Central time zone:
and by cracky -- everything can be in your files. seems to be a "management" issue here.
seems your error report is "data", and as such would be stored in a database table?
but at the end of the day -- you do what "makes sense". the is the only rule here -- common sense
prevails, you do what makes the most sense in your environment. if you don't *want* to use
external tables (although they do make loading of your "stage" easier perhaps) and have good
reasons (or hey, just don't want to) - you don't have to.
do what makes sense, what makes your life easiest, what gets the job done.
Loading data into oracle (9.x)
September 2, 2004 - 6am Central time zone
Reviewer: Abhivyakti from Pune, Maharashtra, India
Hi Tom,
Your answers/suggestions/alternatives are always good to read and in fact a technical joy for a
learner like me.
I wud like to ask u regarding loading data in oracle, i mean, how wud u compare sqlldr , external
tables and a function (in Pro C) that loads - INSERTs the data into oracle??
which one of these wud be the fastest??
Abhi
Followup September 2, 2004 - 9am Central time zone:
u?
wud?
keyboard failing? it is losing vowels -- and even some other characters....
I would use external tables first and foremost, in all cases. If I had to (was forced to) load
over the network, i would use sqlldr. Using Pro*C would be my LAST choice, if even considered.
Loading data into oracle
September 3, 2004 - 1am Central time zone
Reviewer: Abhivyakti from Pune, Maharashtra, India
Hi Tom,
Thanks for the reply, Well sorry for "u" and "wud" as i am much used to use that.
Well...In case of Pro*C function - Why would you choose it last? for what reasons?? I mean - in
what ways its going to be "costly" than ET or sqlldr??
Sorry for ignorance...
regards
Abhi
Followup September 3, 2004 - 9am Central time zone:
(i know, lots of people are starting to treat IM'ism or SMS messaging acronyms as words, as written
language -- in email, in writing, in everything. I think they are appropriate in IM or SMS, they
are not appropriate in written media. So, I just point it out when I see it)
Lets see, write a program, compile program, debug program, maintain program, enhance program,
upgrade program, .......
or
insert into table select * from external_table;
It is an ease of use thing, I write code if and only if I *have* to write code. Writing an
application to do something that can be done in a single line of SQL is something I abhore.
Call NotePAD data by the SQL query
September 5, 2004 - 5am Central time zone
Reviewer: Tanweer from India
Call NotePAD data by the SQL query
How I Display notePAD contains into SQL> Prompt
Is it possible to display notepad contains directly into the SQL> Prompt
7369|MR. SMITH|CLERK|7902|17-12-80|800|null|20
7499|Mr. ALLEN John|SALESMAN|7698|20-02-81|1600|300|30
7521|WARD|SALESMAN|7698|22-02-81|1250|500|null|30
etc.........
Thanks
Followup September 5, 2004 - 10am Central time zone:
what the heck is "notepad" data. not sure at all what you are asking there.
external table error
March 31, 2005 - 11am Central time zone
Reviewer: A reader
Hi Tom,
I am trying to use external table to load data into a table. I have done the following steps:
1. The source data is in SQL Server and I exported it into a dump file on the Oracle Server
(c:\exp\data.out)
2. In the Oracle Server created an external table as
create table external_table
(
id number(10),
lastname char(20),
firstname char(20),
env char(1)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
( fields terminated by ',(')
location ('data.out')
)
/
where data_dir is
create or replace directory data_dir as 'c:\exp';
When I do a select count(*) from external_table;
I am getting
select count(*) from external_table
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04020: found record longer than buffer size supported, 524288, in
c:\exp\data.out
ORA-06512: at "SYS.ORACLE_LOADER", line 52
ORA-06512: at line 1
I checked the log file and it says
Fields in Data Source:
ID CHAR (255)
Terminated by ",("
Trim whitespace same as SQL Loader
LASTNAME CHAR (255)
Terminated by ",("
Trim whitespace same as SQL Loader
FIRSTNAME CHAR (255)
Terminated by ",("
Trim whitespace same as SQL Loader
ENV CHAR (255)
Terminated by ",("
Trim whitespace same as SQL Loader
KUP-04020: found record longer than buffer size supported, 524288, in c:\exp\data.out
KUP-04053: record number 1
Pls help.
Thanks.
Followup March 31, 2005 - 12pm Central time zone:
are there newlines in this file? how big is the biggest record?
March 31, 2005 - 12pm Central time zone
Reviewer: A reader
There should be newlines in the file. I used SQL Server's bcp out to export the data. I did not
explicitly specify any row terminator. The env is only a one char value for all records.
Thanks.
Followup March 31, 2005 - 12pm Central time zone:
can you edit it and verify?
May 5, 2005 - 5am Central time zone
Reviewer: Anshul Deo from Bangalore, India
Hi Tom,
I am rewriting some scripts we currently have that use SQL Loader to use external tables instead.
The one I am currently looking at has three different types of records in the data file to be
imported. These records are position
delimited rather than comma delimited and each of the three record types have different columns.
The record types are distinguished by the first three characters which make up the first column for
each record.
The SQL Loader script uses the WHEN clause, one of the SQL Loader scripts is as follows:
LOAD DATA
REPLACE
--
-- Agency Header Record
--
INTO TABLE GESB_IMPORT_HDR_TMP
WHEN record_identifier = 'AHD'
(
record_identifier position(001:003) char,
remitting_group position(004:009) char,
effective_date position(010:019) date "DD/MM/YYYY"
)
How can I do something similar in external tables so that I can reference the same flat file in
three separate create external table statements but only look at the records I want in each case?
I have rewritten the above example to get the following:
create table GESB_LOAD_TEST1 (RECORD_IDENTIFIER VARCHAR2(3),
REMITTING_GROUP VARCHAR2(6),
EFFECTIVE_DATE DATE)
ORGANIZATION EXTERNAL (TYPE oracle_loader
DEFAULT DIRECTORY STAGING_EXTERNAL_LOAD
ACCESS PARAMETERS(RECORDS FIXED 19
LOGFILE 'GESB_LOAD_TEST1.log'
BADFILE 'GESB_LOAD_TEST1.bad'
FIELDS(RECORD_IDENTIFIER CHAR(3),
REMITTING_GROUP CHAR(6),
EFFECTIVE_DATE CHAR(10)
DATE_FORMAT DATE MASK "DD/MM/YYYY"))
LOCATION ('GESB_TEST1.lis')) REJECT LIMIT UNLIMITED;
Thanks,
Anshul.
Followup May 5, 2005 - 7am Central time zone:
I took your ctl file, ran
$ sqlldr / t.ctl external_table=generate_only
and sqlldr says.....
CREATE TABLE "SYS_SQLLDR_X_EXT_GESB_IMPORT_H"
(
"RECORD_IDENTIFIER" VARCHAR2(3),
"REMITTING_GROUP" VARCHAR2(10),
"EFFECTIVE_DATE" DATE
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
LOAD WHEN ("RECORD_IDENTIFIER" = "AHD")
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'t.bad'
LOGFILE 't.log_xt'
READSIZE 1048576
FIELDS LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"RECORD_IDENTIFIER" (1:3) CHAR(3),
"REMITTING_GROUP" (4:9) CHAR(6),
"EFFECTIVE_DATE" (10:19) CHAR(10)
DATE_FORMAT DATE MASK "DD/MM/YYYY"
)
)
location
(
't.dat'
)
)REJECT LIMIT UNLIMITED
Edit that up a little bit and:
ops$tkyte@ORA10G> create or replace directory dir as '/tmp'
2 /
Directory created.
ops$tkyte@ORA10G> !echo AHD45678901/01/2001 > /tmp/t.dat
ops$tkyte@ORA10G> !echo AxD45678901/01/2001 >> /tmp/t.dat
ops$tkyte@ORA10G> drop table t;
Table dropped.
ops$tkyte@ORA10G> CREATE TABLE "T"
2 (
3 "RECORD_IDENTIFIER" VARCHAR2(3),
4 "REMITTING_GROUP" VARCHAR2(10),
5 "EFFECTIVE_DATE" DATE
6 )
7 ORGANIZATION external
8 (
9 TYPE oracle_loader
10 DEFAULT DIRECTORY DIR
11 ACCESS PARAMETERS
12 (
13 RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
14 LOAD WHEN ("RECORD_IDENTIFIER" = "AHD")
15 BADFILE 't.bad'
16 LOGFILE 't.log_xt'
17 READSIZE 1048576
18 FIELDS LDRTRIM
19 REJECT ROWS WITH ALL NULL FIELDS
20 (
21 "RECORD_IDENTIFIER" (1:3) CHAR(3),
22 "REMITTING_GROUP" (4:9) CHAR(6),
23 "EFFECTIVE_DATE" (10:19) CHAR(10)
24 DATE_FORMAT DATE MASK "DD/MM/YYYY"
25 )
26 )
27 location
28 (
29 't.dat'
30 )
31 )REJECT LIMIT UNLIMITED
32 /
Table created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from t;
REC REMITTING_ EFFECTIVE
--- ---------- ---------
AHD 456789 01-JAN-01
May 10, 2005 - 4pm Central time zone
Reviewer: Mark from NY
Hi Tom,
We have a client who supplies us with a spreadsheet, containing a date column in the format
YYY/MM/DD. To get the year indicated by YYY, we add 1900 to it. For example, if they give us
104/03/01, we have to convert that to 03/01/2004 (mm/dd/yyyy). 99/08/20 becomes 08/20/1999
(mm/dd/yyyy). I suspect this was their quick-fix around the Y2K problem, so they must not be using
a very good database (very big company, too).
I see we can transform a char to a date by specifying the format mask. Is there any neat and
elegant way to define the transformation I need in the external table definition?
Thanks,
Mark
Followup May 10, 2005 - 11pm Central time zone:
I see substr, to_number and a little math plus a to_date in your future.
to_number(substr( C, 1, instr(C,'/')-1 ))
should get you 104
1900+<that> || substr( c, instr(C,'/') )
will get you 2004/03/01
to_date of that....
May 11, 2005 - 10am Central time zone
Reviewer: Mark from NY
Thanks for the response. I am actually familiar with these functions... Guess I wasn't clear about
the crux of my question. I'll try to explain better.
I am going to be loading data like: CSV -> External Table -> Live Table. I would like to catch as
much bad data as possible in the badfile. The client sometimes makes mistakes in their dates, so it
wouldn't be unheard of to receive '101/0314' for example (they forgot a slash). I would like to do
the converting at the external table level -- not when I am inserting into the live table.
Looking two posts up, we have:
23 "EFFECTIVE_DATE" (10:19) CHAR(10)
24 DATE_FORMAT DATE MASK "DD/MM/YYYY"
So in this example, if I understand correctly, having a date string '12/30/1980' (reversed DD and
MM) would cause a bad row to be logged in the badfile. I would like to do something similar, but
rather than have a simple date format mask I would need to call to_number, substr, etc.
Is this possible? Thanks.
Followup May 11, 2005 - 12pm Central time zone:
sorry, if you want SQL, you'll have to do it in SQL - during the SELECT.
can you use a multi-table insert? have the function reeturn a "good date" or a "known bad date
(01-01-0001 for example) and insert the good rows into one table, bad rows into another?
May 11, 2005 - 1pm Central time zone
Reviewer: Mark from NY
Yes I think that will do. Thanks.
What is
May 11, 2005 - 5pm Central time zone
Reviewer: Dave
<quote>flow the data through a pipelined plsql function for cleansing/transformation<quote>
What is a *pipelined plsql function* and how to use it for cleansing/transformation? any example?
Thnaks.
Followup May 11, 2005 - 7pm Central time zone:
search for pipelined, tons of examples in this site.
May 17, 2005 - 11pm Central time zone
Reviewer: A reader
Dear Tom,
I was exploring the possibility of using external tables instead of sqlldr in our DW environment.
The main issue I am faced with is that with external tables, the processing driver has to shift
from the client to the database. We currently drive our load processes from the ETL tool - after
the transformations we pipe the data directly to sqlldr without landing them into files. I can
probably benefit by using external tables sothat I can let Oracle decide on the appropriate DOP and
also use sql functions in some cases. But I do not know how to implement the external table
functionality in this scenario.
Is it possible create external tables on a pipe? even if that is possible, how can I call the load
process from outside the database (I looked at some of the examples in your site about calling
shell scripts from a procedure - not sure if that is the way to go). Appreciate if you can show an
example of how to do this. As always, thank you verymuch for your fantastic solutions!
Followup May 18, 2005 - 8am Central time zone:
No, you cannot use a pipe for the external table.
PROJECT COLUMN
June 15, 2005 - 12pm Central time zone
Reviewer: Vrajesh Shah from USA
Tom,
We are using external table for our incoming files from different vendors. They also sent us the
record count file. Before loading the external table to stage table we check the count of external
table using select count(*) from external table.
This count then check with the record count file to make sure the count are matching. We are not
very sure about the
"PROJECT COLUMN" for oracle 9.2.0.6.0 (our database). Is there a chance that the count(*) from
external table and load count of stage table ( stage table has no constraints, or validation) can
give diffrent numbers?
Followup June 16, 2005 - 3am Central time zone:
I don't understand?
count(*) from an external table will tell you how many records that are not "bad" or "discarded"
will be returned
count(*) form a 'stage' table tells you how many records are in the table.
count
June 17, 2005 - 1pm Central time zone
Reviewer: reader from USA
If 10 records in data file and if we do select count(*) from external table it will always give you
10 irrespective of any bad or rejected records. when we do select * from external_table we might
get 10 or less depends on the rejection of the records. So record count from external table is no
guarantee of that many records you get in stage table.
External Table Access
June 28, 2005 - 2pm Central time zone
Reviewer: Deepak from India
Hi Tom,
I have a very simple question.
Does Oracle store External table data in Database Buffer Cache when we query an external table?
I am asking this because external tables are stored in flat files and they do not have physical
structure like the database block defined. What is the actual process behind external table access?
Thanks in advance.
Followup June 28, 2005 - 2pm Central time zone:
no, it is always read from disk.
memory usage
June 28, 2005 - 2pm Central time zone
Reviewer: Deepak from India
Thanks Tom,
But would you please let us know the Oracle memory structures used in accessing rows from external
table?
Can we provide any kind of hints in queries that refer external tables?
Sometimes I get large pool memory allocation errors when accessing external tables. Why?
Followup June 28, 2005 - 3pm Central time zone:
it just reads them straight into your session. just reads them. they are not cacheable in the
buffer cache.
yes, you can use hints that make sense.
I don't know why on the last part, you give totally insufficient data to comment.
June 28, 2005 - 7pm Central time zone
Reviewer: A reader
Does oracle employ direct path reads / writes while reading from external tables like direct io
mechanism for non-cached CLOBs ?
Followup June 28, 2005 - 8pm Central time zone:
we just read files from the OS, it is very similar to direct IO as direct IO is just IO that
bypasses the cache.
External Table access Error
June 29, 2005 - 1am Central time zone
Reviewer: Deepak from India
Hi Tom,
Here is the information regarding the error I wrote about earlier.
I executed the follownig query....
select * from code_search where item_DESC ='%PC%';
NOTE: code_search is the external table.
The ERROR is....
ERROR:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04050: error while attempting to allocate 524800 bytes of memory
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
Please tell me why does this error comes only while accessing an external table.
As you said external table access do not use any Oracle memory structure but uses direct I/O.
Kindly clarify...
Followup June 29, 2005 - 8am Central time zone:
what does this have to do with large pool?!??!
<quote>
Sometimes I get large pool memory allocation errors when accessing external
tables. Why?
</quote>
hmmm.
Anyway, looks like your session ran out of the ability to allocate more process memory.
If it happens every time, I would suspect a bug in the manner in which you defined the table
somehow. but we have no test case to see. no versions to consider. not much.
and where pray tell did I say it uses no memory???? I said "not cached in buffer cache", that is
not anything like saying "no memory used"
Where in data dictionary is external table definition?
August 25, 2005 - 4pm Central time zone
Reviewer: A reader
Is there a dba_view that holds the definition for an external table?
Followup August 25, 2005 - 6pm Central time zone:
sys@ORA9IR2> desc dba_external_tables;
Name Null? Type
----------------------------------------------------- --------
------------------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TYPE_OWNER CHAR(3)
TYPE_NAME NOT NULL VARCHAR2(30)
DEFAULT_DIRECTORY_OWNER CHAR(3)
DEFAULT_DIRECTORY_NAME NOT NULL VARCHAR2(30)
REJECT_LIMIT VARCHAR2(40)
ACCESS_TYPE VARCHAR2(7)
ACCESS_PARAMETERS VARCHAR2(4000)
Sqlloader vs External Tables
October 4, 2005 - 11am Central time zone
Reviewer: THIRUMARAN from INDIA
Hi Tom,
A) with external tables can I achieve the following .I am working on migration from SOURCE 10g DB
R1(unnormalized tables) to TARGET 10g DB R2 (Normalized tables)
my text below quotes "SOURCE" AS UNNORMALIZED DB & "TARGET" as new DB which is NORMALIZED.I need to
migrate data from SOURCE to TARGET.
1) Can single row in SOURCE be migrated to multiple rows across one or more tables in TARGET.
2) Can multiple rows in single column in SOURCE be migrated to single row and multiple columns in
TARGET.
3)Can single row in SOURCE be migrated to single row in multiple tables in the TARGET.
can this be achieved by using any ORACLE BUILT IN TOOLS / combination of any oracle built in tools?
B) The below site mentions
"Note that SQL*Loader
may be the better choice in data loading situations that require additional indexing of the staging
table"
I am much thankful if you could elaborate this .
1) What is the purpose of staging tables
2) when should we should recommend to use staging tables ?
Ref:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/et_concepts.htm#i1007707
Thanks
Thirumaran
Followup October 4, 2005 - 4pm Central time zone:
1) yes, multi table inserts
2) sure, if you can write the query, you can insert it. You can pivot in SQL on a key
3) see #1
b) I cannot, I do not know why they said that. you could use sqlldr to load a staging table, you
can use an external table to load a staging table - I see no benefit sqlldr would provide.
you would only use a staging table if you ABSOLUTELY need to read and reread the data and have a
need for indexes on it (since external tables cannot be indexed)
October 12, 2005 - 2pm Central time zone
Reviewer: A reader
CTAS with external table
October 14, 2005 - 1pm Central time zone
Reviewer: A reader
I have a real table in the database that is loaded using sqlldr. I want to create a external table
with the same structure.
I tried doing
create table my_ext
organization external
...
as select * from real_table where 1=2;
It doesnt like it.
Any way to do this?
Thanks
Followup October 14, 2005 - 5pm Central time zone:
why doesn't it? In 10g that would work and would create an empty external table (not very useful).
That is called an external table UNLOAD.
Not sure what you are trying to do?
Question
October 17, 2005 - 11pm Central time zone
Reviewer: PRS from USA
Tom,
I have a flat file which has multiple records per employee. I want to use external file. But
this multiple record layout is different based on the record type. Example for EmpId 3005 (Position
2 to 5):
13005999-99-9999 LOAN1 TEST 1003772 O200507150015247
23005999-99-9999 LOAN1 2003772 TEST 012750000188663B
33005999-99-9999 LOAN1 3003772 TEST 012750000188663B
First character(i.e Position) indicates record type. Position 6 to 16 is SSN. Position 2 to 5 is
EmpId. So my questions are
(1) How to load this using external table or SQL LOADER?
(2) How Do I merge number of records(three or two or one) per employee into single records?
As always appreciate your help and guidance. You are the Guru of ORACLE.
Followup October 18, 2005 - 8am Central time zone:
you can either read the server utilities guide and see how to use multiple INTO clauses in sqlldr
with a "when" clause, or you can use an external table like this:
ops$tkyte@ORA9IR2> create or replace directory data_dir as '/tmp';
Directory created.
ops$tkyte@ORA9IR2> drop table et;
Table dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE TABLE et
2 (
3 record_type varchar2(1),
4 rec1_ssn varchar2(11),
5 rec1_empid varchar2(4),
6 rec2_ssn varchar2(11),
7 rec2_empid varchar2(4)
8 /* other fields here.... */
9 )
10 ORGANIZATION external
11 (
12 TYPE oracle_loader
13 DEFAULT DIRECTORY DATA_DIR
14 ACCESS PARAMETERS
15 (
16 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
17 READSIZE 1048576
18 FIELDS LDRTRIM
19 REJECT ROWS WITH ALL NULL FIELDS
20 (
21 record_type (1:1),
22 rec1_ssn (6:16),
23 rec1_empid (2:5),
24 rec2_ssn (6:16),
25 rec2_empid (2:5)
26 )
27 )
28 location
29 (
30 'test.dat'
31 )
32 )REJECT LIMIT UNLIMITED
33 /
Table created.
The first field will be the record type - then you reference the columns you want using rec1_ or
rec2_ -- that is, you map each "type" separately using varchar2's (in case the space sometimes has
a number, and sometimes a date and sometimes a string - varchar2 will be "type safe")
you can use to_number and to_date in the select to fix that.
Direct path load, list partition, ora-00054
October 27, 2005 - 12pm Central time zone
Reviewer: A reader
Hello Tom,
We're running into a problem loading a table using sqlldr in direct path mode.
We have a table that is list partitioned. This table has no FK's, and 2 indexes. There are no
triggers on this table.
If we load one partition (for example, our 'CA' list partition) things are fine.
When we load more than one partition concurrently (for example, start 'CA' and in another window
start 'NY'), we get the following in our log file:
SQL*Loader-951: Error calling once/load initialization
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified
We have the following set for sqlldr command line, to avoid any index maintenance issues:
skip_unusable_indexes=true
skip_index_maintenance=true
Given the simplicity of this table, what could possibly be causing the resource locks? Are we
"ALTERING" anything on the structure level of this table where we'd need this based on how we're
running this?
Followup October 27, 2005 - 1pm Central time zone:
let us see an example... the control file is?
control file and sqlldr command
October 27, 2005 - 3pm Central time zone
Reviewer: A reader
sqlldr / CONTROL=load_control.ctl DATA=data/load_tab.dat skip_unusable_indexes=true
skip_index_maintenance=true direct=true LOG=logs/load_tab.log ROWS=100000
<FILE load_control.ctl>
LOAD DATA APPEND INTO TABLE LOAD_TAB
(
GENERATED_ID POSITION(01:08) CHAR ,
ST POSITION(09:10) CHAR,
FILE_LINE POSITION(11:22) INTEGER EXTERNAL,
PROVIDER POSITION(23:25) CHAR,
ZIP POSITION(26:30) CHAR "LTRIM(:ZIP)"
)
I have been reading up a little more. Someone on a mail list said Oracle issues an implicit "alter
table <name> lock exclusive" for direct path loads. This would make sense why we couldn't
concurrently direct path load into the same table.
Followup October 28, 2005 - 1am Central time zone:
yeah, but it should work OK for partitioned tables - can you use
into table NAME (partition PNAME)
instead - then it'll be at the partition level. (shouldn't need to use parallel=true)
November 29, 2005 - 6pm Central time zone
Reviewer: A reader
December 13, 2005 - 3am Central time zone
Reviewer: CJ from Makati City, PH
Hi Tom,
How does Oracle "proactively" detect that the file pointed to by the external table has changed?
Example:
1.) external table points to 'somefile.dat'
2.) somefile.dat contains data from a timestamped file 'somefile_20051201000001.dat'
3.) two seconds later, the OS receives an independent timestamped file
'somefile_20051201000003.dat' and overwrites 'somefile.dat' with its contents
What I'd want to happen is whenever the entire contents of somefile.dat changes, Oracle will append
its entire contents to a master table, and wait (indefinitely) until somefile.dat changes yet again
(a la real-time ETL without the OS doing anything, as it did when it had to call SQL*Loader per
newly-created file).
Thanks. =)
Followup December 13, 2005 - 9am Central time zone:
it "doesn't"
whenever you query an external table we
a) open it
b) read it
c) close it
Oracle is not sitting there watching this file, you would have to write some (os specific) routine
to do so.
different behavior between sqlldr and external table
January 3, 2006 - 2pm Central time zone
Reviewer: gzhang from Michigan, USA
Tom, your example is relly helpful.
I am trying to use external tables to replace some sqlldr scripts, but have problem when the flat
file has newline char in the data field enclosed by double quotes (yes, I admit the data is not
perfect, but I have no control over that). Yet, sqlldr handles it well.
Table creation:
create table test_table (
key_code varchar2(10)
, key_num varchar2(10)
, big_text varchar2(4000)
, status_code varchar2(10)
);
Here is the infile (3 records, note the 2nd record has newline char in it):
5015,05-101042,"big text field .....",15
9950,05-100972,"big text
field
",20
5015,05-100963,"big text field ......",13
Here is the sqlldr control file:
LOAD DATA
INFILE 'C:\temp\testldr\test_data.csv'
BADFILE 'C:\temp\testldr\test_data.bad'
DISCARDFILE 'C:\temp\testldr\test_data.dsc'
INTO TABLE test_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( key_code
, key_num
, big_text Char(4000) "substr(:big_text,1,250)"
, status_code
)
sqlldr is working fine and loads 3 records. When I use external table, a select returns 2 records
(1st and 3rd). The log file shows that it complains about the 2nd record for not having second
enclosing delimiter.
CREATE TABLE ext_test_table (
key_code VARCHAR2(10)
, key_num VARCHAR2(10)
, big_text VARCHAR2(4000)
, status_code VARCHAR2(10)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY criv_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'CRIV_DIR':'test_data.bad'
DISCARDFILE 'CRIV_DIR':'test_data.dsc'
LOGFILE 'test_data.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"KEY_CODE" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"KEY_NUM" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"BIG_TEXT" CHAR(4000)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"STATUS_CODE" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'test_data.csv'
)
)REJECT LIMIT UNLIMITED;
Is there a way to use external table in this case?
Thanks,
George
Followup January 3, 2006 - 2pm Central time zone:
sorry - but sqlldr doesn't work that way - that file has SIX records as far as sqlldr is concerned!
sqlldr isn't loading that file "ok".
ops$tkyte@ORA10GR2> select * from test_table;
no rows selected
ops$tkyte@ORA10GR2> !cat t.ctl
LOAD DATA
INFILE 'test_data.csv'
INTO TABLE test_table
replace
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( key_code
, key_num
, big_text Char(4000) "substr(:big_text,1,250)"
, status_code
)
ops$tkyte@ORA10GR2> !cat test_data.csv
5015,05-101042,"big text field .....",15
9950,05-100972,"big text
field
",20
5015,05-100963,"big text field ......",13
ops$tkyte@ORA10GR2> !sqlldr / t
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jan 3 14:30:08 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 6
ops$tkyte@ORA10GR2> select * from test_table;
KEY_CODE KEY_NUM
---------- ----------
BIG_TEXT
-------------------------------------------------------------------------------
STATUS_COD
----------
5015 05-101042
big text field .....
15
5015 05-100963
big text field ......
13
Now, there are ways to get sqlldr to load data with embedded newlines, but that isn't one of
them...
difference of newline on windows vs linix?
January 3, 2006 - 3pm Central time zone
Reviewer: A reader
Tom, Thanks for such a fast response. I did get the same result when I run this from the console
of Oralce server (Linux, of course).
However, in my previous questions, I run it on my windows xp client desktop, it does load 3 records
(both 10g and 9ir2 client).
CRIV@devl > truncate table test_table
2 /
Table truncated.
CRIV@devl > host type test_data.csv
5015,05-101042,"big text field .....",15
9950,05-100972,"big text
field
",20
5015,05-100963,"big text field ......",13
CRIV@devl > host type test_data.ctl
LOAD DATA
INFILE 'C:\temp\testldr\test_data.csv'
BADFILE 'C:\temp\testldr\test_data.bad'
DISCARDFILE 'C:\temp\testldr\test_data.dsc'
INTO TABLE test_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( key_code
, key_num
, big_text Char(4000) "substr(:big_text,1,250)"
, status_code
)
CRIV@devl > host sqlldr criv@devl test_data.ctl
Password:
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jan 3 15:16:47 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 3
CRIV@devl > select * from test_table;
KEY_CODE KEY_NUM
---------- ----------
BIG_TEXT
----------------------------------------------------------------------------------------------------
-------------------------------
STATUS_COD
----------
5015 05-101042
big text field .....
15
9950 05-100972
big text
field
20
5015 05-100963
big text field ......
13
Is this because that newline is handled differently on windows and linux? I understand that
newline is CR+LF on win, and LF in linux.
Thanks,
George
Followup January 3, 2006 - 6pm Central time zone:
do you actually have a "windows newline" or a "unix newline" in there.
sqlldr will read to the end of line (\n on unix, \r\n on windows).
so, do you have a unix file you binary ftp, or a windows "text" file.
How to see which file is being used by an external table
January 4, 2006 - 7am Central time zone
Reviewer: Ajeet
Hi Tom,
Suppose I have 10 external tables . How can I find that what file is being used by which external
table ?
also is there is a way to list all files in a directory..using a sql statement. oracle version is
9i or 10g.
thanks
Followup January 4, 2006 - 10am Central time zone:
ops$tkyte@ORA9IR2> desc user_external_tables;
Name Null? Type
---------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
TYPE_OWNER CHAR(3)
TYPE_NAME NOT NULL VARCHAR2(30)
DEFAULT_DIRECTORY_OWNER CHAR(3)
DEFAULT_DIRECTORY_NAME NOT NULL VARCHAR2(30)
REJECT_LIMIT VARCHAR2(40)
ACCESS_TYPE VARCHAR2(7)
ACCESS_PARAMETERS VARCHAR2(4000)
ops$tkyte@ORA9IR2> desc user_external_locations;
Name Null? Type
---------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
LOCATION VARCHAR2(4000)
DIRECTORY_OWNER CHAR(3)
DIRECTORY_NAME VARCHAR2(30
for listing files in a directory:
http://asktom.oracle.com/pls/ask/search?p_string=dirlist
that explains it!
January 4, 2006 - 8am Central time zone
Reviewer: gzhang from Michigan USA
you are right, Tom. I thought of the newline thing a bit, but was not paying much attention.
This file is a windows text file, but it comes from FileMaker (Mac world, which might explains the
following). After I exmained the file in hex mode, I found that the "newline" within the 2nd record
is actually only a linefeed (0A). Therefore on windows, sqlldr only sees 3 real newline (0D0A) and
handles it well.
I guess I'd have to run this on windows.
Thanks again for your help.
George
How to see which file is being used by an external table
January 4, 2006 - 9am Central time zone
Reviewer: gzhang from MI, USA
Ajeet,
You can query user_external_locations to find out which files are used by external tables.
In sqlplus, you can run host command to list files in a directory:
on *nix: SQL> !ls
on windows: SQL> host dir
George
March 6, 2006 - 9am Central time zone
Reviewer: Alexander
Tom,
I have a question about a few of the things you mentioned about external tables above. You said
"with external tables you can
o merge a flat file with an existing table in one statement.
and
o do multi-table inserts"
Can you show a quick and dirty example of either or? Basically, I'm looking at loading data from a
file, but I need to some processing first and I wasn't sure the best way to do it. Originally I
thought I would have to
1)Create the external table
2)Load the data into an Oracle table
3)Write a procedure to do the processing
4)Insert new data back into different xyz tables
But perhaps I can cut out steps 3 and 4 and build that into step 2? Thanks for your advice.
Followup March 8, 2006 - 4pm Central time zone:
all you need to do is look at any merge, any multi-table insert - doesn't matter if the example
really really used external tables or not since you cannot tell the difference.
so, any merge will suffice (search, lots of them here)
any multi-table insert will suffice
you query external tables just like any other table.
external table limit?
March 17, 2006 - 1am Central time zone
Reviewer: J from CA
Is there any limit on number of columns on external table? I have flat file with over 2000 columns.
It need to split into 5 tables, with same sequence number for one record. If I can't create
external table for this one big fat file, I may have to use feature of multi-insert in sqlloader.
Question to use sqlloader is:
can I insert sequenct number for one column (which has to be generated during loading process)? Can
I use variable length of the file to do multi-table insert?
Thanks!
Followup March 17, 2006 - 5pm Central time zone:
well 1000 for the create table, however - you could map larger strings and use substr() in a view
to create a view with more if you wanted.
sqlldr can do sequences, yes. and varying width files.
how to year Concatenate in sqlloader
April 26, 2006 - 3am Central time zone
Reviewer: bala from india
Dear tom,
How to year Concatenate in sqlloader
see "0426" this raw data 26 is day and 04 is month
if i am taking position than only date and month coming but year how to Concatenate to sqlloader.
please help us.
Followup April 26, 2006 - 8am Central time zone:
huh?
did not understand this one.
data already looks pretty much "together" in your example - don't know what the reference to
concatenate is.
load a formated number into oracle external table
April 27, 2006 - 1pm Central time zone
Reviewer: a reader from MD USA
Tom:
Can you show me or point me the answer of
loading a txt file like:
333.23
222.45
102.02
1.23e+3
1.2456e+6
..
into a oracle external table with the column defined as number. When I tried to load it, the lost
2 rows are rejected. I feel it would be a very simple solution, but couldn't find it. Thanks
Followup April 27, 2006 - 3pm Central time zone:
"into a oracle exteral table" - no.
maybe you meant "from"
but it would be useful to see what you "tried"
load a formated number into oracle external table
April 27, 2006 - 4pm Central time zone
Reviewer: A reader from MD USA
Sorry Tom, I did the 'try' and it is ok now.
the reason I posted here is I was creating an external table based on a txt file and the only
rejected row is something like 2345e+6, and I thought it was because Oracle can't read scientific
format, but the real reason is ( when I did the 'try' as you suggested ) I did not define the
number field big enough.
Thanks for your quick and sharp response, I really admire your work here.
sqlloader versus external table
June 27, 2006 - 5pm Central time zone
Reviewer: Christopher from Portsmouth, NH
I need to load data from multiple large flat files into tables that will be repeatedly queried. The
problem is that the data in each row in the flat files is different depending on the first
character of the row. For example, if the first character was 1, the row might contain a name and
address. If the first character was 2, the row might contain a phone number and e mail address. I
could load the data into multiple staging tables (one for each type) using sqlloader like this:
http://www.orafaq.com/faqloadr.htm#MULTITABLE
Is sqlloader better then external tables in this case? Each file can contain multiple types of
records.
Followup June 28, 2006 - 7am Central time zone:
neither is "better", they both can accomplish this.
how does parallel load work
June 29, 2006 - 6am Central time zone
Reviewer: A reader
Hi
I am testing some data loading processes.
I am using parallel and direct. I have a doubt, does parallel load use parallel processes, the p0x
proceses? I dont think so, I have monitoring v$session and I always see one single process and this
insert statement
INSERT /*+ SYS_DL_CURSOR */INTO scott.aus_lin_1
("CODEMP", "CODCEN", "NUMAUS",
"CODPRO", "VARLOG", "CANSER",
"FECENT", "CODPRV"
)
VALUES (NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL
)
This table is hash partitioned.
I was expecting to see several loading processes but doesnt seems like it.
Followup June 29, 2006 - 7am Central time zone:
how are you doing the parallel load?
parallel load
June 29, 2006 - 8am Central time zone
Reviewer: A reader
Hi I used this
userid=scott/tiger
control=T.ctl
log=T.log
bad=T.bad
direct=true
parallel=true
SKIP_INDEX_MAINTENANCE=true
My table has a parallel degree of 16
TIA
Followup June 29, 2006 - 9am Central time zone:
then each sqlldr process will create it's own dedicated server process.
parallel load
June 29, 2006 - 11am Central time zone
Reviewer: A reader
Hi
That is what i thought, but I only see a Server Process in v$session!
I have teste din 9.2.0.6 and 10.2.0.2 and I get same results for both.
I am loading 1200000 rows...
Tia
Followup June 29, 2006 - 1pm Central time zone:
You are running more than one direct path load right? I mean - sqlldr PERMITS parallel loading.
but sqlldr does not "DO IT" automagically.
You have to split the input file into N files (or use skip and load to load slices)
You have to run more than one sqlldr process.
[tkyte@dellpe ~]$ sqlldr big_table/big_table big_table direct=true parallel=true &
[1] 3979
[tkyte@dellpe ~]$
SQL*Loader: Release 9.2.0.6.0 - Production on Thu Jun 29 13:03:36 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
sqlldr big_table/big_table big_table direct=true parallel=true &
[2] 3982
SQL*Loader: Release 9.2.0.6.0 - Production on Thu Jun 29 13:03:36 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
and while they were running, v$session will show:
--------------------
BIG_TABLE(11,164) ospid = 3979 command = 0 program = sqlldr@dellpe (TNS V1-V3)
dedicated server=3980
Thursday 13:03 Thursday 13:03 last et = 0
INSERT /*+ SYS_DL_CURSOR */ INTO BIG_TABLE.BIG_TABLE (ID,OWNER,O
BJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,C
REATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECOND
ARY) VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL)
--------------------
BIG_TABLE(15,29) ospid = 3982 command = 0 program = sqlldr@dellpe (TNS V1-V3)
dedicated server=3983
Thursday 13:03 Thursday 13:03 last et = 0
INSERT /*+ SYS_DL_CURSOR */ INTO BIG_TABLE.BIG_TABLE (ID,OWNER,O
BJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,C
REATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECOND
ARY) VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL)
parallel load
June 30, 2006 - 5am Central time zone
Reviewer: A reader
Hi
then what is the use of PARALLEL if I have to manually parallelize the process?!
What is the difference between
sqlldr big_table/big_table big_table direct=true &
sqlldr big_table/big_table big_table direct=true &
with
sqlldr big_table/big_table big_table direct=true parallel=true
sqlldr big_table/big_table big_table direct=true parallel=true
?
Followup June 30, 2006 - 7am Central time zone:
you are telling sqlldr "I will be running many of you at the same time, please do the extra work in
order to make this possible"
if you did it without parallel=true, the second one would block and wait for the first to continue.
Normally - a direct path operation in session 1 precludes ANY OTHER SESSION from writing to the
table.
direct=true lets sqlldr coordinate amongst itself over more than one session.
oracle sqlloader
July 14, 2006 - 2am Central time zone
Reviewer: badaubep from vietnam
hi tom!
i am a new in oracle.
i have a problem in sql loader .
this is the error
"unable to lock table khachhang due to oracle error 1031"
pls show me the solution
thanks so much
look forward your reply
ba dau bep
Followup July 14, 2006 - 8am Central time zone:
well,
[tkyte@dellpe ~]$ oerr ora 1031
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
// without the appropriate privilege. This error also occurs if
// attempting to install a database without the necessary operating
// system privileges.
// When Trusted Oracle is configure in DBMS MAC, this error may occur
// if the user was granted the necessary privilege at a higher label
// than the current login.
// *Action: Ask the database administrator to perform the operation or grant
// the required privileges.
// For Trusted Oracle users getting this error although granted the
// the appropriate privilege at a higher label, ask the database
// administrator to regrant the privilege at the appropriate label.
sounds most likely that you are using the replace option which tries to truncate and you do not own
the table. So, don't use replace - use append or delete.
Using default column values in External table
July 15, 2006 - 4pm Central time zone
Reviewer: Thiru
1.Is it possible to provide while creating external table a default value for a column whose value
is null? If so, is it part of the access parameters options? Can you please provide an example.
2.I have a ascii file generated from mainframe that has columns with datatype 'char' while it
should find a place in Number datatype. How is this converted while creating the ext table?
Followup July 16, 2006 - 9am Central time zone:
1) I love views, use a view. Easiest of all possible approaches. That way I don't even have to
dig into the docs myself :)
2) eh? you have strings right - a character string like 1234 that represents a number. It is
highly unlikely you have a mainframe file with actual binary data in the Oracle number format. You
just map as "char" and tell us "it is a number", we convert. You use char in the "access
parameters" and "number" in the create table part.
recover database
July 23, 2006 - 11pm Central time zone
Reviewer: A reader from vietnam
thanks Tom for your reply.
i have a question :
unfortunately, my database is corrupted .
I have a copy of all data files ( include system.dbf, redo.dbf ...).can i restore my databse ?
My steps are :
+create new database
+copy all datafile to new
+create control file
but it's error.
pls show me the way to recover database
thank u very much
badaubep
Followup July 24, 2006 - 10am Central time zone:
ouch, this hurts so bad.
please utilize support right now, before you do anything. They'll sort through what you have and
what your options are.
you never want to be learning recovery the day you need to do it. The one thing - the only thing -
a DBA is not allowed to get wrong is recovery (backup - they can do that wrong, we can fix that,
tuning - same thing, but recovery! if they cannot recover they have missed the boat)
9iR2 external table bug fixed by 10gR2
August 23, 2006 - 5am Central time zone
Reviewer: Phil Miesle from Ennis, Ireland
Discovered weird behaviour when comparing 'select count(*)' with 'select *' from an external table:
$ cat /tmp/test.txt
D20060515 V3069 W003063 SKPS0100255_1 132.0
D20060512 V3069 W003066 SKPS0100255_1 72.321
D20060517 V3069 W003061 SKPS0100255_1 48.0.3
===
create or replace directory external_test as '/tmp';
create table test
(day_ varchar2(10),
splr varchar2(20),
wh__ varchar2(20),
skps varchar2(20),
val number)
organization external
(type oracle_loader default directory external_test
access parameters
(
records delimited by newline
fields terminated by whitespace
(day_ char,
splr char,
wh__ char,
skps char,
val float external
)
)
location ('test.txt')
)
reject limit unlimited;
===
In 9iR2 (9.2.0.7):
SQL> select count(*) from test;
COUNT(*)
----------
3
SQL> select * from test;
[chomp]
2 rows selected.
===
The 'missing' row was the third record in test.txt; the '48.0.3' is not a valid number. 'select
count(*)' gives no .bad file and no indication in the logfile that something is amiss. The 'select
*' does.
By 10gR2 (10.2.0.1) this is corrected; the 'select count(*)' gives the expected errors/behaviour.
Just an FYI for anyone reading this, as this particular AskTom article has a very high google
pagerank.
Followup August 27, 2006 - 3pm Central time zone:
<quote src=Expert Oracle Database Architecture>
Dealing with Errors
In a perfect world, there would be no errors. The data in the input file would be perfect, and it
would all load correctly. That almost never happens. So, how can we track errors with this process?
The most common method is to use the BADFILE option. Here, Oracle will record all records that
failed processing. For example, if our control file contained a record with DEPTNO 'ABC', that
record would fail and end up in the bad file because 'ABC' cannot be converted into a number. WeÂ’ll
demonstrate that in the following example.
First, we add the following as the last line of demo1.ctl (this will add a line of data that cannot
be loaded to our input):
ABC,XYZ,Hello
Next, we run the following command, to prove that the demo1.bad file does not yet exist:
ops$tkyte@ORA10G> host ls -l demo1.bad
ls: demo1.bad: No such file or directory
Then we query the external table to display the contents:
ops$tkyte@ORA10G> select * from SYS_SQLLDR_X_EXT_DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 Sales Virginia
20 Accounting Virginia
30 Consulting Virginia
40 Finance Virginia
Now we will find that the file exists and we can review its contents:
ops$tkyte@ORA10G> host ls -l demo1.bad
-rw-r--r-- 1 ora10g ora10g 14 Jul 17 10:53 demo1.bad
ops$tkyte@ORA10G> host cat demo1.bad
ABC,XYZ,Hello
But how can we programmatically inspect these bad records and the log that is generated?
Fortunately, that is easy to do by using yet another external table. Suppose we set up this
external table:
ops$tkyte@ORA10G> create table et_bad
2 ( text1 varchar2(4000) ,
3 text2 varchar2(4000) ,
4 text3 varchar2(4000)
5 )
6 organization external
7 (type oracle_loader
8 default directory SYS_SQLLDR_XT_TMPDIR_00000
9 access parameters
10 (
11 records delimited by newline
12 fields
13 missing field values are null
14 ( text1 position(1:4000),
15 text2 position(4001:8000),
16 text3 position(8001:12000)
17 )
18 )
19 location ('demo1.bad')
20 )
21 /
Table created.
This is just a table that can read any file without failing on a datatype error, as long as the
lines in the file consist of fewer than 12,000 characters. If they are longer than 12,000
characters, then we can simply add more text columns to accommodate them.
We can clearly see the rejected records via a simple query:
ops$tkyte@ORA10G> select * from et_bad;
TEXT1 TEXT2 TEXT3
--------------- --------------- ---------------
ABC,XYZ,Hello
A COUNT(*) could tell us how many records were rejected. Another external table created on the log
file associated with this external table could tell us why the record was rejected. We would need
to go one step further to make this a repeatable process, however. The reason is that the bad file
is not “blanked” out if there were no errors in our use of the external table. So, if there were
some preexisting bad file with data in it and our external table generated no errors, we would be
misled into thinking there were errors.
IÂ’ve taken three approaches in the past to resolve this issue:
* Use UTL_FILE and reset the bad file—truncate it, in effect, by simply opening it for write
and closing it.
* Use UTL_FILE to rename any preexisting bad files, preserving their contents, but allowing
us to create a new one.
* Incorporate the PID into the bad (and log) file names. WeÂ’ll demonstrate this later in the
“Multiuser Issues” section.
In that fashion, weÂ’ll be able to tell if the bad records in the bad file were generated by us just
recently or if they were left over from some older version of the file itself and are not
meaningful.
[NOTE]
ALTER TABLE T PROJECT COLUMN REFERENCED|ALL
The COUNT(*) earlier in this section made me think about a new feature in Oracle 10g: the ability
to optimize external table access by only accessing the fields in the external file that are
referenced in the query. That is, if the external table is defined to have 100 number fields, but
you select out only one of them, you can direct Oracle to bypass converting the other 99 strings
into numbers. It sounds great, but it can cause a different number of rows to be returned from each
query. Suppose the external table has 100 lines of data in it. All of the data for column C1 is
“valid” and converts to a number. None of the data for column C2 is “valid,” and it does not
convert into a number. If you select C1 from that external table, youÂ’ll get 100 rows back. If you
select C2 from that external table, youÂ’ll get 0 rows back.
You have to explicitly enable this optimization, and you should think about whether it is “safe”
for you to use or not (only you know enough about your application and its processing to answer the
question “Is it safe?”). Using the earlier example with the bad line of data added, we would expect
to see the following output upon querying our external table:
ops$tkyte@ORA10G> select dname
2 from SYS_SQLLDR_X_EXT_DEPT
3 /
DNAME
--------------
Sales
Accounting
Consulting
Finance
ops$tkyte@ORA10G> select deptno
2 from SYS_SQLLDR_X_EXT_DEPT
3 /
DEPTNO
----------
10
20
30
40
We know the “bad” record has been logged into the BADFILE. But if we simply ALTER the external
table and tell Oracle to only “project” (process) the referenced columns, as follows:
ops$tkyte@ORA10G> alter table SYS_SQLLDR_X_EXT_DEPT
2 project column referenced
3 /
Table altered.
ops$tkyte@ORA10G> select dname
2 from SYS_SQLLDR_X_EXT_DEPT
3 /
DNAME
--------------
Sales
Accounting
Consulting
Finance
XYZ
ops$tkyte@ORA10G> select deptno
2 from SYS_SQLLDR_X_EXT_DEPT
3 /
DEPTNO
----------
10
20
30
40
we get different numbers of rows from each query. The DNAME field was valid for every single record
in the input file, but the DEPTNO column was not. If we do not retrieve the DEPTNO column, it does
not fail the record—the resultset is materially changed.
</quote>
Is it possible to do in Sql loader?
October 8, 2006 - 2am Central time zone
Reviewer: A reader
Hi Tom,
Thanks for your invaluable help to the oracle community. I have requirement to load the flat
file, it would having 7 millions lines. Like that we have to load 90 files per day(90*7 million
records). The trick is one record is number of x lines in the file, where x is not constant across
all the files. Following is the sample file.
RCG 123672 0 0 1 0
OFG 20060701 20060731 LHR MEL LHR MEL GB I F F
ODI 0 LHR MEL EK 00022 F EK 00022 F 1000 2000
ODI 0 MEL LHR EK 00456 F EK 00456 F 2230 0255
GFS AU 7 21 26 16.64 15.08 0.67 0.77 0.9 0.87 0.1 1.1 2.1 0.33 0.31
GFD AU 22 40.2964 42.4656 20.1232 17.9568 18.1562 4.0373
GFD AU 23 42.6445 43.8285 21.1232 19.3555 19.4857 5.0643
GFD AU 24 40.1754 43.8285 22.1232 21.3584 22.0587 7.0893
GFD C 22 ? 32.9852 20.1232 ? 18.1562 4.0373
GFD C 23 ? 36.4555 21.1232 ? 19.4857 5.0643
GFD C 24 ? 36.4555 22.1232 ? 22.0587 7.0893
GFD OC 23 ? 41.5195 ? 0 0 ?
GFD OC 24 ? 40.9195 ? 0 0 ?
GFD PU 23 52.6415 52.1252 20.1232 19.3575 20.3545 5.0373
GFD PU 24 52.6415 52.1252 20.1232 19.3575 25.5407 5.0373
RCG 123672 0 0 1 0
OFG 20060701 20060731 LHR MEL LHR MEL GB I A F
ODI 0 LHR MEL EK 00022 A EK 00022 A 1000 2000
ODI 0 MEL LHR EK 00456 A EK 00456 A 2230 0255
GFS AU 7 21 26 16.64 15.08 0.67 0.77 0.9 0.87 0.1 1.1 2.1 0.33 0.31
GFD AU 22 40.2964 42.4656 20.1232 17.9568 18.1562 4.0373
GFD AU 23 42.6445 43.8285 21.1232 19.3555 19.4857 5.0643
GFD AU 24 40.1754 43.8285 22.1232 21.3584 22.0587 7.0893
GFD C 22 ? 32.9852 20.1232 ? 18.1562 4.0373
GFD C 23 ? 36.4555 21.1232 ? 19.4857 5.0643
GFD C 24 ? 36.4555 22.1232 ? 22.0587 7.0893
GFD OC 23 ? 41.5195 ? 0 0 ?
GFD OC 24 ? 40.9195 ? 0 0 ?
GFD PU 23 52.6415 52.1252 20.1232 19.3575 20.3545 5.0373
GFD PU 24 52.6415 52.1252 20.1232 19.3575 25.5407 5.0373
Each record is separated by the record 'RCG', that is the header for each record. In the above
example from the line 'OFG' to 'GFD'(Before the next 'RCG' record starts) is the one record. From
each record type I have to take the fixed position values , i.e for OFG - I have take position
(5:12),(14,21). For ODI - position (7:9),(11:13) and so on.
I don't want to load 'RCG' record into table.
Is the above requirement can be done through sql loader? Please advise me.
Followup October 8, 2006 - 7am Central time zone:
explain how they can be one record, yet you use "fixed positions" - giving us "non-fixed positions"
when they are concatenated and a varying number of columns??
eg: cannot imagine what the table looks like here.
View on an external table
October 18, 2006 - 8am Central time zone
Reviewer: Igor Drozdov from Russia
Hi,
Views on external tables are different from views on regular tables. They demand granting direct
rights on the directory and the external table to non owner user for simple select from this view.
Why?
Example (... means "skip").
User A:
creare or replace directory bsqpctrates_dat_dir as ...
create table A.bsq_pct_rates ...
create view A.vw_bsq_pct_rates as select * from A.bsq_pct_rates;
grant read on directory bsqpctrates_dat_dir to B;--(!!)
grant select on A.bsq_pct_rates to B;--(!!)
grant select on A.vw_bsq_pct_rates to B;
And only then user B:
select * from A.vw_bsq_pct_rates;
Thank you,
Igor Drozdov
Followup October 18, 2006 - 9am Central time zone:
has nothing to do with views.
has everything to do with external tables.
remove the view from your example entirely:
ops$tkyte%ORA10GR2> create or replace directory data_dir as '/tmp/'
2 /
Directory created.
ops$tkyte%ORA10GR2> create table external_table
2 (EMPNO NUMBER(4) ,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2) ,
9 DEPTNO NUMBER(2)
10 )
11 ORGANIZATION EXTERNAL
12 ( type oracle_loader
13 default directory data_dir
14 access parameters
15 ( fields terminated by ',' )
16 location ('emp.dat')
17 )
18 /
Table created.
ops$tkyte%ORA10GR2> select empno from external_table where rownum < 5;
EMPNO
----------
7369
7499
7521
7566
ops$tkyte%ORA10GR2> create user a identified by a;
User created.
ops$tkyte%ORA10GR2> grant create session to a;
Grant succeeded.
ops$tkyte%ORA10GR2> grant select on external_table to a;
Grant succeeded.
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> select empno from ops$tkyte.external_table where rownum < 5;
select empno from ops$tkyte.external_table where rownum < 5
*
ERROR at line 1:
ORA-06564: object DATA_DIR does not exist
so, it has nothing to do with the view, it is a requirement of the external table itself
In order to read that directory object, you must have read on the directory.
View on an external table
October 18, 2006 - 8am Central time zone
Reviewer: Igor Drozdov from Russia
Oracle 9.2.0.6
View on external table
October 19, 2006 - 1am Central time zone
Reviewer: Igor Drozdov from Russia
But what about
grant select on A.bsq_pct_rates to B;--(!!) ?
With
grant read on directory bsqpctrates_dat_dir to B;--(!!)
and whithout
grant select on A.bsq_pct_rates to B;--(!!)
user B during:
select * from A.vw_bsq_pct_rates;
gets
ORA-04043 object A.bsq_pct_rates does not exist
The paradigm of the "view" is totally broken, mainly because ONLY the owner of the view must have
been explicitly granted privileges to access ALL objects referenced in the view definition!
Thanks & Regards
Igor Drozdov
Followup October 19, 2006 - 8am Central time zone:
that is your interpretation, but at least now you know how it is actually implemented and that
after all is what "counts"
View on an external table
October 20, 2006 - 12am Central time zone
Reviewer: Igor Drozdov from Russia
I am sorry. English is not my native language. What does the phrase [what "counts"] mean in [and
that after all is what "counts"]?
Thank you,
Igor Drozdov
Followup October 20, 2006 - 4am Central time zone:
that is all that matters.
that is all that is relevant.
View on an external table
October 20, 2006 - 6am Central time zone
Reviewer: Igor Drozdov from Russia
Thank you for your reply.
Regards
Igor Drozdov
cloning database in different os
October 23, 2006 - 12am Central time zone
Reviewer: A reader from vietnam
Hi Tom!
thanks for your support.i have a new question.
" my database is on window 2000.now i want to change in window 2003 server. how can i do without
exportation and importation ? "
Looking forward your reply.
badaubep
Followup October 23, 2006 - 10am Central time zone:
just backup and restore. you do not want to export/import.
October 23, 2006 - 8pm Central time zone
Reviewer: A reader from vietnam
Hi Tom!
thanks for your support
" my database is on window 2000.now i want to change in window 2003 server and upgrade oracle
8i-->9i or 10g. how
can i do without exportation and importation ? "
Looking forward your reply.
thanks
badaubep
Followup October 24, 2006 - 12am Central time zone:
just upgrade the database using the upgrade tools. read the migration guide that is supplied.
Followup of previous question
November 7, 2006 - 5am Central time zone
Reviewer: A reader
Hi Tom,
On Oct 8 2006, I had asked you this question and you had asked me to explain further. Sorry,
I couldn't send you the reply since I was on leave. Again I am posting the same question here.
Following is the flat file format.
RCG 123672 0 0 1 0
OFG 20060701 20060731 LHR MEL LHR MEL GB I F F
ODI 0 LHR MEL EK 00022 F EK 00022 F 1000 2000
ODI 0 MEL LHR EK 00456 F EK 00456 F 2230 0255
GFS AU 7 21 26 16.64 15.08 0.67 0.77 0.9 0.87 0.1 1.1 2.1 0.33 0.31
GFD AU 22 40.2964 42.4656 20.1232 17.9568 18.1562 4.0373
GFD AU 23 42.6445 43.8285 21.1232 19.3555 19.4857 5.0643
GFD AU 24 40.1754 43.8285 22.1232 21.3584 22.0587 7.0893
GFD AU 22 ? 32.9852 20.1232 ? 18.1562 4.0373
GFD AU 23 ? 36.4555 21.1232 ? 19.4857 5.0643
RCG 123672 0 0 1 0
OFG 20060701 20060731 LHR MEL LHR MEL GB I A F
ODI 0 LHR MEL EK 00022 A EK 00022 A 1000 2000
ODI 0 MEL LHR EK 00456 A EK 00456 A 2230 0255
GFS AU 7 21 26 16.64 15.08 0.67 0.77 0.9 0.87 0.1 1.1 2.1 0.33 0.31
GFD AU 22 40.2964 42.4656 20.1232 17.9568 18.1562 4.0373
GFD AU 23 42.6445 43.8285 21.1232 19.3555 19.4857 5.0643
GFD AU 24 40.1754 43.8285 22.1232 21.3584 22.0587 7.0893
GFD AU 22 ? 32.9852 20.1232 ? 18.1562 4.0373
GFD AU 23 ? 36.4555 21.1232 ? 19.4857 5.0643
Each set of lines is separated by the record 'RCG', that is
considered to be one record to oracle table. In the above example from the line 'OFG' to
'GFD'(Before the next 'RCG'
record starts) is the one record in oracle table. From each record type I have to take the
fixed position values
Columns in the table would be like this
DEPT_DT1,DEPT_DT2,ONLINE_ORGN,ONLINE_DSTN,
FLT_NO1,FLT_NO2,FX_DAYZERO_BKG_QTY,F0_DCP1_BKG_QTY,
F0_DCP1_BKG_QTY,F0_DCP2_BKG_QTY,F0_DCP3_BKG_QTY,
F0_DCP4_BKG_QTY,F0_DCP5_BKG_QTY
Values for these columns are
DEPT_DT1 - Record type OFG - Position (5:12)
DEPT_DT2 - Record type OFG - Position (14:21)
ONLINE_ORGN - Record type OFG - Position (19:21)
ONLINE_DSTN - Record type OFG - Position (23:25)
There will be always two ODI records for a set.
FLT_NO1 - Record type ODI(1st Rec)- Position (18:22)
FLT_NO2 - Record type ODI(2nd Rec)- Position (18:22)
There will be only one GFS record for a set.
FX_DAYZERO_BKG_QTY - Record type GFS - Position (16:20)
There will be maximum of 5 GFD records for a set. But it may vary from file to file.
F0_DCP1_BKG_QTY - Record type GFD(1st) - Position (11:17)
F0_DCP2_BKG_QTY - Record type GFD(2nd) - Position (11:17)
F0_DCP3_BKG_QTY - Record type GFD(3rd) - Position (11:17)
F0_DCP4_BKG_QTY - Record type GFD(4th) - Position (11:17)
F0_DCP5_BKG_QTY - Record type GFD(5th) - Position (11:17)
For e.g, if the file have only 3 GFD, we have to fill only three f0_dcp_bkg_qty column and other
two columns(F0_DCP4_BKG_QTY,F0_DCP5_BKG_QTY) are empty.
Could you explain , whether this can be achievable using sqlloader. If so, please provide me a
example.
Waiting for your reply
November 8, 2006 - 11pm Central time zone
Reviewer: A reader
Hi Tom,
I am waiting for your reply. Kindly answer my above question , whether it can be done through
sqlloader.
Followup November 9, 2006 - 8am Central time zone:
use continueif to assemble your logical single record from the N number of physical records in the
file.
continueif lets you say "keep appending input lines of data until...."
Followup of continueif logic
November 15, 2006 - 2am Central time zone
Reviewer: A reader
Hi Tom,
Thanks for your wonderful idea.
Using the continueif I had done my requirement. But it can done using trigger only, which is time
consuming.
The continueif statments provides me the full set as one record. Within the record , I have to take
only few elements from each record type for different columns. What I had done is , I had created
one extra column to put the entire record set from sqlldr. And in the before insert trigger I had
procsssed the entire set using the newly created column and assigned the values for all other
columns.
Can this be done without trigger?
Followup November 15, 2006 - 7am Central time zone:
I don't know why a trigger is involved at all. you know where the columns are, at least you seemed
to in the original example.
Convert from Table to Text File
November 15, 2006 - 5pm Central time zone
Reviewer: Ron from Raleigh, NC
Tom...
I need to convert an Oracle Table into text format (comma seperated or tab seperated). how do we do
it ?..
I can write a SQL script, spool it and run that looks like
----------------------------------------
sql> select accounts || ' ' || report_date || ' '|| acct_type ||' '||receipts||'
'||expenditures||' '||pack_decimal from treasury;
-----------------------------------------
but iam looking more for an output in the above format i was mentioning.
any help is apprecited..
Thanks
Exporting numeric fields in COMP/COMP-3 format to be used by COBOL
December 5, 2006 - 4am Central time zone
Reviewer: Venkataramesh K from London, UK
Hi Tom,
I have my database in Oracle 10g. I need to export data to a flat file which i am able to do
successfully. My proc takes the query as input and does it. But the exported file will be used by a
cobol program thru a copy book.
Now the copy book has some of the numberic fields as COMP and COMP-3 data types. How do i export
the data from oracle to be suitable for COBOL.
Thanks is advance,
Ramesh
ORA-06564 and best practices
January 3, 2007 - 5pm Central time zone
Reviewer: Duke Ganote from http://maps.google.com/maps?q=39.056690,-84.245720+(Near+here)
I was surprised to see that even a user with SELECT ANY TABLE privileges needs explicit grants on a directory while reading an external table:
dwdev1:CIDW\cidwview> select count(*) from catdm.ic129_parser; -- an external table
select count(*) from catdm.ic129_parser
*
ERROR at line 1:
ORA-06564: object CAT_DIR does not exist
dwdev1:CIDW\cidw> grant read, write on directory cat_dir to cidwview;
There doesn't seem to be a SELECT ANY DIRECTORY privilege as such. Do privileges need to be granted on individual directories?
I'm trying to think through any best practices on granting access on external tables generally. We've typically allowed a few select users to read the staging tables, but now I'm about to introduce external tables into our production database. Do you have any recommendations?
Followup January 5, 2007 - 8am Central time zone:
you want to AVOID the "any" privileges like the plague - always. If there is a fined grained privilege that could be granted, grant that - NOT THE ANY privilege. Far too much destruction could be done otherwise.
grant the select and grant the directory access as needed, use roles if you want for end users (not developers writing stored procedures, there you want a direct grant of course)
External table, best practices: segregate log file directory?
January 5, 2007 - 10am Central time zone
Reviewer: Duke Ganote from http://maps.google.com/maps?q=39.056690,-84.245720+(Near+here)
During the development process, log files are created by default in the same directory as the flat file. In a production database, I'd speculate that one should either
(a) set NOLOGFILE, NOBADFILE, etc. after the data warehouse load is completed
(b) create the log, etc files in a separate directory from the source directory.
For the first option, when looking at ALTER external TABLE options http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#i1007591
all the access parameters are part of the so-called opaque_format_spec
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm
which I'd have to store and modify as text.
Followup January 6, 2007 - 8am Central time zone:
in all environments (development should mirror test and production, less issues as you go from environment to environment that way) - having separate directories would be a good idea. You don't necessarily want read/write on the directory containing the INPUTS - putting the logs off to the side makes sense.
and if you do in development what you intend to do in "real life", you'll have no need to modify anything.
Which is the best option for simply to load data into the table?
April 18, 2007 - 12pm Central time zone
Reviewer: Gowtham Sen from India
Hi,
I am getting files of 30 GB to 50 GB.
So, I am using external tables to load data into the tables, without performating any operations.
So, in that case, would use of sqlloader imporves performance?
Which is the best option among these two.
Please suggest me.
Thank you,
Regards,
Gowtham Sen
Followup April 18, 2007 - 12pm Central time zone:
create table as select or insert /*+ append */ into an existing table would be logically equivalent to direct path sqlldr
through a parallel in there
maybe nologging the table first
and you have a parallel direct path load without logging - using a simple single DML/DDL statement.
You don't need sqlldr to do that.
difference of external tables and SQl loader
April 20, 2007 - 5am Central time zone
Reviewer: Naveen Kumar from India
for exrternal tables a single discard and bad file is generated for all the input files that you are using while loading multiple files at a time.Where as sql loader will generate multiple discard and bad files for each input files.
And also u cant have primary or any Referntial integrity on external tables.
and also u cant have clob,blob columns in your external tables
Followup April 20, 2007 - 7am Central time zone:
and also YOU cannot have any primary or referential integrity constraints on files used by sqlldr either
who is this "U" guy, seems like they have problems...
May 10, 2007 - 6am Central time zone
Reviewer: Neha Verma from Houston
SQL Loader is a good option avialable for importing the data.
Tom,
I want to import data from MS-Access to Oracle 8i. Is there any way to access the MS-Access data through some Oracle command or query? Is it possible to have stored Procedure doing the Export from MS-Access and Importing into Oracle? I want to have a scheduler, doing this activity.
SQLLDR / External tables / OCI
June 5, 2007 - 3am Central time zone
Reviewer: Phil from UK
Hi Tom,
Quick question for you, more architectural than code but related to the above. Telecom client wish to add call data (lots of it) to a CDR table. We want to product real(ish) time analytics on this table but want the data to come in fast and unaffected performance-wise. Was thinking of partitioned table with a refresh from the temp partition into the main CDR each minute but what is your first impression? If they used a flat file for example, how would we process this without affecting the data still being added to it? I assume we could not without interrupting load from the telephony side. This is very early days so I don't have all the information but would like to be pointed in vaguely the right direction so we don't waste time etc.
Regards
Phil
Followup June 6, 2007 - 12pm Central time zone:
we are very good at reading and writing the same segments at the same time (non-blocking reads and all)
unless and until you identify factually that "we cannot do it", I would say "just load em up and query them"
Data typecasting while loading from flat files
June 13, 2007 - 5am Central time zone
Reviewer: Dheeraj from INDIA
Hi,
I have a requirement where-in I am loading a huge no. of records from flat file into the DB using SQLLDR or External table.
My question is:
Since each column of my record would have different data type (VARCHAR2, NUMBER, DATE etc), is it prudent to make all my staging table/external table columns as VARCHAR2 data types while loading so that data dump happens faster OR should I create staging table/external table with column data types same as what is expected from flat files so that invalid records are rejected & I do post-load processing only with the correct records ?
Issue with the former approach is that post-load processing could happen for incorrect records as well leading to delay in overall process...because at the end of process, these incorrect records are goind to be filtered-out.
Thanks in advance,
Dheeraj
Followup June 13, 2007 - 8am Central time zone:
which approach would YOU like to take - it is a decision you need to make, there is no universal 'best' way.
Your requirements will dictate what approach you take.
Data typecasting while loading from flat files
June 13, 2007 - 10am Central time zone
Reviewer: Dheeraj from India
Thats true, Tom. But the background I am coming from is -
Assuming this to be a part of ETL process where-in fair no. of transformations happen on the dumped records, will it be fair to waste effort in doing transformations for invalid records and rejecting them at a later stage, when we could have actually rejected them @ dump stage itself ?
Do we have any analysis or example to support each feature ? Some benchmarks like:
i) If there are costly transformations/data typecasting in almost every column of dumped record then use approach X else use Y
ii) If x% percentage of records are generally expected to be invalid from flat file then use approach X else approach Y?
Any help will be highly appreciated.
Cheers!!!
Followup June 13, 2007 - 2pm Central time zone:
... ill it be fair to waste effort in doing transformations for invalid records and rejecting them at a later stage, ..
i don't know, what do you think?
It is purely 100% a matter of how you want to process this data. Do you want them rejected into a bad file OR do you want to see them, touch them and log them yourself?
You decide. Rejecting them before they get to your code will likely be more efficient - but now you'll have records in a bad file AND failed records you yourself saved. Will it be fair to waste effort in having to process the failed records from two different sources?
How to create a excel or csv file from Oracle using external files
June 20, 2007 - 12pm Central time zone
Reviewer: Kandy Train from TX USA
Hi Tom,
I can create a external file with the extension of .dmp
But How can I make it a excel file so that end users can read the data outside the database??
Thanks,
Kandy Train
Followup June 20, 2007 - 1pm Central time zone:
just name it ".csv" and excel will be quite happy with it.
or generate .html and same will be true...
or generate .sylk and same.... (search site for owa_sylk)
Creating a excel file from external table
June 20, 2007 - 1pm Central time zone
Reviewer: Kandy Trian from TX USA
Hi Tom,
Thanks for the response. I created a csv file instead of a dmp file. Now excel can open it. But, the format doesn't look good with other characters and html like tags and all that. How could I see these data like a table??
I mean is there a straight forward way than using owa_sylk which uses utl_file_dir method and pl/sql programming??
Thanks for the response,
Kandy Train
Followup June 20, 2007 - 2pm Central time zone:
html works...
html works??
June 20, 2007 - 5pm Central time zone
Reviewer: Kandy Train from TX USA
Hi Tom,
I have created the file from external table as .html and still it has those characters when I open it as a html(Web page) and opening the same file from a excel is still the same, I mean it doesn't look like a normal excel file but with some characters and tags.
This is how I created the file,
CREATE TABLE all_objects_html
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY xtern_data_dir
LOCATION ( 'all_objects_xt.html' )
)
AS
SELECT *
FROM all_objects;
Thanks for your time and response,
Kandy Train
Followup June 20, 2007 - 5pm Central time zone:
umm, well - you have to create an html file.
that create table will create precisely one type of file: a data pump file. period.
Anything else will be done via java stored procedures, utl_file, mod_plsql running a plsql routine.
I never dreamed you would try to create HTML that way. That didn't even enter my thought process ;)
Creating a excel file from Oracle
June 20, 2007 - 5pm Central time zone
Reviewer: Kandy Train from TX USA
Hi Tom,
Thanks for the response. I love external tables and always starts from there. My requirement is to dump data to an excel work book. It should dump different tables (Or data sets) into different sheets and put all those into a one excel work book.
I would like to hear if any one has attempted to something like this.
I think i will have to use Java inside the database to do this.
Thanks,
Kandy Train
Followup June 21, 2007 - 10am Central time zone:
you might be able to use simple html - but you would best be served by lurking in a microsoft help forum to get help on creating a file format that excel can deal with...
Another CSV option - APEX
June 20, 2007 - 9pm Central time zone
Reviewer: David S from Atlanta, GA USA
If you're interested in producing CSV and/or XML output, you should take a look at Application Express (ApEx). It's a great tool (this site was built with it), and very easy to use for development. Using ApEx, a user could select the proper data and download it immediately into Excel - ApEx is beautiful for this.
JExcelApi to Create a Workbook from Oracle
June 21, 2007 - 10am Central time zone
Reviewer: Kandy Train from TX USA
Hi,
Thanks for all your time and effort helping me.
I found your article, http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206 where it mention about JExcelApi program found in
http://www.andykhan.com/jexcelapi/
I have never used Java before, but in this case I got to use it. My question is, Could I install this Api into Oracle and use it?? If the answer is yes, How could I do it?? Because I have never used Java before, It would be great to see the steps that I should use in order to install this api and then use it.
Thanks,
Kandy Train
Creating Excel Workbook from PL/SQL
June 28, 2007 - 5pm Central time zone
Reviewer: Kandy Train from TX USA
Hi,
I just wanted to update the case that Jason Bennett (http://radio.weblogs.com/0137094/2006/10/26.html ) has a exmaple that create a workbook with multiple sheets from PL/SQL. I used it and it works. So, I didn't have to go through Java for this.
Thanks,
Kandy Train
Interrupting high insert table
July 6, 2007 - 5am Central time zone
Reviewer: Phil from UK
Hi Tom
One last post before we prototype (I promise).
In your reply to a posting above:
Followup June 6, 2007 - 12pm US/Eastern:
we are very good at reading and writing the same segments at the same time (non-blocking reads and all)
unless and until you identify factually that "we cannot do it", I would say "just load em up and query them"
The client is still concerned. Would it be of any benefit to get them to use streams or something else to keep this table that they are inserting to at high rates somewhat isolated? The table will need some indexes which will reduce insert performance and they are naturally worried that if we produce aggregate queries on it while the inserts are occurring that performance will be affected.
Followup July 6, 2007 - 12pm Central time zone:
how would streams help?
you insert fast into table T
you mine the logs for inserts into T and replicate them into T'
you have to query now T' which is a table undergoing high insertion rates
and streams has to replicate this excessively high insertion rate.
and your original source system will have to content for access to the online redo logs for real time apply - OR we'll mine the archives downstream, but think about "log jams" as you continuously insert....
this client would move the problem from T to T' and make is worse. Consider that :)
Variable lengths
July 9, 2007 - 8pm Central time zone
Reviewer: A reader from CA
Is there a way to load a file with variable lengths/formats in SQL Loader?
eg something like:
a,b,c,d
a,b
c
ab,c,d,d,e
(and no - the first set of characters are not the line lengths).
Followup July 9, 2007 - 9pm Central time zone:
sure, but it would depend entirely on what you mean by "load it"
give us a table create and what that input file should result in and describe the logic behind it all.
July 9, 2007 - 10pm Central time zone
Reviewer: A reader
Here goes:
create table t(
key varchar2(100), --the first column, the key for the row
col varchar2(100), -- the column position in th e row
value varchar2(100), -- the value in the column
row_cnt number); --the row identifier
File:
a,b,c,d
a,b
c
ab,c,d,d,e
The loaded table should look like:
The first column is the key for the row.
Key|col|value|row_cnt
a|1|a|1
a|2|b|1
a|3|c|1
a|4|d|1
a|1|a|2
a|2|b|2
c|1|c|3
ab|1|ab|4
ab|2|c|4
ab|3|d|4
ab|4|d|4
ab|5|e|4
Followup July 10, 2007 - 11am Central time zone:
you absolutely lost me.
you have given ZERO logic here, no clue how to automagically transform your input.
I could probably guess (pretty sure i could) but I hate guessing.
and if my guess is correct, we'll be using an external table and SQL to do this load, not sqlldr.
To A Reader
July 10, 2007 - 2am Central time zone
Reviewer: Michel CADOT from France
create table t_ext (line varchar2(2000))
organization external (
type oracle_loader
default directory work_dir
access parameters (
records delimited by newline
nobadfile
nologfile
nodiscardfile
fields terminated by '##################'
missing field values are null
(line)
)
location ('data.txt')
)
reject limit unlimited
/
SQL> with
2 cols as (select level col from dual connect by level <= 100),
3 data as (select ','||line||',' line, rownum row_cnt from t_ext where rownum > 0)
4 select substr(line, 2, instr(line,',',2)-2) key,
5 col,
6 substr(line,
7 instr(line,',',1,col)+1,
8 instr(line,',',1,col+1)-instr(line,',',1,col)-1) value,
9 row_cnt
10 from data, cols
11 where col < length(line)-length(replace(line,',',''))
12 order by key, row_cnt, col
13 /
KEY COL VALUE ROW_CNT
----- ------- ----- -------
a 1 a 1
a 2 b 1
a 3 c 1
a 4 d 1
a 1 a 2
a 2 b 2
ab 1 ab 4
ab 2 c 4
ab 3 d 4
ab 4 d 4
ab 5 e 4
c 1 c 3
12 rows selected.
Regards
Michel
To A Reader
July 10, 2007 - 3am Central time zone
Reviewer: Michel CADOT from France
If you know the maximum number of columns, for instance 10, then it is faster to use:
create table t_ext (
key varchar2(10),
col1 varchar2(10),
col2 varchar2(10),
col3 varchar2(10),
col4 varchar2(10),
col5 varchar2(10),
col6 varchar2(10),
col7 varchar2(10),
col8 varchar2(10),
col9 varchar2(10)
)
organization external (
type oracle_loader
default directory work_dir
access parameters (
records delimited by newline
nobadfile
nologfile
nodiscardfile
fields terminated by ','
missing field values are null
(key,col1,col2,col3,col4,col5,col6,col7,col8,col9)
)
location ('data.txt')
)
reject limit unlimited
/
SQL> with
2 cols as (select level col from dual connect by level < 11),
3 data as (
4 select key, col1, col2, col3, col4, col5, col6, col7, col8, col9, rownum row_cnt
5 from t_ext
6 where rownum > 0
7 )
8 select key, col,
9 case when col = 1 then key
10 when col = 2 then col1
11 when col = 3 then col2
12 when col = 4 then col3
13 when col = 5 then col4
14 when col = 6 then col5
15 when col = 7 then col6
16 when col = 8 then col7
17 when col = 9 then col8
18 when col = 10 then col9
19 end value,
20 row_cnt
21 from data, cols
22 where col < case when col1 is null then 2
23 when col2 is null then 3
24 when col3 is null then 4
25 when col4 is null then 5
26 when col5 is null then 6
27 when col6 is null then 7
28 when col7 is null then 8
29 when col8 is null then 9
30 when col9 is null then 10
31 else 11
32 end
33 order by key, row_cnt, col
34 /
KEY COL VALUE ROW_CNT
----- ------- ----- -------
a 1 a 1
a 2 b 1
a 3 c 1
a 4 d 1
a 1 a 2
a 2 b 2
ab 1 ab 4
ab 2 c 4
ab 3 d 4
ab 4 d 4
ab 5 e 4
c 1 c 3
12 rows selected.
Regards
Michel
July 10, 2007 - 6pm Central time zone
Reviewer: A reader from CA
Thanks!
How to find to which path it directing ?
November 6, 2007 - 12pm Central time zone
Reviewer: A reader
Tom,
Is there a way to find the path of the directory.?
For ex: DBA created one directory called 'data_dir' sometime back.
Now I need to see what was the path used while creating that directory.
Regards
Chandra Matta
Followup November 6, 2007 - 3pm Central time zone:
select * from dba_directories;
Direct path
July 6, 2008 - 8am Central time zone
Reviewer: Juel from UK
ON 10g (10.2.0.3)
Now the sqlloader w. direct path runs directly on the server (Solaris).
Could we run the sqloader job w. direct path from "anywhere" - ie a windows client (also 10g) or
from another Unix/AIX server?
Someone meant that in order for direct path to work it needed to be on the very same sever - I cant
find that info anywhere in the docs
regards
Followup July 7, 2008 - 11am Central time zone:
someone is frequently wrong.
as they are here.
the requirement is that the sqlldr client is the same release as the server if you are pre 9i. that is all.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_modes.htm#sthref1449
(it would be very easy to test - you just need to use user/pass@DATABASE - connect over the listener, even on a single machine - to see it on the network)
Back to the spfile question (Pauline)
August 25, 2008 - 10am Central time zone
Reviewer: John A. from Winston-Salem, NC
Hey Tom,
I "think" this qualifies as a current thread, but if not I apologize and you can delete at your
pleasure.
Pauline from NY asked a question (back in 2003) about the spfile causing a core dump, which is what
I'm experiencing. There are absolutely no trace files or any output in the alert log. The only
thing I see is the ORA-03113 on startup. If I startup using pfile, I have no problem. For the
record, the spfile was generated from the pfile immediately prior to shutdown, so it should be a
duplicate.
When I startup using spfile, I see the aforementioned error on my command line. Checking the
alert.log there is no new entry. No new traces in bdump or udump. There is, however, a lovely
245M core dump in the $ORACLE_HOME/dbs.
Starting up with pfile works like a charm, with no errors generated and seemingly innocuous traces
in the alert.log. (Most of which mention Hash Bucket Statistics, and one which mentions failing as
follows:
/oracle/admin/db_name/bdump/sid_lgwr_3675.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/product/10.2.0
System name: SunOS
Node name: server_name
Release: 5.10
Version: Generic_137111-05
Machine: sun4u
Instance name: instance_name
Redo thread mounted by this instance: 1
Oracle process number: 7
Unix process pid: 3675, image: username@server_name (LGWR)
*** 2008-08-25 07:37:46.914
*** SERVICE NAME:() 2008-08-25 07:37:46.914
*** SESSION ID:(5500.1) 2008-08-25 07:37:46.914
LGWR: Archivelog for thread 1 sequence 2224 will NOT be compressed
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
Maximum redo generation record size = 156160 bytes
Maximum redo generation change vector size = 150676 bytes
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x10)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x10)
The previous DBA was trying to setup DataGuard but had problems, so we're not currently sending
ARCH messages anywhere. I'm aware we're a bit behind on our patches, but so far nobody has been
able to remember what the CSI is. (Old DBA took it with him when he left, I guess.)
Any ideas? (Again, if this doesn't qualify under Pauline's post, then you can delete me.)
Regardless, thanks for taking the time to read this!
Followup August 26, 2008 - 8pm Central time zone:
please utilize support
if you
startup with pfile
create spfile from that pfile
shutdown
startup
it should be OK, but I don't see you doing that here - you state you did, but I don't see a cut&paste, so I cannot be sure.
RAC and parallel load
August 27, 2008 - 6am Central time zone
Reviewer: Bauer Wolfgang from Germany
Hi Tom,
are there any drawbacks when using external tables, RAC and parallel load?
Our DBAs are not sure if there might be any problems when using RAC (2 Nodes on RH Linux / Oracle
10.2.0.3) and both instances access the same files.
We tested it and it seems to work perfect but is there anything we have to take care of? For
example the logfiles. If an error occurs (rejected rows) both instances might wright into the same
file at the same time.
The two server/Instances have access to the flatfiles over an nfs mount.
Regards
Wolfgang
Followup August 27, 2008 - 9am Central time zone:
you need to have access to the file on all nodes that want to read it - nfs will "do", but you could use a clustered file system like ocfs for example as well.
with the generated logs, you just need to make sure they are going where you want them to go, they could go to local file systems - we would not care (eg: if both nodes have a /log/foo/bar device of their own - that'll work, or if /log/foo/bar is a shared disk - that'll work)
Thanks
August 27, 2008 - 10am Central time zone
Reviewer: Bauer Wolfgang from Germany
Thanks Tom.
So apart from resisting the logs in different directories there is nothing "dangerous" from loading
a table in parallel using RAC?
We will initial load a 1 TB online database from files from our mainfraim in about 2 month - I will
report the results.
Followup August 29, 2008 - 11am Central time zone:
bear in mind, "regular" NFS might not be the most concurrent, responsive input method - but not any more or less in RAC than non-RAC (eg: are you able to get the read input rates you would like to get....)
You'll need really good network response and a really fast NFS server in order to do this. Remember, as node 1 is loading and node 2 is loading, they'll be hitting that same resource - the single resource. Might look for a way to direct attach that storage unless the nfs is something like a netapp server.
ERROR IN DIRECTORY
September 21, 2008 - 12pm Central time zone
Reviewer: BISWARANJAN from INDIA
HELLO TOM , I HAVE CREATED A DIRECTORY IN A PARTICULAR LOCATION USING GTHE CREATE DIRECTORY OPTION
, BUT WHEN I AM GOING TO THAT PARTICULAR LOCATION I AM NOE SEEING ANY WITH THAT NAME(WHEN OPENING
THE CORRESPONDING IN WINDOS) .
SO CAN U PLEASE GUIDE ME HOW TO DO THIS?
Followup September 21, 2008 - 2pm Central time zone:
AND YOU WON'T UNLESS YOU CREATED IT IN WINDOS YOURSELF
create directory 'my_dir' as '\foo\bar\whatever'
simply tells Oracle - when you see 'MY_DIR' referenced, we are really talking about the OS directory \foo\bar\whatever. It is a mapping feature, a level of indirection, instead of hard coding a path, you give us a name and a path, you can change the path without changing the name whenever you want.
but we do on create any OS directories
we do not change permissions on them
You must have that directory
You must have the permissions at the OS level such that Oracle can see that directory
external table row limit ?
January 29, 2009 - 12am Central time zone
Reviewer: joshuasingham from Malaysia
hi,
i would just like to check whether there is a row limitation if we use external table say for
example there is over few million rows in the flat file will the table be able to load all the data
Followup January 30, 2009 - 1pm Central time zone:
there is no documented limit other than those imposed by the file system itself.
|