How To Control File Parallel Write

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


ADVERTISEMENT

Server Utilities :: How To Write Control File To Load Data Into Revenue Table

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

Performance Tuning :: How To Control Concurrent Inserts Of Record By Parallel Sessions

Aug 7, 2012

There is a detail table

There is a summary table

During batch process record is entered in detail table as well as summary table.

The process first checks if record exists in summary table for same group_no and if 'yes' then "updates" the record with the newly added amount (sums it) else inserts a new record
Whereas in the detail table it inserts the record directly

detail table :
group_no doc_no amount
101 doc1 100
101 doc2 200
102 doc3 300
102 doc4 400

summary table :
group_no amount
101 300
102 700

Now if the batch process runs in parallel, (out of many) two different sessions insert same group_no; This is because while sesond session inserts a record, first session inserting the same record (group_no) has not yet committed ; So second session Not knowing that already there is same Group_no (101) inserted, again inserts another record with same group_no rather than summing it.

Can it be solved without using temp table, select for update?

View 4 Replies View Related

Reports & Discoverer :: REP-50127 / Cannot Write To File When Trying To Save A Report To A File

Sep 17, 2010

I am receive the REP-50127 error, cannot write to file when trying to save a report to a file on the network via report parameters.

I am guessing the rwserver does not have permissions to the network drive.

Will the SERVER_IN_PROCESS=NO run the rwserver process as the user executing the report?

View 1 Replies View Related

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 View Related

Performance Tuning :: Parallel Index With Non-partitioned No-parallel Table?

Apr 30, 2012

If we have not set parallel degree for a table then we can ( try to ) force parallel execution on a table using a parallel hint Does this 'parallelism' works on the index search in the query as well?

In which situations non-parallel non-partitioned table but parallel index (degree>2) will facilitate a query?

View 5 Replies View Related

PL/SQL :: Write Clob Or Xml To A File?

Jul 9, 2013

I have read the following article:[URL] 11070I want to know wherer if there exists a possibility of write a clob or an xml into a file on disk, if we do not have the CREATE ANY DIRECTORY privilege. Many functions, like UTL_FILE.FOPEN, or dbms_xslprocessor.clob2file, or dbms_xmldom.writetofile, need an Oracle directory to be created (with CREATE OR REPLACE DIRECTORY...). But if we don't have this privilege, is there a possibility to export a clob into a file as xml (the clob contains 100% xml, but this is the column data type, CLOB) if we don't have that privilege?The clob data contains 48200 characters.

View 8 Replies View Related

Multiple Processes To Write File?

Sep 10, 2012

In PL\SQL program, I am writing information from one table to file. In my current architecture, I am writing the information to approximately 1000 files.

If I put the database write operation in another package and in another method and call this method from my PL\SQL program asychronously, can that increase performance?

View 1 Replies View Related

SQL & PL/SQL :: How To Write Text At Specified Position In File

Apr 11, 2013

suppose i have a file named chk.txt and I have write 10 line in that file and i just want to write some text at line 5 or line first then how we can do this ?

View 3 Replies View Related

Server Utilities :: How To Write CTL File

Jun 10, 2013

How to write the CTL file for this kind of situation.

a.txt
id name subject
12aaaHistory
23bbbScience
45cccZoology

b.txt
idlayerLayerNo
12xxx121
23yyy232
23lll233
45xxx451
45yyy452
45lll453

i have files a.text which is parent file and another one is child one called file b.txt . Both files are linked together by common field called "id". Interesting part child file have multiple layers name associated with ids. (we are only aware that in b.txt for each id there could be max 3 layers)

So they needs to get loaded into Table called PARENT_TBL

So PARENT_TABLE looks like
ID NAME SUBJECT LAYER LAYERNO

How I'm going to achieve this ?

View 3 Replies View Related

PL/SQL :: How To Write To Text File Using Oracle

Feb 23, 2013

How to write to text file using oracle? And how do I handle spaces/next line? (i was trying to use spaces(ch(32)), however it is just converted into squares in the text file.)

View 5 Replies View Related

Can Get Name Of File Loading Table In Control File

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

Forms :: Read And Write File Using Oracle 6i?

Nov 16, 2011

I have a file studnet.txt in following order

JAMs|1231|PHYSICS
SAM|1232|PHYSICS
ALI|1233|CHEMISTRY

I want to read and write data from a file in | seprated mode. file READ and WRITE using Oracle Forms6i. I have a knowledge of file handling in C++ but not use it in Oracel Form before this.

View 5 Replies View Related

PL/SQL :: Read Byte Array And Write It Into File

Sep 19, 2013

I have a web service which gives me the response in Byte array.write this byte array into a binary file? .

View 2 Replies View Related

Connect To Oracle DB And Write SQL Results Into Excel File

Sep 27, 2013

I would like to connect to oracle database and would like to execute series of SQLs and write those SQL results into Excel file (sheet1).

my requirement is simple :
------------------------
db_user_id scott
db_user_pwd tiger
db_sid orcl

select count(*) from emp;
select count(*) from dept;

print the same SQLs in A column and result should be in B column in excel file.

column A column B
select count(*) from emp; 14
select count(*) from dept; 4

thats it.

View 1 Replies View Related

Write Shell Script To Parse Parameter File

Mar 29, 2013

I'm working on trying to write a shell script to parse a parameter file, but at the same time I want to be able to overwrite the parameter file settings with other command line settings. For instance if my par file had export/import settings for the username, password, schema, etc;

I wanted to run the same export/import with those settings for a different schema. I want to be able to put the schema=<different_than_par_file> after the parfile=<parfile.par and have the parfile be read and applied for everything except the different schema.

Right now I'm storing the cmd line and parsing it again looking for other parameters besides the parfile.

View 4 Replies View Related

PL/SQL :: Read  Long Raw data And Write To A File

Sep 17, 2012

So we need an mechanism to read data from LONG RAW and convert into actual file.

View 1 Replies View Related

JDeveloper, Java & XML :: How To Write Results Of Select Statement Into File

Feb 25, 2011

SQL> desc res;
Name Null? Type
----------------------------------------- -------- ----------------------------
RESULT PUBLIC.XMLTYPE

SQL> select * from res;

RESULT
--------------------------------------------------------------------------------
<fine No="2"><stdNo>2</stdNo><value>300</value><reason>breaks keyboard</reason><
date>2011-10-03</date></fine>

how can me write results of select statement into xml file instead of show them on screen?

View 20 Replies View Related

SQL & PL/SQL :: Write A Dynamically Changing Filename - Code Not Producing Any File?

Jul 19, 2011

I tried to write a dynamically changing filename and file type so that i can dump some data. But the code I wrote is not producing any file at all even though it compiles with no errors. I run this code at XE database which comes free from the oracle website. I did all the directory settings. No problem with it because I can produce file with different codes. The code is as following:

CREATE OR REPLACE PROCEDURE dump_csv_file IS

TYPE number_array IS VARRAY(10000) OF NUMBER;
TYPE string_array IS VARRAY(10000) OF VARCHAR2(100);
TYPE date_array IS VARRAY(10000) OF DATE;
TYPE v_file_array IS VARRAY(10000) OF UTL_FILE.FILE_TYPE;

[code]...

View 11 Replies View Related

Recover Control File

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

All Control File Lost?

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

PL/SQL :: Append In Control File

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

Getting Error When Try To Load The Control File

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

DECIMAL EXTERNAL In Control File?

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

How To Add Another Control File To DBOG Database

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

Error In Create Control File

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

Restore Backup Without Control File

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

Simulating Control File Damage

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

Creation Control File Failed?

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

Restoring Control File When Using A Recovery Catalog

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







Copyrights 2005-15 www.BigResource.com, All rights reserved