Oracle基础操作

 1# 切换到oracle用户。
 2[root@Udb ~]# su - oracle
 3
 4# 使用sqlplus登录到根。
 5[oracle@Udb ~]sqlplus /nolog
 6
 7SQL> conn / as sysdba
 8
 9# 启动数据库实例。
10SQL> startup
11
12# 停止数据库实例。
13SQL> shutdown immediate

1# 以下命令均基于oracle用户环境下执行。
2
3# 检查监听器状态
4[oracle@Udb ~]$ lsnrctl status
5
6# 启动监听器
7[oracle@Udb ~]$ lsnrctl start

 1# 根据前文中介绍的方法，使用sqlplus登录到根执行以下操作。
 2
 3# 建立表空间（正式/临时）
 4SQL> create tablespace testins datafile '/ora_ds/testins/testins.dbf' size 4096m;
 5
 6SQL> create bigfile tablespace testins datafile '/ora_ds/testins/testins.dbf' size 409600m;
 7
 8SQL> create temporary tablespace tmp_testins tempfile '/ora_ds/testins/tmp_testins.dbf' size 2048M autoextend on next 100M maxsize 4096M;
 9
10# 使用前文建立的表空间，建立新用户。
11SQL> create user testins identified by "6yhn*IK<" default tablespace testins temporary tablespace tmp_testins;
12
13# 向新建用户授权。
14SQL> grant resource,connect,dba to testins;

 1# exp命令使用参考
 2[oracle@Udb ~]$ exp -help
 3
 4Export: Release 11.2.0.1.0 - Production on Fri Sep 19 02:47:25 2014
 5
 6Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 7
 8You can let Export prompt you for parameters by entering the EXP
 9command followed by your username/password:
10
11     Example: EXP SCOTT/TIGER
12
13Or, you can control how Export runs by entering the EXP command followed
14by various arguments. To specify parameters, you use keywords:
15
16     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
17     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
18               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
19
20USERID must be the first parameter on the command line.
21
22Keyword    Description (Default)      Keyword      Description (Default)
23--------------------------------------------------------------------------
24USERID     username/password          FULL         export entire file (N)
25BUFFER     size of data buffer        OWNER        list of owner usernames
26FILE       output files (EXPDAT.DMP)  TABLES       list of table names
27COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
28GRANTS     export grants (Y)          INCTYPE      incremental export type
29INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
30DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
31LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
32ROWS       export data rows (Y)       PARFILE      parameter filename
33CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)
34
35OBJECT_CONSISTENT    transaction set to read only during object export (N)
36FEEDBACK             display progress every x rows (0)
37FILESIZE             maximum size of each dump file
38FLASHBACK_SCN        SCN used to set session snapshot back to
39FLASHBACK_TIME       time used to get the SCN closest to the specified time
40QUERY                select clause used to export a subset of a table
41RESUMABLE            suspend when a space related error is encountered(N)
42RESUMABLE_NAME       text string used to identify resumable statement
43RESUMABLE_TIMEOUT    wait time for RESUMABLE 
44TTS_FULL_CHECK       perform full or partial dependency check for TTS
45VOLSIZE              number of bytes to write to each tape volume
46TABLESPACES          list of tablespaces to export
47TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
48TEMPLATE             template name which invokes iAS mode export
49
50Export terminated successfully without warnings.

 1[oracle@Udb ~]$ imp -help
 2
 3Import: Release 11.2.0.1.0 - Production on Fri Sep 19 02:51:11 2014
 4
 5Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 6
 7You can let Import prompt you for parameters by entering the IMP
 8command followed by your username/password:
 9
10     Example: IMP SCOTT/TIGER
11
12Or, you can control how Import runs by entering the IMP command followed
13by various arguments. To specify parameters, you use keywords:
14
15     Format:  IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
16     Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
17               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
18
19USERID must be the first parameter on the command line.
20
21Keyword  Description (Default)       Keyword      Description (Default)
22--------------------------------------------------------------------------
23USERID   username/password           FULL         import entire file (N)
24BUFFER   size of data buffer         FROMUSER     list of owner usernames
25FILE     input files (EXPDAT.DMP)    TOUSER       list of usernames
26SHOW     just list file contents (N) TABLES       list of table names
27IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record
28GRANTS   import grants (Y)           INCTYPE      incremental import type
29INDEXES  import indexes (Y)          COMMIT       commit array insert (N)
30ROWS     import data rows (Y)        PARFILE      parameter filename
31LOG      log file of screen output   CONSTRAINTS  import constraints (Y)
32DESTROY                overwrite tablespace data file (N)
33INDEXFILE              write table/index info to specified file
34SKIP_UNUSABLE_INDEXES  skip maintenance of unusable indexes (N)
35FEEDBACK               display progress every x rows(0)
36TOID_NOVALIDATE        skip validation of specified type ids 
37FILESIZE               maximum size of each dump file
38STATISTICS             import precomputed statistics (always)
39RESUMABLE              suspend when a space related error is encountered(N)
40RESUMABLE_NAME         text string used to identify resumable statement
41RESUMABLE_TIMEOUT      wait time for RESUMABLE 
42COMPILE                compile procedures, packages, and functions (Y)
43STREAMS_CONFIGURATION  import streams general metadata (Y)
44STREAMS_INSTANTIATION  import streams instantiation metadata (N)
45DATA_ONLY              import only data (N)
46VOLSIZE                number of bytes in file on each volume of a file on tape
47
48The following keywords only apply to transportable tablespaces
49TRANSPORT_TABLESPACE import transportable tablespace metadata (N)
50TABLESPACES tablespaces to be transported into database
51DATAFILES datafiles to be transported into database
52TTS_OWNERS users that own data in the transportable tablespace set
53
54Import terminated successfully without warnings.