Oracle start, CREATE table space, user, authorization, database Import Export Use tutorial

* * Start * *
1, start the database instance, is divided into two steps: The first step, starts listens; the second step is to start the database instance.

1.1 Go to Sqlplus startup instance
{{{
[Oracle@redhat ~]$ su-oracle--"Switch to Oracle User"
[Oracle@redhat ~]$ lsnrctl Start--"turn on Listening"
[Oracle@redhat ~]$ Sqlplus/nolog--"Go to Sqlplus"
Sql> conn/as SYSDBA--"Connect to SYSDBA"
Sql> Startup--"Start database instance"
sql> shutdown Immediate--"Close database instance"
[Oracle@redhat ~]$ Lsnrctl stop--"Turn off listening"
}}}

* * Common Operations * *
{{{
Connect to another user:sql> conn ts/ts
View all the tables under the user Sql>select * from User_tables;
See what database instances are available: Sql>select instance_name from v$instance; "See what instances are available"
See which users: sql> select username from dba_users; "View the corresponding users"
View Oracle Version: sql> select * from V$version;
Create user TS identified by TS Sql> Sql> Grant DBA to TS;

}}}

* * CREATE table space, user, Authorization * *
{{{
--View 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 m Axsize 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; --The CMS system needs to increase grant create view to Bp_oracle views permissions

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

--Database Import 1: normal
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 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

--Remove Tablespace
Sql> drop tablespace bp_oracle including CONTENTS and datafiles;
--delete the user, and execute the statement carefully, the Cascade deletes all objects under that user.
sql> drop user bp_oracle cascade;
--Modify User password
sql> alter user bp_oracle identified by bp_oracle;
}}}

**EXPDP Import Export * *
{{{
--Export database with no version
SQL>EXPDP bp_oracle/bp_oracle schemas=bp_oracle dumpfile=bp_oracle20120221.dmp DIRECTORY=DUMP_DIR JOB_NAME=full
--Export Database with version
sql> EXPDP bp_oracle/bp_oracle schemas=bp_oracle directory=dump_dir dumpfile=bp_oracle20120221.dmp 10.2.0.1.0
}}}

**exp, IMP Import export (commonly used) * *
{{{
]--Export data specify table name data
Sql>exp NMSWXT_MHWZ/NMSWXT_MHWZ file=/ Home/oracle/dmp/nmswxt_mhwz_news_content.dmp tables= table 1, table 2, table 3//tables without parentheses, and tables do not use with owner, will try to conflict, Owner and tables cannot be specified at the same time. Owner specifies the data to export the specified user, the tables parameter specifies the table to be exported
--import data, with mapping relationship
Sql>imp Nmswxt_mhzz/nmswxt_mhzz file=/home/oracle/dmp/ Nmswxt_mhwz_news_content.dmp fromuser=nmswxt_mhwz Touser=nmswxt_mhzz
}}}