Import and export of Oracle DMP files

Import and export of DMP files:

Import and export of the whole library

1. Exporting from a remote machine
EXPDP Npmdb/[email protected] Schemas=npmdb directory=my_dir dumpfile=npmdb_87.dmp

Parameter description
Schemas: can be seen as the database name, directory: Dmp,log file directories, DumpFile: The exported DMP file name
Note: Creation of the Directory directory
Create directory Data_pump_dir as '/opt/data/oracle_tmp_data/';
View of directory Directories
SELECT Privilege,directory_name, Directory_path from User_tab_privs T, all_directories D WHERE t.table_name (+) = D.direct Ory_name ORDER by 2, 1;
assigning permissions
Grant Read,write on directory Data_pump_dir to Npmdb;

2. The exported DMP file is placed on the machine to be imported

3.dmp file Import
IMPDP Npmdb/[email protected] Remap_schema=npmdb:npmdb directory=my_dir dumpfile=npmdb_87.dmp TRANSFORM=segment_ Attributes:n Remap_tablespace=npmdbs:npmdb
Parameter description
Remap_schema=npmdb (Exported database): Npmdb (the database to be imported), directory: The catalog that holds the DMP files to import, DumpFile: The imported DMP file name, Remap_tablespace=npmdbs ( Table space Exported): Npmdb (imported tablespace)
Note: An empty library is required for full library import
Creation of directory when creating references to directories when exporting

Import and export with query

Export the first 10000 rows of a specific table
EXPDP Nmosdb/[email protected] directory=my_dir dumpfile=nmosdb_87.dmp tables=tai_scene_controlorder query= \ "WHERE Rownum\<\=10000\ ";

Import a specific table
IMPDP Nmosdb/[email protected] tables=nmosdb. Tai_scene_controlorder Directory=my_dir dumpfile=nmosdb_87.dmp transform=segment_attributes:n

Exclude a table Export
EXPDP Nmosdb/[email protected] schemas=nmosdb directory=my_dir dumpfile=nmosdb_87.dmp exclude=table:\ "in \ (\ ' TPP_ Event_his\ ' \) \ "

Problems you may encounter during import and export:

Require imported libraries to be empty libraries when importing

To view all tables under the current user:

Select COUNT (*) from User_tables;

or select COUNT (*) from user_tab_comments;

If the query result is not 0, all objects under the current user are deleted

There are several ways to delete database objects:

A. Deleting a user

drop user Nrmdb cascade;
The following error occurred:
ora-01940:cannot Drop a user that is currently connected
by viewing the user's process and kill the user process, and then deleting the user
Select sid,serial# from v$session where username= ' Nrmdb ';
alter system kill session ' 50,1122 ';
alter system kill session ' 54,3248 ';
drop user Nrmdb cascade;
Note: This removes the corresponding user
you need to create a new user and specify the default tablespace for the new user
create user Nrmdb identified by nrmoptr;
Grant DBA to Nrmdb;
alter user Nrmdb default tablespace nrmdb;

B. Using a statement to delete

--delete Tables
Select ' Drop table ' | | table_name | | '; ' | | Chr (13) | | Chr (from user_tables);
--delete views
select ' Drop View ' | | | view_name| | '; ' | | Chr (13) | | Chr (from user_views);
--delete seqs
select ' Drop sequence ' | | sequence_name| | '; ' | | Chr (13) | | Chr (from user_sequences);
--delete Functions
Select ' Drop function ' | | object_name| | '; ' | | Chr (13) | | Chr (Ten) from user_objects where object_type= ' FUNCTION ';
--delete Procedure
Select ' drop procedure ' | | object_name| | '; ' | | Chr (13) | | Chr (Ten) from user_objects where object_type= ' PROCEDURE ';
--delete Package
select ' Drop package ' | | object_name| | '; ' | | Chr (13) | | Chr (Ten) from user_objects where object_type= ' package ';
--delete Trigger
select ' Drop TRIGGER ' | | Sys_context (' USERENV ', ' current_user ') | | ' "." | | trigger_name | | ' "; ' | | CHR (13) | | CHR (from user_triggers);

C. Using Stored procedures

An error occurred while deleting a table:

drop table tpp_event_his;
The error that occurred:
Ora-00054:resource busy and acquire with NOWAIT specified or timeout expired
The main reason is that a transaction is executing (or the transaction has been locked), all of which cause the execution to be unsuccessful.
1. Users with DBA authority to see which locks are in the database

Select T2.username,t2.sid,t2.serial#,t2.logon_time
From V$locked_object t1,v$session T2
where T1.session_id=t2.sid order by T2.logon_time;

2, according to the SID view specific SQL statements, if SQL is not important, you can kill

Select Sql_text from v$session a,v$sqltext_with_newlines b
where DECODE (a.sql_hash_value, 0, Prev_hash_value, sql_hash_value) =b.hash_value
and a.sid=&sid order by piece;

3. Kill the transaction
Alter system kill session ' 339,13545 ';

Problem with char type character set when importing

When a ORA-02374: such as error occurs, it is possible that there are fields of type char in the table, which is required when this situation occurs:
Querying the character set of the Oracle server side
Select Userenv (' language ') from dual;

Query the character set of the DMP file
Select Nls_charset_name (To_number (' 0354 ', ' xxxx ')) from dual;

Querying the character set of the Oracle client side
Echo $NLS _lang

If the result of the check finds that the server side is inconsistent with the client-side character set, uniformly modify the same character set as the server side.

Other issues that are most likely to be used by SQL

View Table Spaces
Select Tablespace_name from Dba_tablespaces;

View Nrmdb user's default tablespace, temporary tablespace
Select Username,default_tablespace,temporary_tablespace
From Dba_users
where username = ' Npmdb ';

See all the Users
Select username from dba_users;

To modify the password for an Oracle user
ALTER USER Nmosdb identified by Nmosoptr;

To view the path to a data file
Select Tablespace_name,file_id,bytes/1024/1024,file_name from Dba_data_files order by file_id;

View all table space sizes
Select Tablespace_name,sum (bytes)/1024/1024 from Dba_data_files Group by Tablespace_name;

Table space size already in use
Select Tablespace_name,sum (bytes)/1024/1024 from Dba_free_space Group by Tablespace_name;

Create a large file table space
Create Bigfile tablespace fmdb logging datafile '/opt/data/oracle_data/fmdbs.dbf ' size 100G autoextend off extent Manageme NT local segment space management auto;

Linux default does not support. rar files, to be processed under Linux. rar files, need to install RAR for Linux, can be downloaded from the Internet, but remember that RAR for Linux is not free; available from http://www.rarsoft.com/ download.htm Download Rarfor Linux 3.2.

Multiple files are compressed into a single compressed file
Tar czvf DB_JS.tar.gz fmdb_87.dmp,kpidb_87.dmp,nmosdb_87.dmp,npmdb_87.dmp,nrmdb_87.dmp
The following error occurred:
Tar:exiting with failure status due to previous errors
Workaround: Remove multiple files between
Tar czvf DB_JS.tar.gz fmdb_87.dmp kpidb_87.dmp nmosdb_87.dmp npmdb_87.dmp nrmdb_87.dmp

Import and export of Oracle DMP files