Backup oracle tables 11g

Question

Use oracle 11g in the free version. According to the database lessons with oracle, the free version does not allow creating database. One can create user and create tables. Based on this, how do I backup 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 saw 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; 

This directory is in the system file explorer or is sgbd?

to export:

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

Some remarks:

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

The password I registered in the installation is root. Logo conecto: connect andre/root

Answer 1

I believe that a logical backup (dump), generated through the EXPDP utility, is the simplest option.

The directory used as the dump destination must exist in the operating system’s file system and be referenced in the database by an object of type Directory.

Creating a directory in 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 wish 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 safety reasons, in a production environment, do not use it for this purpose. Create a new user and grant the privileges stated above.

To export all schematics from 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