Oracle Database Import and export practices

Oracle Database Import and export practices

There is an Oracle server that needs to be exported and imported. I have been familiar with Oracle before, but I have never studied Oracle Export and Import. Now I am in a hurry, so I have to make up the Internet. The operation result is as follows:

Oracle Data Export and Import
Step 1: Create the user's tablespace and data files
Log On As an oracle user
[Oracle @ oracle ~] $ Ps-ef | grep pmon | grep-v grep
Oracle 4052 1 0 Jun23? 00:00:10 ora_pmon_zlzxdb
Oracle 4488 1 0 Jun23? 00:00:07 ora_pmon_yibiaodb
From the pmon process, we can see that I have two instances. to log on to multiple instances, you need to switch the environment variable.
[Oracle @ oracle ~] $ Export ORACLE_SID = zlzxdb
[Oracle @ oracle ~] $ Sqlplus/as sysdba # Log On as A dba
SQL> conn/as sysdba link to database
Connected.
SQL> select username, default_tablespace from user_users; view the currently logged-on user

USERNAME DEFAULT_TABLESPACE
------------------------------------------------------------
SYS SYSTEM

SQL> show parameter instance_name; view the current instance

NAME TYPE
--------------------------------------------------------------------
VALUE
------------------------------
Instance_name string
Zlzxdb
SQL> select name from v $ database; view the current database name
 
NAME
---------
ZLZXDB

SQL> select name from v $ datafile; query the data file path

Create bigfile tablespace xmjidian datafile
'/U01/app/oracle/oradata/zlzxdb/disk4/oracle_xmjidian_data.dbf' size 2048 m autoextend on; create big data files and increase automatically

Create USER xmjidian identified by xmjidian; create xmjidna and set the password to xmjidan. You can also use the following syntax to create the default tablespace of a specified user, or change it to the default tablespace of the user.
Create user xmjidian identified by xmjidian default tablespace xmjidna;
SQL> alter user xmjidian default tablespace xmjidian; Modify xmjidian user's default tablespace to xmjidian

Grant dba to xmjidian; grant dba permissions. Generally, this authorization is too large. After importing data, you can revoke dba permissions or directly authorize users.
Grant connect, resource, create any view to xmjidian;

Create an Export Import directory and grant permissions
Create export directory
Create directory exp_dir as '/u01/app/oracle/oradata/zlzxdb/pai_data ';

Grant export Directory Permissions
Grant read, write on directory exp_dir to xmjidian;

Create import directory
Create or replace directory data_dir as '/u01/app/oracle/oradata/zlzxdb/pai_data ';

Grant read, write on directory data_dir to xmjidian;

Query the Directory and name of the created exported import File
Select * from dba_directories;

Data Export and Import
Export
Expdp zlzxdb/zlzxdb @ zlzxdbrw schemas = zlzxdb dumpfile = oracle_xmjidian.dmp
DIRECTORY = exp_dir;

Data export is slow and patient

Data Import
Import data as an xmjidian user

Impdp xmjidian/xmjidian @ zlzxdb directory = data_dir dumpfile = oracle_xmjidian.dmp
Remap_schema = zlzxdb: xmjidian exclude = user full = y;

Impdp yibiaodb01/yibiaodb01 DIRECTORY = dump_dir DUMPFILE = export REMAP_SCHEMA = ybljt02: yibiaodb01 remap_tablespace = ybljt02data: Large, ybljt02index: Large

Export data troubleshooting:
ORA-01653: Table XMJIDIAN. LZ901017 cannot be extended through 8192 (in table space ZLZXDATA01) due to insufficient table space in zlzxdata01,

Solution: Add a tablespace to zlzxdata01
Alter tablespace zlzxdata01 add datafile '/u01/app/oracle/oradata/zlzxdb/disk4/oracle_xmjidian_zlzxddata01.dbf' size 2048 m autoextend on;

Select * from dba_role_privs where grantee = 'zlzxdb'; view ZLZXDB User Permissions