Home » SQL & PL/SQL » SQL & PL/SQL » Backup (Oracle 11.1 , linux)
Backup [message #676428] Sun, 09 June 2019 06:02 Go to next message
Manishkumar
Messages: 36
Registered: May 2019
Member
Hi,

Is there any way to export database in form of insert into statememts using sqlplus console

Thanks,
Manish
Re: Backup [message #676430 is a reply to message #676428] Sun, 09 June 2019 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 66653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Build the query to display what you want, columns or insert statements.

Re: Backup [message #676431 is a reply to message #676430] Sun, 09 June 2019 06:15 Go to previous messageGo to next message
Manishkumar
Messages: 36
Registered: May 2019
Member
i want insert statements. How could that be done?
Re: Backup [message #676433 is a reply to message #676431] Sun, 09 June 2019 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 66653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Select 'INSERT '||... from mytable;

Re: Backup [message #676434 is a reply to message #676433] Sun, 09 June 2019 06:23 Go to previous messageGo to next message
Manishkumar
Messages: 36
Registered: May 2019
Member
will it give entire table data in form of insert statements.
Re: Backup [message #676435 is a reply to message #676434] Sun, 09 June 2019 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 66653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes.
Re: Backup [message #676437 is a reply to message #676435] Sun, 09 June 2019 09:00 Go to previous messageGo to next message
Manishkumar
Messages: 36
Registered: May 2019
Member
for example if student is a table then select 'insert'|| from student will gives all insert statement of student table
Re: Backup [message #676439 is a reply to message #676437] Sun, 09 June 2019 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 66653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The ellipsis (...) in my sentence means you have to concatenate all the columns you want in the order you want in the format you want for the INSERT statement.

Re: Backup [message #676441 is a reply to message #676439] Sun, 09 June 2019 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 66653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is Sunday, I will be kind.
Here's for the DUAL table:
SQL> select 'INSERT INTO DUAL (dummy) VALUES ('''||dummy||''');' statement from dual;
STATEMENT
--------------------------------------
INSERT INTO DUAL (dummy) VALUES ('X');
Re: Backup [message #676442 is a reply to message #676437] Sun, 09 June 2019 11:45 Go to previous messageGo to next message
John Watson
Messages: 8023
Registered: January 2010
Location: Global Village
Senior Member
Ypu can try to automate it with this sort of thing, run it as SCOTT:
SELECT 'insert into '
              || table_name
              || ' ('
              ||
       (
              SELECT rtrim(extract(xmlagg(XMLELEMENT(e, t.column_value.getrootelement()
                            || ',')),'//text()'),',')
              FROM   TABLE(xmlsequence(t.column_value.extract('ROW/*'))) t)
              || ') values ('
              ||
       (
              SELECT dbms_xmlgen.convert(rtrim(extract(xmlagg(XMLELEMENT(e, ''''
                            || t.column_value.extract('//text()')
                            || ''',')),'//text()'),','),1)
              FROM   TABLE(xmlsequence(t.column_value.extract('ROW/*')))t)
              || ');' ins_stmt
FROM   user_tables,
       TABLE(xmlsequence(dbms_xmlgen.getxmltype('select * from '
              || table_name).extract('ROWSET/ROW'))) t
WHERE  table_name = 'EMP';
I didn't write it, and it is pretty buggy, but you can work on that.
Re: Backup [message #676446 is a reply to message #676434] Sun, 09 June 2019 12:44 Go to previous messageGo to next message
EdStevens
Messages: 1126
Registered: September 2013
Senior Member
Manishkumar wrote on Sun, 09 June 2019 06:23
will it give entire table data in form of insert statements.
What happened when you tried it?

And why do you not simply use expdp? What you are asking about is a preconceived technique to accomplish some unknown (to us) business objective. If you would describe that business objective, I'll bet we can offer a more efficient technique than the one you imagine.
Re: Backup [message #676447 is a reply to message #676446] Sun, 09 June 2019 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 66653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
And why do you not simply use expdp?
Maybe this sentence in his previous (incomplete) topic will help you to understand:


Quote:
when i am trying to run dump with content=data_only it is not replicating.

Although he did not explain what he means by "it is not replicating".
And although the beginning of the sentence is:

Quote:
But after deletion of content in table when i am trying...

which, for me, explain why there is nothing in the dump (if "it is not replicating" means that).

Re: Backup [message #676449 is a reply to message #676447] Sun, 09 June 2019 19:40 Go to previous messageGo to next message
Manishkumar
Messages: 36
Registered: May 2019
Member
I have taken dump before deletion. Delete statements are perfectly replicating but when i am running dump with content=data_only it is coming on only one db, data not replicating to others.
Re: Backup [message #676457 is a reply to message #676449] Mon, 10 June 2019 03:49 Go to previous messageGo to next message
cookiemonster
Messages: 13689
Registered: September 2008
Location: Rainy Manchester
Senior Member
So what does the log file on the other db say?
Re: Backup [message #676462 is a reply to message #676447] Mon, 10 June 2019 09:26 Go to previous messageGo to next message
EdStevens
Messages: 1126
Registered: September 2013
Senior Member
Michel Cadot wrote on Sun, 09 June 2019 13:03

Quote:
And why do you not simply use expdp?
Maybe this sentence in his previous (incomplete) topic will help you to understand:

Ah, I had not picked up on this and his previous topics. After reviewing them, I think I'll just bow out.
Re: Backup [message #676463 is a reply to message #676457] Mon, 10 June 2019 09:55 Go to previous messageGo to next message
Manishkumar
Messages: 36
Registered: May 2019
Member
dump data is not reflecting. Other db not getting any apply error. Which log file needs to check. where is that present?
Re: Backup [message #676464 is a reply to message #676441] Mon, 10 June 2019 09:56 Go to previous messageGo to next message
Manishkumar
Messages: 36
Registered: May 2019
Member
Thanks Michel, I am able to get insert statements.

[Updated on: Mon, 10 June 2019 09:56]

Report message to a moderator

Re: Backup [message #676465 is a reply to message #676463] Mon, 10 June 2019 09:59 Go to previous messageGo to next message
cookiemonster
Messages: 13689
Registered: September 2008
Location: Rainy Manchester
Senior Member
Manishkumar wrote on Mon, 10 June 2019 15:55
dump data is not reflecting. Other db not getting any apply error. Which log file needs to check. where is that present?
I assumed you were using datapump, but given your other topic, probably not.
I've no idea what logging replication does.
Re: Backup [message #676466 is a reply to message #676465] Mon, 10 June 2019 10:27 Go to previous messageGo to next message
Manishkumar
Messages: 36
Registered: May 2019
Member
yes i am talking about datapump. For details you can see my last post yesteray in replication.
Re: Backup [message #676468 is a reply to message #676466] Tue, 11 June 2019 03:08 Go to previous messageGo to next message
cookiemonster
Messages: 13689
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well datapump has a log file.
You can specify what it's called and what folder it goes in when you run datapump.
So look at the datapump documentation, write a log file, read the log file.
Re: Backup [message #676475 is a reply to message #676468] Tue, 11 June 2019 12:35 Go to previous message
Bill B
Messages: 1899
Registered: December 2004
Senior Member
If you are inserting into different schemas or tablespaces on the other database you need to tell datapump how to perform the mapping. read up on the command line options for datapump import on oracle 11
Previous Topic: Aggregate operation on array
Next Topic: How to build a query that gets its values from a file
Goto Forum:
  


Current Time: Tue Oct 22 12:04:51 CDT 2019