Oracle 11g linux create a tablespace user authorized Database Import and Export

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