if there is any particular DBFS settings to increase the performance on external table loading currently I have just mounted it with direction just looking for any other ways to improve the reading from the flat file that sits on dbfs on exadata x-2 half rack
View 1 RepliesDoes RMAN backupset (backup to ) on DBFS is supported. I can find ACFS supported but is not mentioned for DBFS. My current customer is thinking backup to DBFS then copy to tape as the interim solution before getting ZFS next year.
View 5 Replies View RelatedOne of my databases which is running on exadata x2-2 , has been restored to non-exadata machine in order to test few things. I had a sub-partition table in the exadata , compressed for query high. test machine (NOT EXADATA) , after uncompressing this subpartiton tables i am getting the following err message :
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type I have executed the following commands :
alter table crm.cm_ncd modify partition P01_CM_NCD nocompress;
alter table crm.cm_ncd modify partition P02_CM_NCD nocompress;
alter table crm.cm_ncd modify partition P03_CM_NCD nocompress;
[code]...
ERROR at line 1:
ORA-12801: error signaled in parallel query server P005
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type If all the partitions are uncompressed why i am getting this error message ?
I am loading data using Oracle External Table.
I am faced with certain errors which I am unable to proceed with.
Forwarding the source code snippet of the script of the oracle procedure.
The Source Code
drop table nar_temp_xtern;
create table nar_temp_xtern
(
cost_centre varchar2(06),
description varchar2(80),
field3 varchar2(80),
[code]....
Errors received :
ERROR at line 1:
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: "column, enclosed,
[code]....
Is it possible to trim the file name while loading into OWB through external tables?
Like suppose I am trying to read a file which has a timestamp value appended in its name. In that case loading into external file would give an error.
I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.
I have attached the code that creates the external table and populates it.
the following situation, I have a directory named /dat/global/stock/ inside this i will get files named differently for example below.abcdef.112dfgrt.2......
Here i want to load this file one by one into the external tables and generate one more file based on some enrichment.
Step 1. Have to take first file and to load into the ext table.
Step 2. Enrichment
Step 3.File generation.
Now here i am facing a problem that in that particular directory i usually get 1000 files so i need to get file one by one and to put in one more directory. how can i get file one by one and generate file by using oracle loader
I have 1M Records coming from an External Data source as a Flat File (using ETL). Now I need only Yesterday's data only to load in my Database Table.
this can be done using Bulk Load and Filter.
write the CODE.
Second Part:-
Hint: if I need to update only those records been updated Say the Address1 field is updated. So this records need to update in my Master Customer Table.
If I have many fields in table and any records that are modified (coming to me from External Datasource as a Flat file) how to identify and update that record in my Master Customer Table?
This is the below method which I have used to figure out exadata model.
ex: grep -i MACHINETYPES /opt/oracle.SupportTools/onecommand/databasemachine.xml <MACHINETYPES>X3-2 Half Rack HP</MACHINETYPES>
But for one environment, though it is a full rack but still I am getting result as half rack.
I am having an Oracle 11g R2 RAC setup. My database size is 22TB in normal servers. I have checked my database in Exadata X2-2 and found out that the HCC was good and as per my assumption the 22TB can come down to 10TB .
My challenge is i need to transfer the 22TB data to the exadata quater rack which is having space constraint. Is there any way other than export & Import as data export and import will use entire 22TB. Is there any way to transfer the data to Exadata with compressed form from the source server.
I am having doubt on the no_index concept in oracle. I am using oracle exadata server.It is basically data warehouse project.I am in the situation to join some tables and get the result set for reporting purpose.
Among the tables, 2 tables having huge count.1st table has more than - 1,000,000,000 rows2nd table has more than - 200,000,000 rows when i join these 2 tables with some small set of tables, it is taking long time (around 20 MIN) to retrieve the result set. The final result set is around 100 rows only.
But, when i force NO_INDEX hint in the same query, is giving the same result in very fast manner(around 5 MIN). Because it is working based on cell smart scan. So, can i force the NO_INDEX hint to all tables? I forced the NO_INDEX hint only the table which is contain 200,000,000 rows not for others.
Query Plan : Normal Query using the the range scan based on the key. No_INDEX Query going full table scan.
I would like to know if I can create one OS user by ORACLE_HOME database ?
View 3 Replies View Related11.2.0.3 win 2003. have direct aim to restore database from inc0+inc1 backup + recover up to latest archives made after inc1.
Steps have to be done:
1. SQLPLUS> startup nomount from pfile='......';
2. RMAN> restore controlfile from ...
3. SQLPLUS > alter database mount;
4. RMAN> run
{
[code]...
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/08/2012 13:30:20
[code]....
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
795 108.13M DISK 00:00:16 05-OCT-12
BP Key: 831 Status: AVAILABLE Compressed: YES Tag: BACKUP_WORK_00000
[code]...
Questions:
1. Why it ignoring "set new name" and db_file_name_convert??
2. Why no backup or copy of datafile 1,2,3,4 found to restore, it can seee it clearly through all its commands?
Tasks need to performs are
1) Post Installation check list ie: How can I see every things are installed correctly.
2) upgrade the exadata OS version .
3) Upgrade the Database.
4) Test Migration from Oracle database to Exadata.
5) Right now there RAC servers are installed without domain, I need to add domain name to existing RAC.
following is the requirement
External Table
WKSHT_FILE_EXT
wksht_line
Export Table
Wksht_export
global_idvarchar2(10)
wksht_linevarchar2(250)
[code]....
Step 1.Insert all records from the external table into the export table. Truncate the export table first
Step 2.Read in a record from the export map table
Step 3.Search through export table records looking for the key words BRANCH =. Compare the branch code with the branch code form the map table
Step 4.If a match is found mark all records in the export table for the worksheet with the global ID from the export map table as follows..The first line of a worksheet is marked by the words WKSHTS..The last line of the work sheet is marked by the words COMPANY CONFIDENTIAL..We will need to capture the line break so also mark the next line after the COMPANY CONFIDENTIAL line
Step 5.Continue with Steps 2 - 4 until all records have been processed from the export map table.
first I have to create a procedure ti insert data from external table to export table.Global id will be blank.it will be updated by the mapping table's Global Id when The EB COLUMN's data(i.e 8p,2Betc ) will match with the BRANC=NA,2Betc of the datasheet loaded from the external table.. FOLLOWING IS THE SAMPLE DATASHEET
WKSHTS AAAAA BBBBBBBBBBB ELECTRONICS INC. TIME REPORT-DATE PAGE
SORT - BR, SLSREP AEC FIELD SALES REPRESENTATIVE 16:14 09/21/12 1
BRANCH = 2B
EMPLOYEE NAME SALVAAG, GREGG Days in the Month 28
[code]....
THERE ARE 2 pages..I have to split this LONG REPORT STORED IN WKSHT_LINE COLUMN OF EXPORT TABLE to 2 records..like wise 500 pages are there means 500 records.. AND THEN FIND BRANCH= after that which two words will come i.e NA,2B etc if it will MATCH WITH MAPPING TABLE"S EB COLUMN"S DATA,THEN MAPPING TABLE's GLOBAL ID WILL BE UPDATED TO EXPORT TABLE's GLOBAL ID WHICH IS BLANK
I'm experiencing some problems when trying to import an 50Mb XML file to an Oracle database. In this XML file I do have several data from customers:
- Name
- Address
- Contacts
I also have several tables within the DB that would receive this information:
- Customer
- CustomerAddress
- CustomerContacts
The problem is that, with my XML Transformation and correspondent insertion onto the databse I'm having an huge problem of time expended. I'm having more than 3 hours to insert over 180.000 records on those tables. what can I do to accelerate the process?
I am trying to load a single column into a Table. Below is the ctl file:
OPTIONS (ERRORS=10000)
LOAD DATA
TRUNCATE
INTO TABLE "ECOMM"."CC_ECOMM"
TRAILING NULLCOLS
(EMAIL_ID)
Calling loader:
sqlldr silent=ALL userid=username/password@DB control=/home/DEV/control/CC_ECOMM.CTL log=/home/DEV/log/Unsubs.log bad=/home/DEV/log/Unsubs.bad discard=/home/DEV/log/Unsubs.dis data=/home/DEV/files/CC_ECOMM.TXT
Though the count in the Table after load is the same as the input file, they are all NULL.
I tried adding FIELDS TERMINATED BY X'A' for new line and also FIELDS TERMINATED BY X'D' for carriage return. Both times bad file was created and the records that were loaded were again NULL.
The input file has a list of emails:
iatraveler2008@aol.com
iaz65@aol.com
2blue2brown@comcast.net
2c3mwilson@embarqmail.com
abigailolschan@comcast.net
imisskoco@aol.com
I tried FIELDS TERMINATED BY X'10' and FIELDS TERMINATED BY X'13' too for new line and carriage return respectively. This time there were no bad file created, but the Table has Null values.
Log File contents:
Number to load: ALL
Number to skip: 0
Errors allowed: 10000
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Silent options: FEEDBACK, ERRORS and DISCARDS
[code]....
We are getting the below error frequently from the application while doing insertion/dataloading to a table. The mentioned error is in the Primary key index
Error: 'ORA-01502: index 'INDEX_NAME' or partition of such index is in unusable state'.
I set the value SKIP_UNUSABLE_INDEXES = TRUE using the command 'ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES = TRUE' to avoid this. Again we are getting the same error and Every time Iam rebuilding('alter index INDEX_NAME rebuild') the index and doing the DML Operation.
I was trying to load data from XML files to an Oracle database table.I followed these below steps to load that file data into a table. Created XML_DIR1 as oracle directory where i have kept all XML files.
Create table import_rpt_xml of xmltypexmltype store as binary xml; insert into import_rpt_xmlvalues (xmltype (bfilename('XML_DIR1','I-Yamanouchi-20040525-501.xml'),nls_charset_id('AL32UTF8')));
This insert shows below error: Error starting at line 80 in command:insert into import_rpt_xmlvalues(xmltype(bfilename('XML_DIR1', 'I-Yamanouchi-20040525-501.SGM'), nls_charset_id('AL32UTF8')))
Error report:SQL Error: ORA-31061: XDB error: XML event errorORA-19202: Error occurred in XML processingIn line 69 of orastream:LPX-00217: invalid character 142 (U+008E) I tried to look into my XML and got that it has some Japanese characters in it.
this to deal with japanese characters in XML. I don't want to miss those characters. My databse NLS_CHARACTERSET is 'AL32UTF8'.
My sample XML file looks like this.
<ichicsr lang="ja">
<ichicsrmessageheader>
<messagetype>ichicsr</messagetype>
<messageformatversion>2.1</messageformatversion>
<messageformatrelease>2.0</messageformatrelease>
<messagenumb>US-Yamanouchi-W2004050033-4</messagenumb>
<messagesenderidentifier>Yamanouchi</messagesenderidentifier>
<messagereceiveridentifier>PMDA</messagereceiveridentifier>
[Code]...
and so on.
The manual work around on populating child tables for testing purpose are taking long time and its very painful work. So I am trying for a tool that takes parent table name and child table name as input and produce insert statements for child table with foreign keys as output.
View 5 Replies View RelatedI need to do some analysis and research to find if any of these 8000 tables I have in a spreadheet that are going to be spit off into a separate database are used by any of our PeopleSoft processes.
I'm assigned to Student Records identified by NTSR.
So for table AAP_ETHNIC_PMPT, I could do something like
SELECT *
FROM PSSQLTEXTDEFN
where SQLID like 'NTSR%'
AND upper(sqltext) like '%PS_AAP_ETHNIC_PMPT%';
But how can I automate this and search 8000 rows in column A2 of a spreadsheet? What other tables other than PSSQLTEXTDEFN or PSPROJECTITEM can I use to search for values of NTSR?
Every day we have use different data file for an external table to read from.
filename like this "filenameddmmyyyy"
Every day, I have to change name of datafile in external table script which is not a good way.
I want the automated way for external table to read the latest datafile from specified directory.
Further, there is anyway that we could know that the latest data file is available for external table.
when i am writing dump from external table, it is accessing records from dump.but when i am trying to access other dumps(create thru expdp) it is giving error.the logic i am following is mentioned below-
CREATE OR REPLACE DIRECTORY "DIR_GMS" AS 'D:Gopal_works est_env_files'
GRANT READ ON DIRECTORY dir_gms TO gopal;
GRANT WRITE ON DIRECTORY dir_gms TO gopal;
-- creating dump file in directory
CREATE TABLE emp_ext
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir_gms
LOCATION ('emp_ext_dmp.dmp')
)
[code]......
i am able to see records.
New point:
-- taking export thru expdb
expdp hr/hr tables=EMPLOYEES directory=DIR_GMS dumpfile=HR_EMP.dmp logfile=expdpEMP.log
then i created one EXTERNAL TABLE TO access it.
CREATE TABLE emp_xt (
EMPLOYEE_ID NUMBER(5),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50))
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir_gms
LOCATION ('HR_EMP.DMP')
);
while accessing, it is giving error:
SELECT * FROM EMP_XT
ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-31619: invalid DUMP FILE "D:Gopal_works est_env_filesHR_EMP.DMP"
ORA-06512: AT "SYS.ORACLE_DATAPUMP", line 19
I'm trying to create a external table column validation.
I have data like this
First column is Numeric, second is Non-Numeric
1,X
2,Y
3,Z
There are chances that I may have data as following
1,X
2A,Y
3,Z
My requirement is whenever I get non-numeric data in first column I want to pass 0 or NULL. So, I have created a external table with following script.
CREATE TABLE EXT_test
(
SL NUMBER (4),
BRANCH_NAME VARCHAR2 (100)
)
ORGANIZATION EXTERNAL
(
[code]........
Following is the code for function is numeric
function isnumeric( p_string in varchar2) return boolean as
l_number number;
begin
l_number := p_string;
return TRUE;
exception
when others then
return FALSE;
end;
It is not recognizing function IS NUMERIC. Is there any way, I can do this through External Table.
I'm getting error like
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 "identifier": expecting one of: "comma, date_format, defaultif, enclosed,
(, ltrim, lrtrim, ldrtrim, notrim, nullif, optionally, ), rtrim, terminated"
KUP-01008: the bad identifier was: isnumeric
KUP-01007: at line 9 column 16
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
I have an external table. The table gets created successfully. Once the table is created when I try to access it, I get the following error :
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file /cmpnt/dev/test/ADE_TEMP_4992.log
The default directory is valid and does not have any issues. The IP address of my DB server and the server from which I am connecting to the DB are different. Is this is the issue ? However , all SQL queries are working fine except this one.
i had small query on external tables. i had (.csv) file outside the database. In this file, one column will be added monthly. i need to create the external table dynamically by adding the column for every month.
How the procedure can be created for this requirement.
when i just execute
CREATE TABLE ext_schoolof
REJECT LIMIT UNLIMITED;
then it gets " select * from ext_schoolof " BUT when i use procedure, it creates external table but when I try to get " select * from ext_schoolof ", then I get errors
The error numbers are
ORA-29913
ORA-29400
KUP-00554
KUP-01008
KUP-01007
[code]....
Provide material for External table concept .which is like exactly as SQL * Loader concept.
What are the basic difference between External table concept and SQL * Loader concept.
what empty blocks are, and how to remove them.What I'd like to do is not have empty blocks in the first place on loading a table. I load a lot of "static" tables and would like to not have any wasted space at the end, with minimal shinanigans.
I've set pctfree 0
I"ve set initial to close to the end table size
I've set next to 1M
I've set pctincrease 0
blocksize is 8k
Yet I still need to at least do an alter table deallocate unused
I am loading data from XML file into Oracle table.This program is working fine for small XML files. If I try to load large XML file with multiple pages, only first ten records are loaded. Here is the procedure.
PROCEDURE Test_xml_read(p_tag varchar2,p_xml_file varchar2,p_path varchar2)
AS
BEGIN
INSERT INTO stg_xml_table(
Productid,productname,price)
select y1.productid,y1.productname,y1.price
y2.categoryid,y2.categoryname,y2.categorypath
FROM xmltable('ProductFeed/Products/Product'
passing xmltype(bfilename('TEST_DIR1', 'sample.xml' ), nls_charset_id('CHAR_CS'))
[code]...
what changes to be done to load multiple pages of data pages table.