Oracle startup, tablespace creation, user, authorization, Database Import and Export tutorial

Oracle startup, tablespace creation, user, authorization, Database Import and Export tutorial

The most basic operation of a database is to import and export data and create tablespaces, users, and authorizations, today, we will also introduce the commands for Oracle startup, table space creation, user authorization, and Database Import and export.

　　** Start **
1. Start the database instance in two steps: Step 1: Start the listener; Step 2: Start the database instance.

　　1.1 go to sqlplus to start the instance
{{{

[Oracle @ redhat ~] $ Su-oracle -- "switch to oracle user"

[Oracle @ redhat ~] $ Lsnrctl start -- "enable listener"

[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 -- "Disable listener"

}}}

　　** Common Operations **
{{{

Connect to other users: SQL> conn ts/ts

View all the tables under the user SQL> select * from user_tables;

Check which database instances are available: SQL> select instance_name from v $ instance; [view which instances are available]

View users: SQL> select username from dba_users; [view corresponding users]

View the Oracle version: SQL> select * from v $ version;

Create user SQL> create user ts identified by ts; SQL> grant dba to ts;

}}}

　　** Create a tablespace, user, and authorization **
{{{

-- View the dbf storage location

SQL> select * from dba_data_files; -- view the file location SELECT * FROM dba_directories;

-- Create a tablespace

SQL> 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
SQL> create user bp_oracle identified by bp_oracle default tablespace bp_oracle;

　　-- User authorization
SQL> 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.

SQL> grant read, write on directory dump_dir to bp_oracle; -- can be created without dump_dir

SQL> create directory dump_dir as 'G:/oracle_dump_dir '; -- view the 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: ing

SQL> impdp bp_oracle/bp_oracle directory = dump_dir dumpfile = ncp201209.dmp remap_schema = ncp: bp_oracle remap_tablespace = ncp: bp_oracle

-- Data export with version

SQL> expdp bp_oracle/bp_oracle DIRECTORY = dump_dir dumpfile = bp_oracle.dmp version = 10.2.0.1.0

-- Delete a tablespace

SQL> 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.

SQL> drop user bp_oracle cascade;

-- Change User Password

SQL> alter user bp_oracle identified by bp_oracle;

}}}

　　** Expdp Import and Export **
{{{

-- Export database without version

SQL> expdp bp_oracle/bp_oracle schemas = bp_oracle DUMPFILE = bp_oracle20120221.dmp DIRECTORY = DUMP_DIR JOB_NAME = full

-- Export the database with version

SQL> expdp bp_oracle/bp_oracle schemas = bp_oracle DIRECTORY = dump_dir dumpfile = bp_oracle20120221.dmp version = 10.2.0.1.0

}}}

　　** Import and Export of EXP and IMP (commonly used )**
{{{

-- Export data specified 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 is not followed by parentheses, in addition, tables should not be used with the owner and will attempt to conflict. The owner and tables cannot be both specified. The owner specifies the data of the specified user to be exported. The tables parameter specifies the table to be exported.

-- Import data with ing relationships

SQL> imp nmswxt_mhzz/nmswxt_mhzz file =/home/oracle/dmp/nmswxt_mhwz_news_content.dmp fromuser = nmswxt_mhwz touser = nmswxt_mhzz

}}}