Shulou(Shulou.com)06/01 Report--

Just arrived at the company not long, there is an oracle server to export, import, before contact with oracle, but to oracle export, import has not been studied, this anxious bad me, so quickly surf the Internet to make up for it. The operation results are as follows:

Oracle data export and import

The first step is to create the user's tablespace and data file

Login as a user of oracle

[oracle@oracle ~] $ps-ef | grep pmon | grep-v grep

Oracle 4052 10 Jun23? 00:00:10 ora_pmon_zlzxdb

Oracle 4488 1 0 Jun23? 00:00:07 ora_pmon_yibiaodb

From the pmon process, you can see that I have two instances. Multi-instance login requires switching environment variables.

[oracle@oracle ~] $export ORACLE_SID=zlzxdb

[oracle@oracle ~] $sqlplus / as sysdba # # Log in as dba

SQL > conn / as sysdba link to the database

Connected.

SQL > select username,default_tablespace from user_users; to view currently logged in users

USERNAME DEFAULT_TABLESPACE

SYS SYSTEM

SQL > show parameter instance_name; to view the current instance

NAME TYPE

VALUE

-

Instance_name string

Zlzxdb

SQL > select name from vault database; view the current database name

NAME

-

ZLZXDB

SQL > select name from vault datafile; query data file path

Create bigfile tablespace xmjidian datafile

'/ u01 AUTOEXTEND ON; AUTOEXTEND ON; create big data files and grow them automatically

Create USER xmjidian IDENTIFIED BY xmjidian; creates the xmjidna and sets the password to xmjidan. Or you can create the user's default table space for the specified user using the following syntax, or you can finally change it to the user's default table space

CREATE USER xmjidian IDENTIFIED BY xmjidian DEFAULT TABLESPACE xmjidna

SQL > alter user xmjidian default tablespace xmjidian; modify the default tablespace for xmjidian users to xmjidian

The permission granted to dba by grant dba to xmjidian;. In general, this authorization is too large. After importing the data, you can withdraw the permission of dba or authorize the user directly.

Grant connect,resource,create any view to xmjidian

Create an export import directory and grant permissions

Create an export directory

Create directory exp_dir as'/ u01qapplash oracle.oradataAccording to zlzxdbdbshareownership data'

Grant permissions to the export directory

Grant read,write on directory exp_dir to xmjidian

Create an import directory

Create or replace directory data_dir as'/ u01/app/oracle/oradata/zlzxdb/share_data'

Grant read,write on directory data_dir to xmjidian

The directory and name of the export import file created by the query

Select * from dba_directories

Data export and import

Export under zlzxdb

Expdp zlzxdb/zlzxdb@zlzxdbrw schemas=zlzxdb dumpfile=oracle_xmjidian.dmp

DIRECTORY=exp_dir

Data export is slow and wait patiently.

Data import

Import data as users of xmjidian

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=ybljt02_20150706.dmp REMAP_SCHEMA=ybljt02:yibiaodb01 remap_tablespace=ybljt02data:YIBIAODB01DATA01,ybljt02index:YIBIAODB01INDEX01

Export data troubleshooting:

ORA-01653: table XMJIDIAN.LZ901017 cannot extend this type of failure through 8192 (in tablespace ZLZXDATA01) due to insufficient zlzxdata01 tablespace

Solution: add tablespaces to zlzxdata01

ALTER TABLESPACE zlzxdata01 ADD DATAFILE'/ u01 size size 2048m AUTOEXTEND ON

Select * from dba_role_privs where grantee = 'ZLZXDB'; to view the permissions of the zlzxdb user

Reference: http://www.ilanni.com/?p=4413

Http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL847 oracle official website documentation

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.