OraFAQ Forum: Backup & Recovery » How to can back up some table to dump file

Home » RDBMS Server » Backup & Recovery » How to can back up some table to dump file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to can back up some table to dump file [message #181438]

Mon, 10 July 2006 02:38

nghiant
Messages: 38
Registered: July 2006
Location: Viet Nam

Member

In mysql, I have a tool for back up some table to dump file. It is insert statement.
In oracle have any statement to execute this function. I want backup data in all table begin start with "M" character to dump file.
Similar, can export data in all table "M" to csv file ? Laughing

Best Regard

Nghia

Report message to a moderator

Re: How to can back up some table to dump file [message #181441 is a reply to message #181438]

Mon, 10 July 2006 02:57

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Oracle, depending on version your use, provides export (previous versions) or data pump export (10g).

You'll need something like this:

OS> EXP username/password@database TABLES=M% FILE=m_tables.dmp

To store your whole database into the CSV files, you'd have to do it table by table, writing and .SQL query and spool the output into the file.

Report message to a moderator

Re: How to can back up some table to dump file [message #181454 is a reply to message #181441]

Mon, 10 July 2006 03:55

nghiant
Messages: 38
Registered: July 2006
Location: Viet Nam

Member

Thank for your reply.
But I still don't execute this command. I think I am using Oracle client that I do not have exp utility.
I connect to Oracle server and copy this utility to client in bin directory.
I run exp.exe, however have a message
"Message 206 not found;
10.1.0.2.0 - Production on 月 7月 1

Copyright (c) 1982, 2004, Oracl

Invalid format of Export utility na

Verify that ORACLE_HOME is properly

Export terminated unsuccessfully"

Please tell me know why.

(I can run this tool in Oracle Server)

Report message to a moderator

Re: How to can back up some table to dump file [message #181462 is a reply to message #181454]

Mon, 10 July 2006 04:09

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

True, Client installation doesn't install import and export utilities. To be able to use them, take the Server installation set and choose custom install; in there, select utilities you need.

Report message to a moderator

Re: How to can back up some table to dump file [message #181509 is a reply to message #181441]

Mon, 10 July 2006 06:18

nghiant
Messages: 38
Registered: July 2006
Location: Viet Nam

Member

Littlefoot wrote on Mon, 10 July 2006 02:57

To store your whole database into the CSV files, you'd have to do it table by table, writing and .SQL query and spool the output into the file.

Can you write demo for me ? I 'm need it.
Follow me, I can write a client program. With a sql statement, I select it and save to csv file. Laughing

It is not good, but can use.

Can I using Oracle Server as Oracle Client ??

Report message to a moderator

Re: How to can back up some table to dump file [message #181521 is a reply to message #181509]

Mon, 10 July 2006 06:52

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Demo is here:

SQL> spool dept.csv
SQL> select deptno ||','|| dname ||','||loc
  2  from dept;

DEPTNO||','||DNAME||','||LOC
---------------------------------------------
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

SQL> spool off;

It would, of course, be stupid to write such a code for ALL tables you have. Automated process could be done using a PL/SQL block which would loop through all tables and, knowing columns from the data dictionary, dynamically create a SELECT statement; UTL_FILE package would then be used to write data into the .csv file.

However, I prefer simple EXPORT and IMPORT utilities.

Report message to a moderator

Re: How to can back up some table to dump file [message #181538 is a reply to message #181521]

Mon, 10 July 2006 07:58

nghiant
Messages: 38
Registered: July 2006
Location: Viet Nam

Member

Thank for comment.
I have a new request. When using third software, I see we can save file in csv file as:

deptno, dname, loc
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

However spool statement will return result not beautiful, it still some reduntant text as
"select deptno ||','|| dname ||','||loc from dept;".
Perhaps using only sql statement to do this task /?

Report message to a moderator

Previous Topic:	back up
Next Topic:	Regarding Archive log files ......

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 11:30:16 CST 2024