Oracle 11g table backup

Question

Oracle 11g table backup

Navigation

#1 by (3 votes)

3

I use oracle 11g in the free version. According to database classes with oracle, the free version does not allow you to create a database. You can create user and create tables. Based on this, how do I back up these tables? Example of the initial command:

COPY FROM <db> TO <db> <opt> <tbl> {<cls>> } USING <sel>
<db> : string de banco de dados;

An example of a bank name would be: hr/hr@banco1

I have a hotel name table with the fields (id, nomeHotel, endereco, qtd_quarto).

Another way I've seen on the internet is with expdp :

Creating directory:

CREATE DIRECTORY dp AS '...\datapump';  
GRANT EXP_FULL_DATABASE  to system;  
GRANT READ, WRITE ON DIRECTORY dp to system;

Is this directory in the system file explorer or is it from sgbd?

to export:

expdp systen/pasword@db10g full=Y directory=dp dumpfile=DB10G.dmp logfile=expdpDB10G.log

Some comments:

I created a user named andre and, connected to this user, created the tables. I noticed that in the commands the user is system. If I do with the system user, do I copy my tables? Or do I have to be logged in to my user?

The password I registered for the installation is root. Soon I connect: connect andre/root

oracle oracle11g

asked by anonymous 21.11.2016 / 19:25

1 answer

How to send information by local POST to external server? How do I filter my data?

score 3 · Answer 1

I believe a dump generated by the EXPDP utility is the simplest option.

The directory used as the dump target must exist in the operating system's file system and be referenced in the database by a Directory object.

Creating a directory on the operating system (use the terminal):

# mkdir /u02/DPUMP

Creating a directory in the database (use a client such as SQLPlus or Oracle SQL Developer):

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u02/DPUMP';

Grant the following privileges to the database user informed in the EXPDP call:

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO "U_DPUMP";
GRANT EXP_FULL_DATABASE to "U_DPUMP";

If you want to use the same user to import dumps, also grant this privilege:

GRANT IMP_FULL_DATABASE TO "U_DPUMP";

Although the SYSTEM user already has such privileges, for security reasons, in a production environment, do not use it for this purpose. Create a new user and grant the above privileges.

To export all the schemas of the database (use the terminal):

# expdp U_DPUMP/654321@XE full=y directory=DATA_PUMP_DIR dumpfile=dump_full_20170331_0800.dmp logfile=expdp_full_20170331_0800.log

To export a database schema (use the terminal):

# expdp U_DPUMP/654321@XE schemas=NOME_ESQUEMA directory=DATA_PUMP_DIR dumpfile=dump_nomeesquema_20170331_0800.dmp logfile=expdp_nomeesquema_20170331_0800.log

To export a table from a database schema (use the terminal):

# expdp U_DPUMP/654321@XE tables=NOME_ESQUEMA.NOME_TABELA directory=DATA_PUMP_DIR dumpfile=dump_nomeesquema_nometabela_20170331_0800.dmp logfile=expdp_nomeesquema_nometabela_20170331_0800.log