How To Export Database Out Of Select Result
Nov 18, 2008I got this select that gets me a couple thousand rows, or over a million, and i can't get how to export it into a new database.
View 2 Replies
ADVERTISEMENT
SQL & PL/SQL :: Obtain Result Set When Doing Select
May 13, 2010when in sqlplus I execute:
select * from mytable;
in sqlplus I get the returned rows of this table. But this is not the case when I do in plsql the following:
create or replace procedure myproc is
a varchar2(50);
begin
a := 'select * from mytable';
execute immediate a;
end;
there is nothing I can see. Is there a way to catch this result set and see it on the screen? Something that is not creating new structures like cursors, etc?
PL/SQL :: SELECT - How To Wait Until Result Appears
Mar 28, 2013SELECT * from TABLE_1 where a=b;
Is it possible to make the SELECT (PL/SQL is OK ) wait/block in case there are no rows found until another session inserts rows that match the condition?
Something like this
cursor c is select * from TABLE_1 where a=b order by column_a for update skip locked;
row c%rowtype;
begin
open c;
[Code].....
Or the INSERTing session should send some kind of notification (semaphore?) to the reading session?
SQL & PL/SQL :: Check Datatype Of Result From Select Query?
Jul 7, 2011I have this query
SELECT wmsys.wm_concat (gp.prog_acronym)
FROM inf_program gp, ea_audit_program ap
WHERE ap.sys_prog_id = gp.sys_prog_id
AND ap.sys_audit_id =484
is there any way I can check the datatype of the result of the above query ? ,my dba added some patch to oracle , after the patch this query is returning a clob in java , it should return string and it used to return string before patch and in other databases it returns string, I can check the return type only from java side , is there any way oracle can say me the datatype ?
PL/SQL :: Unexpected Result With Select Max (column) In Anonymous Block
Nov 27, 2012The following query gives me 28,800 as sum(sal)
SELECT SUM(salary)
FROM employees
WHERE department_id =60
O/P is : 28800But when i use the above query in anonymous block it gives me 684400
DECLARE
v_sum_sal NUMBER;
[code]....
The above output statements gives me 684400 as output.. But the expected is 28800
SQL & PL/SQL :: Different Number Of Result Sets While Adding Further Columns In Select Clause
May 4, 2010The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.
Before adding the columns 4,5,6,the result set count was 11.
SELECT PAYMENT_METHOD_MAP.NETTINGGROUP_ID,
PAYMENT_METHOD_MAP.CREDITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.DEBITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.AGENT_ID,
SETTLEMENT.NETTINGGROUP_ID,
SETTLEMENT.SETTLEMENTDATE
[code]....
Client Tools :: Different Charactersets Show Line-break In Select Result
Mar 22, 2010i have a little curiosity in result of a select statement.
One database has following characterset settings:
NLS_CHARACTERSET WE8ISO8859P15
NLS_NCHAR_CHARACTERSET AL16UTF16
and the following select statement results in multiple lines:
SQL> select INSTANCE_NAME,STATUS from v$instance;
INSTANCE_NAME
------------------------------------------------
STATUS
------------------------------------
BUP
OPEN
Another database has following characterset settings:
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
and the same select shows only a 2 line result:
SQL> select INSTANCE_NAME,STATUS from v$instance;
INSTANCE_NAME STATUS
---------------- ------------------------------------
HPSMP OPEN
Both databases are version 10.2.0.4 and both are running on HPUX 11.31.Why do we get this differents in showing the result?
Our problem is that the package-start scripts from HP, to start a database, check the result of the select statement above and if we have the multiline result the check gives an error and stops the package again.
Export/Import/SQL Loader :: Export Whole Database Metadata Only?
May 9, 2013I would like to export an entire DB metadata . I want to exclude data.is it possible.We have 100+users.We get request to restore package from their schema very often.So I am thinking of creating job to emport an entire DB metadata .
View 7 Replies View RelatedStoring Select Query Result Into Array And Using It In Another Query?
Aug 7, 2009I am looking to simplify the below query,
DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)
Since both the inner queries are same,I want to extract out to a local variable and then use it.
Say,
Array var = SELECT ID FROM B WHERE BID=0;
And then ,
DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)
How to do this using SQLPLUS?
SQL & PL/SQL :: Return A Result Set From One Database To Another?
Sep 4, 2012Is there any way to return a result set (using a Database Link) from one Database to another Database? Database A needs to call Database B and Database B needs to return a result set via a stored package/function. The stored procedure/function on Database B also accepts 1 input parameter. Something like "select test.test('some input parameter')@database_b from dual'.
View 39 Replies View RelatedSQL & PL/SQL :: Export Large Number Of Records From Select Into Text File
Feb 9, 2011I need to export large number of records from select into the text file. It's about 2milion records.I can do it by PLSQL (see below) where executing of process takes time too much. How to export to text file faster?
DECLARE
fileid UTL_FILE.file_type;
BEGIN
fileid := UTL_FILE.fopen ('VRS_CEM', 'cust.txt', 'W');
UTL_FILE.put ( fileid, 'IMSI|MSISDN|CONTRACT');
UTL_FILE.NEW_LINE(fileid);
[code]....
Export/Import/SQL Loader :: How To Skip Database JOBS During Export And Import
Aug 7, 20121) Is there a way to skip database jobs while exporting (EXPDP) ?
2) Is there a way to skip database jobs while importing (IMPDP) ?
PL/SQL :: Incorrect Query Result Over Database Link
Jul 2, 2012My problem is I have 3 tables (TEST_TBL1, TEST_TBL2, TEST_TBL3). TEST_TBL2 and TEST_TBL3 are in remote database and I use database link to join them. The following query returns incorrect result (I seems that it ignore the where clause)
SELECT * FROM TEST_TBL1 JOIN TEST_TBL2@db_remote USING (KEY1) JOIN TEST_TBL3@db_remote USING (KEY2) WHERE KEY1=XXX OR KEY2=YYY;
I am on 11R1 (11.1.0.7)
FOR EXAMPLE:
Local database:
CREATE TABLE TEST_TBL1
(
KEY1 NUMBER(5) NOT NULL,
[Code].....
Data Guard :: Unable To Receive Result From A Database
Mar 21, 2013standby db /primary db in sync managed recovery working but
broker
show configuration
Connected.
DGMGRL> show configuration
Configuration - GenesisDR
Protection Mode: MaxAvailability
Databases:
orcl - Primary database
Warning: ORA-16629: database reports a different protection level from the protection mode
standby - Physical standby database
Error: ORA-16664: unable to receive the result from a database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL>
Performance Tuning :: Export SQL Plan From Test Database To Prod Database?
Jul 16, 2013An SQL query is taking a lot of time than usual and not completing even left after hours! The query joins a table with a quite complex view.
The same query in a test database completes in less than 2 mins.
I would like to export the sql plan from test database to prod database.
how to export/import in 10.2.0.4 version for a particular sql statement's execution plan.
Export/Import/SQL Loader :: Refresh A PROD Database Into TEST Database
Jun 17, 2012I need to refresh a PROD database into TEST database. The PROD and TEST runs on 10g. I need a full refresh. Is there any pre req's which i should keep in mind ?.
View 1 Replies View RelatedData Guard :: ORA-16664 / Unable To Receive Result From Database
Oct 17, 2013I decided to configure our current standby databases for administration in EM 12c via the "Manage an existing standby database with Data Guard broker". The first one went off without issue but on the second I got "ORA-16664: unable to receive the result from a database". I've scoured through everything and confirmed, more than once, that the tnsnames, listener and pwfile are all good to go. I can tnsping everything from both servers. The sby1 database is functioning fine and all log files are being transported and applied without issue from primary to standby, on different servers. I can connect via sqlplus to each database from opposite servers. I don't understand how I can do the below in DGMGRL:The listener doesn't not know of service requested (PRI1) and is unreachable in the first connect, but then I can turn around and successfully connect to PRI1 without issue?
DGMGRL> connect sys@sby1Password:Connected.DGMGRL> show configuration; Configuration - PRI1_ora-9002.xxxx Protection Mode: MaxPerformance Databases: PRI1 - Primary database sby1 - Physical standby database Fast-Start Failover: DISABLED Configuration Status:ORA-12514: TNS:listener does not currently know of service requested in connect descriptorORA-16625: cannot reach database "PRI1"DGM-17017: unable to determine configuration status DGMGRL> connect sys@pri1Password:Connected.DGMGRL> show configuration Configuration - PRI1_ora-9002.xxxx Protection Mode: MaxPerformance Databases: PRI1 - Primary database sby1 - Physical standby database Error: ORA-16664: unable to receive the result from a database Fast-Start Failover: DISABLED Configuration Status:ERROR DGMGRL> ;
How To Export 9i Database
Apr 7, 2010I've been tasked with exporting an oracle 9i database. The problem is that my experience with oracle is extremely limited. What I need to do is export the schema and data for each table into text files that I can use to rebuild the database in MySQL. From what I understand, the database contains around 8 tables. I will have to go to the client's office to perform the export so I would like to find out as much information as possible before I get there so I won't have to waste time while there.
My research on the topic shows that there is an exp utility that will export information into a binary file. If I download the 10g express edition, can I import the 9i dmp file into 10g? This would at least allow me to have access to the database to futz with it without having to be at their office. Is there a better way to go about doing this?
Is there a way to export all of the tables into a text format similar to what mysqldump will do?
Import / Export Schema In The Database
Mar 11, 2012 i want to export a particular schema from a database, and than import it in the same database but saving it as a different schema name.
and i need to use imp export utility for it.
one schema with the data... other one with just the meta data.
Import / Export Database With Options?
Jun 20, 2011steps to export/import a database with options with exp/imp command.i want to acess the exported database table with my current user schema, means actually i had exported a database earlier but i'm not able to access tables directly rather i've to use exported database schema like abc.tablename.
View 1 Replies View RelatedPL/SQL :: How To Import And Export Database In Oracle
Jun 27, 2013How to import and export database in oracle? what is difference between backup and restore?
View 0 Replies View RelatedEnterprise Manager Console Database Export?
Jul 6, 2010I have oracle9i installed on my system.. i have created a database using enterprise manger console..
Now i want to export this database for the purpose of backup.. so that i can import it later.
Error 1455 / Export Full DATABASE
Dec 13, 2012I am trying to export a full DATABASE using the command...EXP [username/password]@[CS]FILE=PATH\[filename.dmp] LOG=PATH\[logname.log] INDEXES=n STATISTICS=none COMPRESS=Y
the database begins to export as shown below, but the export terminates with the below error.
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user [username]
. exporting PUBLIC type synonyms
. exporting private type synonyms
[code],,,,
considering that i have exported full databases successfully before using the mentioned command above.
Enterprise Manager :: Scheduling Export Database
Jul 25, 2012I would like to export my whole database every evening. How can I do this using enterprise manager.
View 4 Replies View RelatedPerformance Tuning :: Import And Export Of Database
Oct 14, 2010I am doing import and export of database.Before loading data i drop all the tables and import.Is there any issue if we do drop tables and import data frequently.
View 2 Replies View RelatedSecurity :: Export Policies And Import To Another Database?
Aug 24, 2012How can I export FGA / row level security policies from one database to another? I have created a new version of my schools ERP database, with upgraded application software, and now need to get the policies from our current production system to the new one.
View 5 Replies View RelatedExport/Import/SQL Loader :: Refresh Database Through Exp / Imp?
Oct 3, 2012version 10203 on windows (old db on solaris 8170)
i Have to refresh database data, No of users/schemas are 400+. fastest way to do that would be to do full exp/imp.but 1st drop current users cascade. (any command to drop all users in with one command?)then validate all all tables/schemas are same & up-to-date. i am thinking to check full exp logs on old & new db.(what that can take forever manually going through thousands of tables etc)
Export/Import/SQL Loader :: Migrate Database Into Different SID
Jul 19, 2012I need to migrate a 10g database into an 11gR2 on same Red Hat Linux platform (although different servers with different versions of Linux). The difference between the two databases however is the SID, the new one has a different SID which means that the datafiles will be named differently (as our datafile names include the SID) otherwise everything else is the same..
I propose to take the following steps:
- install 11gR2 on the new server
- create the 11gR2 database with new SID using DBCA
- full export 10g database
- full import dump file into 11gR2 database.
I however do not have experience of how this will work with respect to the full import where the datafiles are named differently. For example TABLESPACE TEST in the source database has datafile TEST_SOURCE.dbf but the same TABLESPACE TEST in the target database will have datafile TEST_TARGET.dbf.
Will all the data in the source database be correctly imported into the new database?
Any Way To Include Sys.aud$ Table In Full Database Export
Jan 2, 2013I am doing an export using the following parfile information:
userid=/
directory=datapump_nightly_export
dumpfile=test_expdp.dmp
logfile=test_expdp.log
full=y
content=all
However when I run this I do not see the sys.aud$ in the log file. I know I can do a seperate export to specifically get the sys.aud$ table but is there any way to include it in with my full export?
Error While Datapump Export Backup Of Full Database
Sep 5, 2012One of my Friend gets error While datapump Export backup of Full database.
Pfa below error details:-
ORA-31693: Table data object "RADIOMIRCHI_PIP_HRMS"."GM_DEPT" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-00604: error occurred at recursive SQL level 3
ORA-21780: Maximum number of object durations exceeded.
[code]......