PL/SQL :: Transferring Data From Oracle Table To A File On A Windows Server

Nov 12, 2013

I have an oracle DB version 11.2 running on oracle enterprise linux 5.9. How to transfer data from the oracle DB to a flat file on a windows server. What i have done so far is to use utl_file to create a csv file on the oracle server and am now attempting to transfer this file.

I was going to use scp or rcp but am unable to get this to work(was looking at filezilla). Another option i can use is ftp as i have a UNIX script which i can run to do this. All this is done through an oracle package which is run hourly through dbms_scheduler. I have been using sp_host_command to run unix commands directly from pl/sql so can use this to run a unix script for last resort if i cant find an easier way to automate this. 

View 1 Replies


Server Administration :: Transferring Database From 11.1.0 Windows 32 Bit To 11.2.0 64 Bit

Mar 30, 2011

I wanted to transfer my database from Oracle 11.1.0. on windows 2003 server 32 bit to Oracle 11.2.0. windows server 2008 64 bit

i have done following

1)On new server of 11.2.0 windows 2008 64 bit use rman backup from Oracle 11.1.0. on windows 2003 server 32 bit to restore the database and recover the database when i try to open the database Alter database open it gave me message that i should start my database as upgrade I start the database with upgrade

STARTUP UPGRADE and then run following


and then run following


and then run


during run of catupgrd.sql after around 30 minutes of run, i got error
ORA-03113: end-of-file on communication channel

but i reconnected myself with database again and shutdown the database and open it in normal mode and it has opened there were thousands of uncompiled objects and i recompiled them.

Only uncompiled objects left 60 are 'OLAPSYS' (Locked user) USER and 30 of PUBLIC user (all Synonyms)

Have i taken correct steps?

What should i do now to check that everything will be alright in future

View 6 Replies View Related

Server Utilities :: Transferring Changed Data From Database A To B By Data Pump?

Apr 1, 2011

I have database A (Working in Live environment) and Database B copy of Database (Not live) I have Restored whole database (A) RMAN backup file on Database (B) Previous week now i don't want to change anything in any schema and want to import only updated and new records in the table in Database B

There are around 20 schema If for example i have everything in new database B all required database objects like Procedure,functions, packages with indexes in all tables and data in tables, i just want to add new data and updated data.

IF i do following in source database

expdp directory=dpump_dir dumpfile=table_data.dmp content=data_only schemas=ACCMAIN,HRMAIN,..... include=TABLE

AND Import in destination database B, will it add new data and update existing one in table and not touch the table structure and indexes.

View 5 Replies View Related

RAC & Failsafe :: ORACLE Server Failover Parameters In Windows Server Machine.config File

Jan 17, 2012

working on setting up connection between a Windows 2008 server and a pair of Oracle 11g DBs in a RAC Cluster. One Database (let's say DatabaseA) is in one data center, and the other (DatabaseB) is an a secondary, backup database. The RAC Cluster is all set up, working fine, etc. However, I Need to set up the machine.config file on my Windows Server, to go only connect to DatabaseA, unless it fails, in which case, we want it to connect to DatabaseB. Think we could do this if the host app server was Linux/Unix, but it is windows, and I just don't have the background as to the parameters to set up in the machine.config file. They are similar, but different, and we want a very specific behavior (use DatabaseA, unless fails, then DatabaseB). Application is .NET 4.0 app.

View 6 Replies View Related

SQL & PL/SQL :: Transferring Data From One To Another Database

Dec 15, 2012

In production database 30 tables are there in different schemas.

In some tables data is existed for 5 years
in some tables data is existed 8 years
in some tables data is existed for 6 years.

And all these tables are having billions and millions of records.Let us assume the tables in the production database are

Table1 -- 8 years data -- No. Of Records 3538969000
Table2 -- 6 years data -- No. Of Records 592844435
Table3 -- 3 years data -- No. Of Records 33224993
Table4 -- 4 years data -- No. Of Records 52361756
Table5 -- 5 years data -- No. Of Records 8948567
Table15 -- 6 years data -- No. Of Records 308476987

Now I want to trnasfer these 15 tables data to test database.Based on the following conditions.

For Table1 I want to transfer 6 years data to test database, keeep 2 years data in production and delete all 6 years data from production databse.

For Table2 I want to transfer 4 years data to test database, keeep 2 years data in production and delete all 4 years data from production databse.

For Table3 I want to transfer 2 years data to test database keeep 1 years data in production and delete all 2 years data from production databse.

This will be done periodically.

I.e. suppose if we run the script now and transfer the requested data to test database,again after one year the data got increased in production then again we have to run the script and transfer data to test database based on some conditions,so it should work for long time. what is the good and fast process to transfer the data.

View 31 Replies View Related

Precompilers, OCI & OCCI :: Transferring Data Between Two Databases

Apr 18, 2009

I am current using Pro*C to upload data from a flat file to database.

Now I have to transfer information(data) between databases. As a rookie, I do not know an efficient of way doing it.

View 4 Replies View Related

Server Administration :: Data File / Table Relation

Feb 25, 2011

I have 2 schemas in my database with over 500 tables in each.i am trying to know which tables actually belong to which datafile in which tablespace.

View 2 Replies View Related

SQL & PL/SQL :: Ftp File To Remote Server By Reading Data From Table?

May 1, 2012

I need a way to ftp file to remote server by reading data from table. I searched a couple of sites which asked me to use Chris xutl_ftp package..but unfortunately the site is no accessible..

Here is the code

* LICENSE: GNU Lesser General Public License (LGPL)
* Copyright (C) 2003-2006 Russ Johnson (


View 3 Replies View Related

Server Administration :: Location Of Table In Tablespace / Data File

Jul 6, 2010

I was wondering if there is any way to know in which Tablespace and Datafile my Table is located. I have exported a table and about to delete it as i am partitioning it.

View 9 Replies View Related

Server Utilities :: Truncate Table And Load It With Data Present In File

Jul 17, 2010

My requirement is to to truncate the table and load it with the data present in file. In the control file, I used the "TRUNCATE" command as well.In case, if the file has some invalid data and sqlldr fails, my existing data will be lost. Is there any option in which the sqlldr does not TRUNCATE the table in case of a failure.

View 6 Replies View Related

PL/SQL :: How To Read CSV File And Insert Data Into Oracle Table

Feb 6, 2013

I have a Clob file as a in parameter in my PROC. . File is comma separated.need procedure that would parse this CLOB variable and populate in oracle table .

View 6 Replies View Related

Server Administration :: Validate OLAPSYS Schema Objects After Transferring Database

Apr 21, 2011

We transferred our Oracle database from windows 2003 enterprise edition 32 bit to windows 2008 enterprise edition server 64 bit.Database is working fine but we have 53 uncompiled objects which are related to OLAPSYS and public as follows


Our business objec is working fine and all other schema does not have any uncompiled objects.How can we validate the OLAPSYS and PUBLIC schema.

View 11 Replies View Related

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

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

SQL & PL/SQL :: Procedure To Load Data In Oracle Table Into Excel File

Dec 29, 2010

send me the procedure for loading the data in an oracle table into an excel file.

View 5 Replies View Related

Server Utilities :: Unload Data From Oracle To Text File?

Jun 14, 2012

We have around 30 million rows in a table which we are extracting through UTIL_FILE and sending this file to somewhere to other systems.

But this extraction taking too much time.

Is there any way out to optimize this process or any new stuff.

View 3 Replies View Related

Server Utilities :: Sql Loader - Load Whole File In Oracle Table?

Oct 26, 2012

I am having query regarding sql loader. my data file is comm(,) seperated and I want to load the whole file in oracle table 'bill_temp' except 1st column data of data file.

File name: bill_file.dat
fields seperated by comma ','
values are like
oracle table bill_temp having the below column:

Here I want load the emp_name,emp_sal and join_date into oracle table bill_temp.
emp_id should not get loaded into table.

Is there any way to skip the loading of particular column data from data file into table?

View 12 Replies View Related

Server Utilities :: How To Load Data In Oracle From Microsoft Access Database (MDB) File

Aug 24, 2012

I have one .mdb (Microsoft Access Database) file and it has some tables in it. I had load it once using toad. But now i have to load it frequently into the database. Is it possible using external table, so i can access that tables using "select" statement.

View 6 Replies View Related

Server Utilities :: Import Constraints Only From Dump File Using Oracle Data Pump?

Nov 16, 2011

How to import constraints only from a dump file using Oracle data pump.

View 1 Replies View Related

Server Utilities :: Import A Flat File Into Oracle And Update Another Table

Jul 5, 2013

I have a text file called ReturnedFile.txt. This is a comma separated text file that contains records for two fields.... Envelope and Date Returned.

At the same time, I have a table in Oracle called Manifest. This table contains the following fields:



I need to write something that imports the ReturnedFile.txt into a temporary Oracle table named UploadTemp, and then compares the data in the Envelope field from UploadTemp with the Envelope field in Manifest. If it's a match, then the DateReturned field in Manifest needs updated with the DateReturned field in UploadTemp.

I've done this with SQL Server no problem, but I've been trying for two days to make this work with Oracle and I can't figure it out. I've been trying to use SQL*Loader, but I can't even get it to run properly on my machine.

I did create a Control file, saved as RetFile.ctl. Below is the contents of the CTL file:

INFILE 'C:OracleTestReturnedFile.txt'


If I could get SQL*Loader running, below is the code I came up with to import the text file and then to do the compare to the Manifest table and update as appropriate:

sqlldr UserJoe/Password123 CONTROL=C:OracleTestRetFile.ctl LOG=RetFile.log BAD=RetFile.bad

update Manifest m set m.DateReturned =
(select t.DateReturned
from UploadTemp t
where m.Envelope = t.Envelope

That's all I got. As I said, I can't find a way to test it and I have no idea if it's even close.

View 2 Replies View Related

Server Utilities :: Import A Dump File Using Impdp Data Pump Utility On Oracle 10g

Feb 19, 2012

Is it possible to import a dump file using impdp data pump utility on oracle 10g where the export dump was taken using traditional exp utility and vice versa.

View 1 Replies View Related

Unable To Deleted Archive Log File On Windows Server?

Sep 20, 2010

OS: windows server 2003

I am not able to delete one month old archive log file manually on windows which doesn't having info about the standby on v$archived_log view of primary database. the sequence were already applied to the standby database. It shows the status as deleted in v$archived_log. while deleting the file manually. it showing an error like another program or person is using it.

View 10 Replies View Related

SQL & PL/SQL :: Copy CSV File From Windows Server Shared Path

Jul 12, 2012

I need to copy .CSV File from a Windows Server shared path (\hostnameoutput) to another server which i believe is on unix.The other server name is On this server i need to put it in the root directory. I will have to use SFTP and not FTP.

View 19 Replies View Related

Server Utilities :: How To Load Date And Time From Text File To Oracle Table Through Sql Loader

May 31, 2010

what i miss to load date and time from text file to oracle table through sqlloader

this is my data in this path (c:externalmy_data.txt)


my table in database emp2

create table emp2 (empno number,
ename varchar2(20),
hiredate date,
etime date,
ejob varchar2(20),
deptno number);

the control file code in this path (c:externalctrl.ctl)

load data
infile 'C:externalmy_data.txt'
into table emp2
fields terminated by ','
(empno, ename, hiredate, etime, ejob, deptno)

this is the error :

C:>sqlldr scott/tiger control=C:externalctrl.ctl

SQL*Loader: Release - Production on Mon May 31 09:45:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 5

View 21 Replies View Related

Server Utilities :: Dynamic Control File Generation In WINDOWS Environment?

Jul 23, 2010

I am trying to generate dynamic control file, as the files I want to upload are coming from different source and their name is constantly changing but following a fix pattern and naming convention.

I am able to generate dynamic control file through SQL. But while calling from BATCH file, i am unable to sent the file name as parameter.

All the examples i have searched are for UNIX, how to do it with BATCH File in WINDOWS.

View 4 Replies View Related

Windows :: Copy A File To DB Server From A Remote Machine Using Plsql / Java

Oct 2, 2013

We want to copy a file from a remote machine to the DB server. copy \<IP_ADRESS>SHARED_FOLDERsome_file.sql d:copied_file.sql

I've used Java and a wrapped it into a PL/SQL procedure so I can do :

exec run_os_command(p_command => 'copy \<IP_ADRESS>SHARED_FOLDERsome_file.sql d:copied_file.sql');

The file was not copied.

I have mapped \<IP_ADRESS>SHARED_FOLDER to x: for example and issued : exec run_os_command(p_command => 'copy x:some_file.sql d:copied_file.sql');

The file was not copied.

But when I issued from the OS prompt copy x:some_file.sql d:copied_file.sql then the file is copied.

What can I add to the PL/SQL procedure so it wil work ?

View 19 Replies View Related

Windows :: Oracle 11g R2 Install On 7 64 Bit Setup EXE File Does Not Work

Jul 5, 2010

I am trying to install Oracle 11g R2 on my Windows 7 Ultimate 64 bit. When I execute setup.exe as Administrator the command window flashes and exits. I could not even see what the message is. Also tried command prompt.

View 12 Replies View Related

Server Administration :: ORA-01111 / Name For Data File 636 Is Unknown - Rename To Correct File

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

Forms :: Oracle 11g Windows 64bit / File Extraction Password

Nov 27, 2012

I downloaded Oracle Fusion Middleware11g Forms and Reports (ofm_frmrpts_win_11. When I begin to extract I am getting bogged down with a password message and that is:

File Windows-amd64 is password protected. please enter the password in the box below.

BTW- I downloaded Windows64 based forms and reports on my windows xp pro machine. I am trying to unzip them and burn it on a DVD so that I can install them on my new dell windows 7 professional laptop.

View 7 Replies View Related

Windows :: Installation Of Oracle 11gR2 - Error In Trace File

Apr 8, 2013

I am trying to install Oracle 11g R2(64bit) on Windows 7 64 bit OS. While Creating Database using DBCA. I am getting error. Below is the screenshot.

Below is the error in the Trace file...

Error in Process: C:Oracleapporacleproduct11.2.0serverinorapwd.exe
Unable to find error file %ORACLE_HOME%RDBMSopw<lang>.msb

View 3 Replies View Related

Installation :: Can't Record Response File For Oracle Client Windows 32bit

Jan 14, 2013

We are going to upgrade our Oracle client on our clients to But I can not record a resopnsfile with setup.exe. Have Oracle removed this feature?

Here's a dump from setup.exe

There is no possibility to run setup.exe witch -record -destinationfile option:

Preparing to launch Oracle Universal Installer from C:UsersTestAppDataLocal
TempOraInstall2013-01-14_02-12-48PM. Please wait ... Usage:
setup [-options] [(<CommandLineVariable=Value>)*]

Where options include:

-clusterware,<crs version>

Version of Cluster ready services installed.

-crsLocation <Path>

Used only for cluster installs, specifies the path to the crs home locat ion. Specifying this overrides CRS information obtained from central inventory.

-invPtrLoc <full path of oraInst.loc>

Unix only. To point to a different inventory location. The orainst.loc f
ile contains:
inventory_loc=<location of central inventory>

-jreLoc <location>

Path where Java Runtime Environment is installed. OUI cannot be run with out it.

-logLevel <level>

To filter log messages that have a lesser priority level than <level>. V alid options are: severe, warning, info, config, fine, finer, finest, basic, gen eral, detailed, trace. The use of basic, general, detailed, trace is deprecated.

-paramFile <location of file>

Specify location of oraparam.ini file to be used by OUI.

-responseFile <Path>

Specifies the response file and path to use.


For attaching homes to the OUI inventory.


Indicates that the Oracle home specified is on cluster file system (shared). This is mandatory when '-local' is specified so that Oracle Universal Installer can register the home appropriately into the inventory.


Session/Installer variables are specified using:

Ex 1: session:ORACLE_HOME_NAME="OraHome"
The lookup order is session:varName then just varName. The session prefix is
used to avoid ambiguity.

Component variables are specified using:

Ex 1: oracle.comp1:1.0.1:varName="VarValue"
Ex 2: oracle.comp1:varName="VarValue"
The lookup order is compInternalName:Version:varName, then compInternalName:
varName, then just varName.

View 2 Replies View Related

Copyrights 2005-15, All rights reserved