This article introduces some common statements used to import and export tablespaces in oracle 11g linux. For more information, see.
Sqlplus
/As sysdba
-- View the dbf storage location
Select * from dba_data_files; -- view the file location SELECT * FROM dba_directories;
-- Create a tablespace
Create tablespace bp_oracle logging datafile '/u02/oradata/devdb/bp_oracle.dbf' size 100 m autoextend on next 50 m maxsize 200 m extent management local;
-- Create a user
Create user bp_oracle identified by bp_oracle default tablespace bp_oracle;
-- User authorization
Grant connect, resource to bp_oracle; -- grant create view to bp_oracle view permission must be added to the cms system.
-- You can access dump_dir for easy import and export operations.
Grant read, write on directory dump_dir to bp_oracle; -- can be created without dump_dir
Create directory dump_dir as 'G:/oracle_dump_dir '; -- view the directory select * from dba_directories;
-- Database Import 1: normal
Impdp bp_oracle/bp_oracle directory = dump_dir dumpfile = bp_oracle20120209.dmp
-- Database Import 2: ing
Impdp bp_oracle/bp_oracle directory = dump_dir dumpfile = ncp20120209.dmp remap_schema = ncp: bp_oracle remap_tablespace = ncp: bp_oracle
-- Data export with version
Expdp bp_oracle/bp_oracle DIRECTORY = dump_dir dumpfile = bp_oracle.dmp version = 10.2.0.1.0
-- Delete a tablespace
Drop tablespace bp_oracle including CONTENTS and datafiles;
-- Delete a user. Be careful when executing this statement. All objects under the user will be deleted cascade.
Drop user bp_oracle cascade;
-- Change User Password
Alter user bp_oracle identified by bp_oracle;
-- Export database without version
Expdp bp_oracle/bp_oracle schemas = bp_oracle DUMPFILE = bp_oracle20120221.dmp DIRECTORY = DUMP_DIR JOB_NAME = full
-- Export the database without version or version
Expdp bp_oracle/bp_oracle schemas = bp_oracle DIRECTORY = dump_dir dumpfile = bp_oracle20120221.dmp version = 10.2.0.1.0
-- Export database without version
Expdp bp_oracle/bp_oracle schemas = bp_oracle DUMPFILE = bp_oracle20120221.dmp DIRECTORY = DUMP_DIR JOB_NAME = full
-- Export the database without version or version
Expdp bp_oracle/bp_oracle schemas = bp_oracle DIRECTORY = dump_dir dumpfile = bp_oracle20120221.dmp version = 10.2.0.1.0
-- Export data specified table name data
Exp nmswxt_mhwz/nmswxt_mhwz file =/home/oracle/dmp/nmswxt_mhwz_news_content.dmp tables = NEWS_CONTENT
-- Import data with ing relationships
Imp nmswxt_mhzz/nmswxt_mhzz file =/home/oracle/dmp/nmswxt_mhwz_news_content.dmp fromuser = nmswxt_mhwz touser = nmswxt_mhzz
-- Create materialized view authorization
Grant create materialized view to nmswxt_sw;
-- Scheduled task authorization
Grant create job to nmswxt_sw;
# Starting a database
STARTUP # STARTUP routine sqlplus/as sysdba
Lsnrctl start # enable the default listening directory