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
}}}