Oracle startup, table space, user, authorization, database import and export under Linux

1.1 Go to Sqlplus launch instance

[[email protected] ~]$ su-oracle--"Switch to Oracle User"
[[email protected] ~]$ lsnrctl Start--"turn on monitoring"
[Email protected] ~]$ Sqlplus/nolog--"Go to Sqlplus"
Sql> conn/as SYSDBA--"Connect to SYSDBA"
Sql> Startup--"Start db instance"
sql> shutdown Immediate--"close db instance"
[[email protected] ~]$ lsnrctl stop--"Turn off monitoring"


Common Operations

连接到其他用户:SQL>  conn ts/ts
查看用户下所有的表      SQL>select * from user_tables;  
查看有哪些数据库实例： SQL>select instance_name from v$instance;   【查看有哪些实例】
查看有哪些用户： SQL> select username from dba_users;     【查看对应的用户】
查看Oracle的版本: SQL>  select * from v$version ;  
创建用户  SQL> create user ts identified by ts;     SQL> grant dba to ts;


CREATE table spaces, users, authorizations

--View the DBF storage location
Sql> select * from Dba_data_files; --View File location SELECT * from Dba_directories;

--New Table space
sql> Create tablespace bp_oracle logging datafile '/u02/oradata/devdb/bp_oracle.dbf ' size 100m autoextend on next 50m MaxSize 200m extent management local;

--New user
Sql> create user bp_oracle identified by bp_oracle default Tablespace bp_oracle;

--User Authorization
Sql> Grant Connect,resource to bp_oracle; --CMS system needs to add grant create view to bp_oracle views permission

--User can access Dump_dir to facilitate import and export operations
Sql> Grant Read,write on the directory Dump_dir to Bp_oracle; --If no dump_dir can be established
Sql> Create directory Dump_dir as ' G:/oracle_dump_dir '; --View Catalog select * from Dba_directories;

--Database Import 1: normal condition
sql> IMPDP bp_oracle/bp_oracle Directory=dump_dir dumpfile=bp_oracle20120209.dmp
--Database Import 2: Mapping situation
sql> IMPDP bp_oracle/bp_oracle directory=dump_dir dumpfile=ncp20120209.dmp remap_schema=ncp:bp_oracle remap_ Tablespace=ncp:bp_oracle
--data export, can be with version
sql> EXPDP bp_oracle/bp_oracle directory=dump_dir dumpfile=bp_oracle.dmp version=10.2.0.1.0

--Delete Table space
Sql> drop tablespace bp_oracle including CONTENTS and datafiles;
--Delete the user, execute the statement carefully, cascade Delete all objects under the user.
sql> drop user bp_oracle cascade;
--Modify User password
sql> alter user bp_oracle identified by bp_oracle;


EXPDP Import and Export

-- 导出数据库不带版本
SQL>expdp bp_oracle/bp_oracle schemas=bp_oracle DUMPFILE=bp_oracle20120221.dmp DIRECTORY=DUMP_DIR JOB_NAME=full
-- 导出数据库 带版本
SQL> expdp bp_oracle/bp_oracle schemas=bp_oracle DIRECTORY=dump_dir dumpfile=bp_oracle20120221.dmp version=10.2.0.1.0


EXP, IMP Import and export (common way)

-- 导出数据 指定表名数据
SQL>exp nmswxt_mhwz/nmswxt_mhwz file=/home/oracle/dmp/nmswxt_mhwz_news_content.dmp tables=表1,表2,表3    //tables后面不要带括号，并且tables不要和owner一起用，会尝试冲突，owner与tables不能同时指定。owner是指定要导出指定用户的数据，tables参数指定要导出的表
-- 导入数据，带映射关系
SQL>imp nmswxt_mhzz/nmswxt_mhzz file=/home/oracle/dmp/nmswxt_mhwz_news_content.dmp fromuser=nmswxt_mhwz touser=nmswxt_mhzz