Server Utilities :: Difference Between Sqlloader And External Tables?
Feb 9, 2011I would like to know which of the above is faster for the same conditions.
i.e. If I am loading 1 million rows for the same conditions which will perform faster?
ADVERTISEMENT
Server Utilities :: Views Linked To External Tables
Mar 28, 2011i just posted another topic where i heard about external table and i had a few questions concerning them. I thought it was best to create a new topic than to continue on the other one...
I noticed that to create an external table the CTL is like this:
CREATE TABLE emp_load (FIELDS description)
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (RECORDS FIXED 62 FIELDS (employee_number CHAR(2),
[Code]...
1) This creates an external table, but, is it possible to Create a normal table in a CTL file? For physical tables, the table has to exist right?
2) if you create a view linked to 2 external tables and if the CSV files are updated each day, the external tables will be updated automatically, and the view will be updated as well?
3) Can't there be any synchronisation problems?
4) What happens if a select request (or someone requests on the view) while the CSV file is being updated?
5) Is there anyway you can protect the accesses from those tables/views when the CSVs are being updated?
6) Is it possible to create an index on these sort of tables?
7) Is it possible to index a view?
8) Are external tables visible on a tool like sql developper?
Server Utilities :: CONCAT Fails In SQLLoader?
Mar 2, 2010I'm trying to concatenate a local phone number field. The LDAP system only has the last 5 digits but for the directory database we need all 7 digits.I've tried every combination I can think of to get the concatenation to work but every combination results in just the first two digits being imported, e.g.,
LOCAL_NUM "'20'||:local_num",
results in just 20 being imported. Every iteration I've tried that didn't result in an error imported only the 20 and ignored the ||. I've also tried calling the CONCAT directly, e.g.,
LOCAL_NUM "CONCAT('20', :local_num)",
result is the same.The problem seems to be that the loader is ignoring the concatenate statement all together. I've tried the statements outside of the loader via sqlplus with expected result so I'm confused as to why it's not working within the loader.
Server Utilities :: Sqlloader Detect Invisible Characters
Aug 20, 2013Is there a way to detect bogus characters in the datafile?
SQLLoader on original file
Record 1: Rejected - Error on table DP, column STARTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Copy the data in the controlfile using notepad++: no errors
Server Utilities :: Return A Column Value Using Sqlloader After Loading
Dec 1, 2011I have the following table intra_trades with t_id as the primary key. There is a trigger on that table that gets the next sequence and inserts it into the t_id column for every insert. I need to load data into that table using SqlLoader as chunks of 3000 rows and return the t_id back the script that Sqlload the data so that it can use that t_id's for the next process in the script.
intra_trades
t_id NUMBER(15) pk
t_name VARCHAR2(30)
t_loc VARCHAR2(40)
t_start TIMESTSTAMP
t_end TIMESTSTAMP
[code]....
The problem is that the only unique key on that table is the t_id which has a sequence on it and it is the pk. There can be duplicate rows in that table to meet the business needs for the company. So it is hard to associate the rest of the data in a row with t_id. The only thing I can think of is return the t_ids in the order it inserted so if the script keeps the order of rows in the memory it can associate the tid with the rest of the intra_trades info.How can I make the sqlloader return an array of t_ids that inserted? I need to return the t_ids's in the order it inserted so that the script can associate the t_id with the rest of the rest of the data in a row.
Server Utilities :: How To Get Proper Value In External Table
May 3, 2012getting proper value from the file in external table.
How can I get the whole status in STATUS column like completed , Inprogress, incompleted.
Right now, if I gave position like (38:9) full status doesn't show. if I give (38:11) then '|1' is adding in status from the flat file.
BATCH_NO FILE_DATEEMP_ID COMPANY_ID TRANSACTIN_ID FILE_NAME STATUS DOC_NO
10000104252012100001***4252012**1:35:57***D100001***04252012***10:35:57***Diverified
[Code].....
Server Utilities :: SELECT On External Is Very Slow?
Aug 10, 2013I just did a 112G file migration of production data using oracle_datapump so I know this works in principle. When I tried it on my test instance I am seeing stuff like this
[oracle@aggs00.test for_test]$ ls -l aggs_day_conversion_agg_2419
-rw-r----- 1 oracle oracle 15917056 Aug 10 09:06 aggs_day_conversion_agg_2419
CREATE TABLE IMP_3251198_2419(
PARTITION_DATE DATE,
USER_ID NUMBER,
SID NUMBER,
[code]....
Executed in 1800.642 seconds
why it could be taking 1800 seconds to select one record from a not very big table? File corruption? Disc fragmentation? Oracle instance configuration?
Server Utilities :: How To Create External Table
Aug 10, 2012im trying to create an external table, and i load my data without no problem, and everything is fine, but i got some behavior with one column that i would like to know whats behind scenes, OK let's get the example:
[*] Sample Data
Line 1:333 1111111112009100000000000080000000013450.33
Line 2:11111111111220091016000000004.48
Line 3:222222222 220091016000000004.48
Line 4:(This is a blank line left)
And this is my External Table Create Query:
CREATE TABLE EXT_TABLE_TEMP
(COL_A VARCHAR2(11),
COL_B VARCHAR2(1),
COL_C DATE,
COL_D NUMBER(12,2))
ORGANIZATION EXTERNAL
[code]....
As you can see i can upload my table with no problem but i always get 3 lines counting last blank line if i try LOAD WHEN COL_A != BLANKS, i dont know if its a problem of the blank space left between fixed fields length, but if i do LOAD WHEN COL_B != BLANKS i get correct result 2 lines instead of 3, i want to know why (missing fields...) and (reject rows...) are not working...
Note: COL_A could be 9-11 length, if length its 9 then 2 spaces left before next one...
Server Utilities :: Skip Last Line In External Table
Jun 15, 2012how to skip last record while loading external table.
the example below I don't want to load the 000000005 in the AAA external table.
My Data is
100001***04252012***06:02:40***CignaGlobalHealthBenefits
201441424_7076551_OLC_1234567899.aaa
201441424_7075703_OLC_3456789134.aaa
201442669_7075775_RIE_5432167891.aaa
700223567_7077646_ECS_2345678912.aaa
700331352_7078197_RIE_5678901234.aaa
000000005
[Code]...
Server Utilities :: SQL Loader Or External Table With A Trigger?
Aug 14, 2013I have to have a sequence added to a large(288 million rows) file when I load the file into the table. If I use SQL Loader I can't use direct since I have a trigger for each row for the sequence but I am not sure if an external table will be any faster since the trigger will be firing for each row also. In this scenario is one better than the other ?
View 8 Replies View RelatedServer Utilities :: External Table - Data Cartridge Error
Aug 6, 2010i created the External Table using the script below.
CREATE TABLE EXT_ST_FINANCEIRO_REAL (
DT_DATA NUMBER,
TIPO NUMBER,
ENTIDADE NUMBER,
VALOR Varchar2(40))
[code]....
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 "missing" expecting on of: "column, exit,("
KUP-01007: at line 6 column 1
ORA-06512: at "SYS.ORACLE_LOADER", line 19
Server Utilities :: Getting Localconfig Is Not Recognized As Internal Or External Command
Dec 30, 2010While i like to start CSS service to create new ASM instance in my own pc for testing purpose gettting the below errors "'localconfig' is not recognized as an internal or external command, operable program or batch file.".
View 1 Replies View RelatedServer Utilities :: External Table Definition And Content Of CSV File
May 26, 2010Below is the external table definition and the content of csv file, why the first record fails ?
0546-0*LB-CRP*16*"Tech", ZAO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 --FAILED
0546-0*LB-CRP*16*ID"Tech", ZAO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 -- SUCCESS
0546-0*LB-CRP*16*"Tech, ZAO"*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 -- SUCCESS
[code]....
Server Utilities :: Append Tables Content To Existing Tables?
Nov 9, 2010problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.
we have found something called table_exist_action=append in impdp.
Server Utilities :: Difference In Character Sets Of Server And Client?
Mar 16, 2011I've a question regarding difference of character sets, while taking a export(logical backup) of database on directly to server(linux RHEL 2.1 AS) and export on a client (windows xp prof machine, where only a oracle 9i client is installed). On server it seems to fine and okay, but on client node i'm getting following error for almost all tables.
EXP-00091: Exporting questionable statistics.
My question is :
[1] Is it creating any sort of problem, if later on i import the data which was taken from client node.
[2] Why there is a difference(marginal) in dump(.dmp) file size.
[3] Is there any way to overcome it, or it is the natural behave of it. Means not a problem.
[4] If i'm using a long or blob as datatype for some of my table,is they have any problem if i persist like above.
Additional Information about character sets On server node :
Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion)
On client node :
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set server uses US7ASCII character set (possible charset conversion)
Server Utilities :: Difference In Size Of Exports
May 7, 2011Recently i've migrated from Oracle 9i SE(9.2.0.1.0) to Oracle 11gR2 EE(11.2.0.1.0). Previously i'm taking export of some of my schema and it's file size was around 1g.(with exp utility of Oracle 9i). As per earlier practice now i'm taking export of same schema with same no of objects and same data volume, the size of export file size on Oracle 11gR2 database is significantly gone down , actual size around 825mb(with expdp utility of Oracle 11g).
So i would like to know why there is a difference in file size(.dmp files) of export files between two oracle versions. I have crosschecked objects and rows of data tables. It is perfectly same.
Command line parameter for export on Oracle 9i
exp test/test FILE=test.dmp OWNER=test GRANTS=y ROWS=y COMPRESS=y LOG=test.log
Command line parameter for export on Oracle 11g
expdp test/test DIRECTORY=dpump_dir DUMPFILE=test.dmp LOGFILE
=test.log
Server Administration :: Find Out Difference Of Data For Some Tables Between Current And Previous Day
May 26, 2011We want to find out difference of data for some tables between current day & previous day. We can use query with minus operation but it will take lot of time since table size is in range from 200 to 500 GB. We have to do this exercise every day.
View 5 Replies View RelatedServer Utilities :: Oracle10g Imp Tables Containing LOB
Nov 23, 2010A full database exported from Oracle 10.2.0.4 and import back to the same instance with different user/schema name. The whole database is in single tablespace, SOURCE_TS. Since SOURCE_TS already exist in the same instance. A brand new user and its default tablespace will be created before the import. SOURCE_TS cannot be access by this new user.
Since my database has many tables containing LOB column, those LOB tables will be pre-create before the import start. Initially, those LOB tables imported successfully but at one stage, it fails. Subsequent LOB tables all failed since then.
Quote:IMP-00017: following statement failed with ORACLE error 1950:
"CREATE TABLE "I43NAV_LOC_VEC" ("VID" NUMBER NOT NULL ENABLE, "BLK_NO" N"
"UMBER NOT NULL ENABLE, "DATATYPE" NUMBER, "VEC_STORAGE_TYPE" VARCHAR2(12) N"
"OT NULL ENABLE, "REL_PATH" VARCHAR2(256), "BYTES_USED" NUMBER, "BLOB_DAT"
"A" BLOB, "BFILE_DATA" BFILE) PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 "
[code].......
I am not able to find out any reason why it fails, since out of 150, 80 of my tables containing LOB column were imported correctly suggested that my imp parameters were correct.
It doesn't seems to be my backup file problem too, I tried twice and the first LOB table failed is not the same with the previous attempts.
Initially I thought might be quota, but unlimited quota to the new tablespace is granted. Even if it is not granted, the error returned would be complaining about quota limit reached in the new tablespace, not SOURCE_TS.
If this is not about disk space or quota issue, in what condition the imp will tries to create table in SOURCE_TS, instead of my new tablespace? I will need to understand the IMP utility logic to sort this out.
Server Utilities :: Exp / Imp Tables From One Tablespace To Another?
Oct 19, 2012C:UsersNeetesh>expdp system/*****@orcl2 dumpfile=temporary.dmp tables=testuser.test,testuser.test2
Export: Release 11.2.0.1.0 - Production on Fri Oct 19 16:39:06 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/*****@orcl2 dumpfile=temporary.dmp tables=testuser.test,testuser.test2
[code]...
then i import it in new user
SQL> create user temp identified by temp;
User created.
SQL> grant create session ,create table to temp;
Grant succeeded.
SQL> alter user temp quota 10 m on users;
[code]...
it gives 2 errors both are same as -ORA-01950: no privileges on tablespace 'new_tbl'
exported table are exist in this 'new_tbl' tablespace but this is not exist in importing database.then is there any way to import these tables in 'users' tablespace or other tablespace other than 'new_tbl'?
SQL & PL/SQL :: UNC In External Tables?
Aug 10, 2011I made a directory and a external table, pointing to a file in the created directory. It appears that using unc path in my directory, won't work with my external table. Although the unc path points to the same server as where the database server is located.Is my assumption correct?
create or replace directory DIVA_POK as '\tvm011gm_diva_dir$ronnendbga_pok'
create table KOT_AOT(
AOT_IDENTIFICERENDE_SLEUTELvarchar2(14),
AOT_BEGINDATUMGELDIGHEIDdate,
AOT_EINDDATUMGELDIGHEIDdate,
[code]...
Server Utilities :: Import Only Tables Of A Schema
Jul 26, 2013The way to do IMPORT of a schema's ALL the TABLES only. Through Data Pump. I don't want to import any other objects like 'package',procedure etc...
Is it the only way is specifying them within EXCLUDE parameter?
Server Utilities :: How To Backup Tables In A Schema
May 31, 2012How to backup the tables in a schema? I need som halp on this subjekt. I hav Dev envrnment that I must to backup.
View 9 Replies View RelatedServer Utilities :: Loading Data Into Tables?
Oct 29, 2010We completed creating a replicate of dB_01 to dB_02 (housed in a single DEV server). But dB_02 had only table structures (no records). What would be our fastest option (tools, commands, etc.) to load more than a thousand new records for each of the 20 tables of dB_02?
View 7 Replies View RelatedServer Utilities :: Export Multiple Tables Only?
Jul 10, 2012send me the command for exporting multiple tables(1000+) in Linux env. 9i db, i know we can do using spool command but dont know exactly how to put it. i know using Datapump but this is 9i.
View 7 Replies View RelatedSQL & PL/SQL :: Looping External Tables?
Jul 31, 2012 I am using an external table with numbers and joining the external table to fetch data and Insert into another table.I am altering the table within procedure to pass csv file name as parameter as It'll change each time. It's working fine but in case if my grouping id is having two gids, then Header from csv file is also inserted as row in to coupon_personlization_members.
I gave skip 1 in external table but for second GID, header is inserted as row.I don't want header to be inserted as row but header will be there on csv file.
Here is my procedure :
CREATE OR REPLACE
PROCEDURE proc_coupon_load(
p_grouping_id NUMBER,
p_file_name VARCHAR2)
[code]..
SQL & PL/SQL :: Creating External Tables
Jan 26, 2011I have created a softlink to a data file orig_file.dat using the command ln -s orig_file.dat orig_file_link.dat
Now is it possible to create an external table using this linked file orig_file_link.dat
I am using this linked file to minimise the space usage because every month we get different data files and external table creation is done as a batch job copying the data file to another file whenever a new data file arrives. External table is created with the new file. so i need to check whether it is possible with the linked file instead of copying the file.
Server Utilities :: How To Export User Without Data Of Few Tables
Jul 29, 2010I need to export a user with all the tables. But I need to export data into only few tables, need to omit the data of few tables.
Ex I dont want to export data of Audit tables with AU prefix.
Server Utilities :: Excluding Data Of Some Tables In EXPDP
Oct 5, 2013i want to exclude only data of some particular tables not complete table object when exporting using expdp.
View 13 Replies View RelatedServer Utilities :: Import Dump File Without 2 Tables
Jan 3, 2012I want to import dump file (without 2 tables) .The dump file contains 100 tables,indexes and constraints. So out of 100 tables i want to import 98 tables from dump file (without 2 tables).
View 13 Replies View RelatedServer Utilities :: How To Export Global Temporary Tables
Apr 18, 2013There's an Application Express application which is based on a schema named TRAFOGLED. In order to let users test new features, there's a test application (Apex has export/import capabilities; no problem about that) which is based on another schema whose name is TRAFOTEST.
I'd like to export TRAFOGLED and import it into TRAFOTEST.I'm using 10gR2 EXPDP utility with a parameter file. Everything seems to be OK, except the fact that I'm unable to export global temporary tables (GTT). How can I tell? I didn't see them after import!
These are my GTTs:
SQL> show user
USER is "TRAFOGLED"
SQL>
SQL> select table_name from user_tables where temporary = 'Y';
[code]...
C:TEMP>
No tables were exported. Certainly, I don't expect any data to be exported, but I'd be happy with CREATE TABLE statements so that I don't have to create these tables separately.