SQL & PL/SQL :: Combining Rows In Data File Based On Values In Control File?
Aug 29, 2013
I have to load data file into a table. And the requirement is as below:
Input Data:
1234|20130815|20130822|This is a test, this is the the part
3456|20130823|20130809|This is a test
3456|20130823|20130809|This is a test
3456|20130823|20130809|This is a test
3456|20130823|20130809|Siva 1234
The data should be inserted only in two rows as below:
When Value in first 3 fields is same, 4th field should be appended to the existing value in table.
1234|20130815|20130822|This is a test, this is the the part
3456|20130823|20130809|This is a testThis is a testThis is a testSiva 1234
View 3 Replies
ADVERTISEMENT
Dec 22, 2010
The following control file updates multiple rows in database table.
LOAD DATA
INFILE *
replace
INTO TABLE temp_tab
FIELDS TERMINATED BY ","
(Data LOBFILE(CONSTANT cadd_pass.xml) terminated by eof
There are 21 lines in the xml. So 21 rows are updated in table.update only one row?
View 9 Replies
View Related
Jan 15, 2013
My Table structure
column1 varchar(10)
column2 Date
Column3 varcahr(2)
Column4 varcahr(2)
By Data file
asds 12/12/2001asas
textsd asds
asds 12/12/2001asas
ramkiy asds
I still want to insert row 2 and row 4 into table by defaulting the date. how can I handle this in control file?
View 14 Replies
View Related
Nov 5, 2010
I want to change dbname,sid,data/control file locations in operational dataguard setup i plan to follow as below
1)shutdown primary and standby (stop managed recovery)
2)change db name in init.ora of primary and standby change database name control file location
3)create control file for primary from trace(script) make changes for db name and file locations
4)mount and open primary database
5)create standby control file
6) transfer standby control file to standby
7) mount stand by database and start manage recovery
If this steps will error free do i need to follow any thing additional to this or what is other best way for this or its not possible at all
View 1 Replies
View Related
Feb 21, 2011
is there any way you can get the name of the file loading the table in a control file? i have a table with a column called source_file, and need to populate it during the load.
View 1 Replies
View Related
Apr 8, 2010
I am using perl script to dynamically generate the control file.If I have data in the control file as well as in the datafile, how would i write the control file in that case. Is the below one correct?
load data
INFILE '*'
INFILE '/export/home/test/test.csv'
INSERT INTO TABLE EMP fields
terminated by "," optionally enclosed by '"'
trailing nullcols
( empno, empname, sal, deptno )
[code]....
Is there any way that if my control file contains half of the data and my data file contains the other half of the data can i club this data into a logical record in the control file to populate the DB?
My exact 2nd requirement is, my DB contains 5 cols and for 1 col the data is common(countryName) which i have to pass to the control file dynamically and the .csv file contains the data for the other four cols. How could i combine these in the ctrl file and populate the DB?
so if the DB contains CountryName, empid, ename, sal and dept..I will get the CountryName to the ctrl file and csv contains the data for empid, ename, sal and dept. How would i combine these data into a logical record and populate the DB?
View 12 Replies
View Related
Dec 29, 2009
I try to recover standby db from a primary db backup set,but the standby control file is different.
to create primary controlfile by rman:
backup control file format '/home/cur_control';
to create standby controlfile:
backup control file for standby format '/home/std_control';
I want to know if there is method to convert cur_control to std_control.
View 3 Replies
View Related
Dec 21, 2011
how to create control file and how to load the data through command window in our database using sql * loader.i am having structure in my database and .csv file in my desktop.
View 20 Replies
View Related
Jan 26, 2012
My oracle table having 2 fields.
filed1 VARCHAR(500)
field2 NUMBER.
i load data to this table from a file using sqlldr.
what is the proper data type should i use in control-file for both the fields.? i dont mention any datatye in ctl file which is working fine with given dataset.
View 19 Replies
View Related
Jul 25, 2013
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
View 4 Replies
View Related
May 15, 2012
I have an 11gR2 data base with Local Extent Management and Manual Segment Management. Can Data Guard be used to replicate this instance into ASM storage? [I have multiple long fields (BLOBs and CLOBs) in various tables.]
View 3 Replies
View Related
Aug 16, 2011
I have a table revenue
create table revenue
(
person varchar2(23),
month varchar2(3),
rev_amt number
)
and i have data in a file like below
Person Jan Feb Mar Apr Mai Jun Jul Aug Sep Oct Nov Dez
--------------------------------------------------------
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
Müller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678
i want to load it into the table in the following way.
Person Month Revenue
-------------------------
Schnyder Jan 345
Schnyder Feb 223
Schnyder Mar 122
Schnyder Apr 345
Schnyder Mai 324
Schnyder Jun 244
Schnyder Jul 123
Schnyder Aug 123
Schnyder Sep 345
Schnyder Oct 121
Schnyder Nov 345
Schnyder Dez 197
........ ... ...
How to write control file to load this data into the above revenue table.
View 2 Replies
View Related
Jul 4, 2012
What is difference when you issue a create controlfile with "standby" keyword on the primary database
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/path/db_stby.ctl';
vs. creating a control using
ALTER DATABASE BACKUP CONTROLFILE to '/path/db_control.ctl';
what does ORACLE put into the standby control that is "extra"? ie. what is the difference between a standby control file and a normal control file?
View 7 Replies
View Related
Mar 22, 2007
I am an Oracle beginner and I am having some trouble with the following insert query.
I am inputting values into text boxes and then this is carried out as a trigger upon clicking a button.
INSERT INTO client VALUES(':student.txtclientid', ':student.txtclientname', ':student.clientaddress', 13564338);
INSERT INTO enrolment VALUES(':student.txtclientid', ':student.lstoccurrence', null, null);
The above text boxes are all working fine as I have viewed the values using the message command. My proplem is that if i leave the fields blank it inserts ':student.txtclientname' into the row, otherwise it returns "Could not insert record"
View 1 Replies
View Related
Jan 26, 2012
I have an oracle autoback of controlfile and spfile. I am trying to restore the production database using this backup, into a new server with a different directory structure.
I have completed the following steps in the restore process.
1. Creating a pfile from spfile
2. Changing the the location of cdump, udump, bdump and the control files ( there are three )
3. Then creating a spfile from this pfile
I am stuck on the next step where to rename all the datafiles and tempfile and restore the database. Following is what I did.
After mounting the database in RMAN, tried to run the following.
RMAN> run
2> {
3> allocate channel c1 device type disk
4> ;
5> @/home/oracle/rman_scripts/newloc.rman
6> SET NEWNAME FOR DATAFILE 1 TO '/u02/oradata/dorian/system01.dbf';
7> SET NEWNAME FOR DATAFILE 2 TO '/u02/oradata/dorian/undotbs01.dbf';
8> SET NEWNAME FOR DATAFILE 3 TO '/u02/oradata/dorian/sysaux01.dbf';
9> SET NEWNAME FOR DATAFILE 4 TO '/u02/oradata/dorian/users01.dbf';
[code]....
I am getting the following error message.
Starting restore at 25-JAN-2012 21:26:48
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/25/2012 21:26:49
RMAN-06026: some targets not found - aborting restore
RMAN-06100: no channel to restore a backup or copy of datafile 30
RMAN-06100: no channel to restore a backup or copy of datafile 29
RMAN-06100: no channel to restore a backup or copy of datafile 28
[code]....
View 14 Replies
View Related
Jun 15, 2012
I had some query.
Actually had some table with following information
Here A,B,C are the vendors and Code is 21 and Amount is mentioned
Code Vendor Amount
21 A 56
21 A 62
21 B 31
21 C 100
21 C 100
Want to display top three vendor for that Code based on Vendor Amount
Code Vendor Amount
21 C 200(100+100)
21 A 118(56+62)
21 B 31
How should i do this?
View 10 Replies
View Related
Nov 3, 2012
i have two tablespaces dictionary managed (SYSTEM,APPLSYSX) i tried to change to locally cause it will cause problem in future when trying to run OATM migration.i did it successfully on APPLSYSX,when i did it on system upon oracle procedure.i have to change all tablespaces to read only when i did that with tablespace APPLSYSD(alter tablespace APPLSYSD read only) i received errors
SQL> alter tablespace APPLSYSD READ ONLY;
alter tablespace APPLSYSD READ ONLY
*
ERROR at line 1:
ORA-01230: cannot make read only - file 636 is offline
ORA-01111: name for data file 636 is unknown - rename to correct file
ORA-01110: data file 636: '/vol5u/oracle/prddb/9.2.0/dbs/MISSING00636'
i have not this file on the OS
View 1 Replies
View Related
Jul 24, 2011
In my system all my control files get corrupted.
FYI,I have taken control file backup and DB backup also 6 hour before the control file get corrupted,then there are quite lot of DB structure change, so Now how to recover my control file with the upto time DB state , since the backup was taken for control file 6 hour before the file get corrupted, now how do I revery the DB/control file, step by step with syntax as well.
View 1 Replies
View Related
Oct 12, 2012
I know this question must have been asked several time. I just want to know the many ways in which we can have a DB up and running even if we do not have any backup of control file not even the trace of the same.
View 6 Replies
View Related
Feb 14, 2013
I am just looking to my control file that I have. I have managed to successfully get my control file to load data into the required tables but just for my learning I was trying to amend the when clause with an and condition but keep getting an error.
My code:
OPTIONS (DIRECT=TRUE, ERRORS=0, SKIP=1)
load data
infile 'A:/My Files/Feeds/20130211/data_summary_1_20131214.txt' "STR '
'"
APPEND
into table I_DATA_1
WHEN (RID <> 'RID')
fields terminated by "~|~" optionally enclosed by '"'
TRAILING NULLCOLS
(
RID,
S_TIMESTAMP timestamp 'DD-MON-RR HH:MI:SS.FF3 PM'
)
the above code works perfectly but if i change the following line:
WHEN (RID <> 'RID' AND 'S_TIMESTAMP' IS NOT NULL)than this gives me an error.
View 4 Replies
View Related
May 25, 2012
How to understand the two parameters db_file_name_convert and log_file_name_convert,if there are missing in the parameter file of standdy database,how does oracle will do?
View 3 Replies
View Related
Mar 12, 2012
I am receiving errors when trying to load the control file. The errors are as follows:
SQL*Loader-500 Unable to open file (homework.ctl)
SQL*Loader-553 file not found
SQL*Loader-559 SYstem error: The system cannot find the file specified.
My control file is located directly in the C drive (C:\homework.ctl). The control file contains the following
LOAD DATA INFILE 'c:\country.dat'
APPEND INTO TABLE homework
fields terminated by ',' optionally encloded by '"'
(country, month, day)
WHEN (month='April')
On the command line, I am entering:
sqlldr system/password control=homework.ctl
View 1 Replies
View Related
Sep 13, 2010
In a control file, the code is as follows:
load data
infile 'C:\Documents and Settings\xxxxx\Desktop\abc.txt'
APPEND
PRESERVE BLANKS
INTO TABLE table1
[code]...
When I run the above control file in sqlldr, I'm getting the error as
Record 1: Rejected - Error on table table1, column column3.
ORA-01481: invalid number format model
In the table the column3 data type is NUMBER(6,2).: The column size in table is 6 and position of column3 in control file is only 4. Also if possible let me know how the same data (send me 2 dummy records) which exactly works for the above control file especially for column3 where decimal number comes in the flat file.
For generating the flat file, for column3, i'm using LPAD(:value,4,0) in the select query column list.
View 1 Replies
View Related
Apr 1, 2011
How can we add another control file to DBOG database? I need to clarify all the steps during this process.
View 7 Replies
View Related
Apr 8, 2012
By mistakenly Oradata file has been deleted and there is I have not any backup when we Startup the database then we get an Error as
ORA-00205: error in identifying control file, check alert log for more info
Now I want to create controlfile as
Microsoft Windows [Version 6.1.7600]
Copyright © 2009 Microsoft Corporation. All rights reserved.
C:\Users\SARASWATIMAA>SQLPLUS
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 8 23:15:09 2012
Copyright © 1982, 2005, Oracle. All rights reserved.
Enter user-name: SYS AS SYSDBA
Enter password:
[code]...
where is My Mistake in Creating Cotrolfile..I want to know that in which condition we use "SET" or "REUSE" in Create Controlfile.
View 3 Replies
View Related
Sep 11, 2011
I have a problem with my control file [URL].........
What I did, I have a duplicate of the database with the invalid control file, let's call it DUMDB. I am using my control file and no recovery-catalog-db.
I recreated the control file. Now everything is working fine, new backups can be taken, restores can be made. I just need to restore a backup tagged with "LASTDEV". The control file for this backup is the invalid one. So I need a restore WITHOUT restoring the control file.
I did:
CODERMAN> catalog backuppiece '/u03/flash_recovery_area/DUMDB/backupset/o1_mf_nnndf_LASTDEV_75fmkllw_.bkp';
cataloged backup piece
backup piece handle=/u03/flash_recovery_area/DUMDB/backupset/o1_mf_nnndf_LASTDEV_75fmkllw_.bkp RECID=1 STAMP=761604017
CODERMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 2.77G DISK 00:00:00 26-AUG-11
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: LASTDEV
Piece Name: /u03/flash_recovery_area/DUMDB/backupset/o1_mf_nnndf_LASTDEV_75fmkllw_.bkp
List of Datafiles in backup set 1
===========================
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 63166185 26-AUG-11 /u02/oradata/dumdb/system01.dbf
2 Full 63166185 26-AUG-11 /u02/oradata/dumdb/sysaux01.dbf
3 Full 63166185 26-AUG-11 /u02/oradata/dumdb/undotbs01.dbf
5 Full 63166185 26-AUG-11 /u02/oradata/dumdb/stdtbs01.dbf
6 Full 63166185 26-AUG-11 /u02/oradata/dumdb/users.dbf
CODERMAN> crosscheck backupset 1;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u03/flash_recovery_area/DUMDB/backupset/o1_mf_nnndf_LASTDEV_75fmkllw_.bkp RECID=1 STAMP=761603187
Crosschecked 1 objects
CODERMAN> RESTORE DATABASE FROM TAG='LASTDEV';
Starting restore at 11-SEP-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/11/2011 20:32:25
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
This is exactly the same message if you do "RESTORE DATABASE FROM TAG='NONEXISTENTDUMMYTAGXXXX'"
Why can the data files not be found even if they are listed in the backup?
I am on 11.2.0.2.0 on Linux64.
View 1 Replies
View Related
Feb 16, 2012
I am trying to simulate a damage in my control files
$ORACLE_BASE/oradata/orcl/control01.ctl and $ORACLE_BASE/flash_recovery_area/orcl/control02.ctl
So I moved these files to another folder different of origin with the database online (in linux, I tried to do it in win7, but not works)!
And for my surprise the DML and DDL commands still working! The Oracle docs said:
QUOTE If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.
[URL] ....
What " the instance becomes inoperable" in Oracle Docs means?
View 2 Replies
View Related
Mar 2, 2011
We have a database that is accessed by ArcSDE, a product to modify maps. It uses BLOBs to store those maps.
We ran a load on the server and the response time was slow. By running the following query:
select event, total_waits, time_waited, avg_ms, round(ratio_to_report(time_waited) over () * 100) percent
from (select substr(event, 1, 30) event, total_waits, time_waited, round(time_waited_micro / total_waits / 1000, 2) avg_ms
from v$system_event
where wait_class in ('System I/O') union
select 'CPU' event, NULL, value, NULL
from v$sysstat
where statistic# = 12
order by 3 desc)
where rownum <= 10;
I get
EVENT TOTAL_WAITS TIME_WAITED AVG_MS PERCENT
--------------------------- -------------- ------------- -------- ---------
control file parallel write 127187 6354909 499.65 70
CPU 988274 11
db file parallel write 20461 886442 433.23 10
log file parallel write 14987 870672 580.95 10
log archive I/O 1557 18094 116.21 0
control file single write 149 10590 710.71 0
control file sequential read 136502 5219 .38 0
log file single write 56 2511 448.41 0
log file sequential read 489 492 10.05 0
BUG: 733426 says to change the event="10359 trace name context forever, level 1"
View 2 Replies
View Related
Nov 30, 2004
I am cloning a database from a cold backup in archivelog mode using the control file script but failes:
STARTUP NOMOUNT
CREATE CONTROLFILE DATABASE PROD NORESETLOGS ARCHIVELOG
:
:
:
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORADATA\PROD\TEMP01.DBF'
[code]...
Tried again with RESETLOGS:
STARTUP NOMOUNT
CREATE CONTROLFILE DATABASE PROD RESETLOGS ARCHIVELOG
:
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-00344: unable to re-create online log 'D:\ORADATA\PROD\REDO01B.ORA'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
View 3 Replies
View Related
Aug 29, 2010
I want to use rman to restore all my control files, I just did a level 0 incremental backup and ran the following script.
rman <<EOF
connect target sys/sys@GOLD_AUX
connect catalog rman/rman@GOLD_CAT
RUN
{
STARTUP FORCE NOMOUNT;
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN RESETLOGS;
}
EOF
My control files seem to be put back in the correct spot and suggest a way I can restore only my control files and have everything in sync with my datafiles.
end of out from rman script
====================
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/oracle/data/aux/control01.ctl
output filename=/oracle/data/aux/control02.ctl
output filename=/oracle/data/aux/control03.ctl
Finished restore at 29-AUG-10
[code]...
View 6 Replies
View Related
