ORACLE 手工建库步骤和脚本

注意问题：对于Oracle不同的版本，建库脚本可能稍有不同，需要具体情况，具体分析。整个建库过程中如果出错了，错误日志记录在 ora_pid.trc文件和alert_instancename.log文件里。

主要步骤：

1,Create the initYahoo.ora by copying

2,Create the diretories needed by initYahoo.ora

3,创建一个密码文件

-bash-2.05b$ orapwd file='/opt/oracle/product/9/dbs/orapw' password=changed entries=5

4, 启动实例

SQL> connect sys/changed as sysdba

Connected to an idle instance.

SQL> startup pfile=/opt/oracle/product/9/dbs/initYahoo.ora nomount

ORACLE instance started.

Total System Global Area  219223120 bytes

Fixed Size                   451664 bytes

Variable Size             201326592 bytes

Database Buffers           16777216 bytes

Redo Buffers                 667648 bytes
  

5, 建库

create database Yahoo

controlfile reuse

logfile

group 1 (

'/opt/oracle/oradata/Yahoo/redo01a.log',

'/opt/oracle/oradata/Yahoo/redo01b.log' ) size 10M reuse,

group 2 (

'/opt/oracle/oradata/Yahoo/redo02a.log',

'/opt/oracle/oradata/Yahoo/redo02b.log' ) size 10M reuse,

group 3 (

'/opt/oracle/oradata/Yahoo/redo03a.log',

'/opt/oracle/oradata/Yahoo/redo03b.log' ) size 10M reuse

maxinstances 1

character set ZHS16GBK

national character set AL16UTF16

datafile

'/opt/oracle/oradata/Yahoo/system01.dbf' size 200M reuse

undo tablespace ts_undo

datafile '/opt/oracle/oradata/Yahoo/undo01.dbf'

size 50M reuse

default temporary tablespace ts_temp

tempfile '/opt/oracle/oradata/Yahoo/temp01.dbf'

size 50M reuse autoextend on next 50M maxsize 300M

6, 数据字典

connect sys as sysdba

spool test_catalog.log

@/opt/oracle/product/9/rdbms/admin/catalog.sql

@/opt/oracle/product/9/rdbms/admin/catproc.sql;

@/opt/oracle/product/9/rdbms/admin/caths.sql;

@/opt/oracle/product/9/rdbms/admin/catrep.sql;

@/opt/oracle/product/9/rdbms/admin/catexp.sql;

spool off

7, sqlplus需要

connect system

spool sth.log

@/opt/oracle/product/9/sqlplus/admin/pupbld.sql;

spool off

8,
以下的步骤，从网上看，是可做，可不做的

create tablespace rbs

   datafile

      '/oracle/DATA/rbstest01.dbf' size 150M reuse,

      '/oracle/DATA/rbstest02.dbf' size 150M reuse

   default storage (

      initial      1M

      next         1M

      pctincrease  0

      minextents   9

      maxextents   300  )

/

create tablespace temp
   datafile
      '/oracle/DATA/tmptest.dbf' size 500M reuse
   default storage (
      initial      128k
      next         128k
      pctincrease  0 )
      temporary
/

create rollback segment r01 tablespace rbs
   storage ( optimal 20M )
/

create rollback segment r02 tablespace rbs
   storage ( optimal 20M )
/

create rollback segment r03 tablespace rbs
   storage ( optimal 20M )
/

create rollback segment r04 tablespace rbs
   storage ( optimal 20M )
/

create rollback segment r05 tablespace rbs
   storage ( optimal 20M )
/

create rollback segment r06 tablespace rbs
   storage ( optimal 20M )
/

create rollback segment r07 tablespace rbs
   storage ( optimal 20M )
/

create rollback segment r08 tablespace rbs
   storage ( optimal 20M )
/

alter rollback segment r01 online
/

alter rollback segment r02 online
/

alter rollback segment r03 online
/

alter rollback segment r04 online
/

alter rollback segment r05 online
/

alter rollback segment r06 online
/

alter rollback segment r07 online
/

alter rollback segment r08 online
/

alter rollback segment r0  offline
/

alter user system temporary tablespace temp
/
spool off

connect INTERNAL/oracle

--修改系统表空间

ALTER TABLESPACE SYSTEM DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 50);

ALTER TABLESPACE SYSTEM MINIMUM EXTENT 64K;

--创建回滚表空间

CREATE TABLESPACE RBS DATAFILE 'D:Oracleoradatatestrbs01.dbf' SIZE 256M REUSE

AUTOEXTEND ON NEXT 5120K

MINIMUM EXTENT 512K

DEFAULT STORAGE ( INITIAL 512K NEXT 512K MINEXTENTS 8 MAXEXTENTS 4096);

--创建用户表空间

CREATE TABLESPACE USERS DATAFILE 'D:Oracleoradatatestusers01.dbf' SIZE 128M REUSE

AUTOEXTEND ON NEXT 1280K

MINIMUM EXTENT 128K

DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);

--创建临时表空间

CREATE TABLESPACE TEMP DATAFILE 'D:Oracleoradatatesttemp01.dbf' SIZE 32M REUSE

AUTOEXTEND ON NEXT 640K

MINIMUM EXTENT 64K

DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) TEMPORARY;

--创建工具表空间

CREATE TABLESPACE TOOLS DATAFILE 'D:Oracleoradatatesttools01.dbf' SIZE 64M REUSE

AUTOEXTEND ON NEXT 320K

MINIMUM EXTENT 32K

DEFAULT STORAGE ( INITIAL 32K NEXT 32K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);

--创建索引表空间

CREATE TABLESPACE INDX DATAFILE 'D:Oracleoradatatestindx01.dbf' SIZE 32M REUSE

AUTOEXTEND ON NEXT 1280K

MINIMUM EXTENT 128K

DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);

--创建回滚段

CREATE PUBLIC ROLLBACK SEGMENT RBS0 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS1 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS2 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS3 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS4 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS5 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS6 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS7 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS8 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS9 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS10 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS11 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS12 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS13 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS14 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS15 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS16 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS17 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS18 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS19 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS20 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS21 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS22 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS23 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

CREATE PUBLIC ROLLBACK SEGMENT RBS24 TABLESPACE RBS STORAGE ( OPTIMAL 4096K ;

--使回滚段在线

ALTER ROLLBACK SEGMENT "RBS0" ONLINE;

ALTER ROLLBACK SEGMENT "RBS1" ONLINE;

ALTER ROLLBACK SEGMENT "RBS2" ONLINE;

ALTER ROLLBACK SEGMENT "RBS3" ONLINE;

ALTER ROLLBACK SEGMENT "RBS4" ONLINE;

ALTER ROLLBACK SEGMENT "RBS5" ONLINE;

ALTER ROLLBACK SEGMENT "RBS6" ONLINE;

ALTER ROLLBACK SEGMENT "RBS7" ONLINE;

ALTER ROLLBACK SEGMENT "RBS8" ONLINE;

ALTER ROLLBACK SEGMENT "RBS9" ONLINE;

ALTER ROLLBACK SEGMENT "RBS10" ONLINE;

ALTER ROLLBACK SEGMENT "RBS11" ONLINE;

ALTER ROLLBACK SEGMENT "RBS12" ONLINE;

ALTER ROLLBACK SEGMENT "RBS13" ONLINE;

ALTER ROLLBACK SEGMENT "RBS14" ONLINE;

ALTER ROLLBACK SEGMENT "RBS15" ONLINE;

ALTER ROLLBACK SEGMENT "RBS16" ONLINE;

ALTER ROLLBACK SEGMENT "RBS17" ONLINE;

ALTER ROLLBACK SEGMENT "RBS18" ONLINE;

ALTER ROLLBACK SEGMENT "RBS19" ONLINE;

ALTER ROLLBACK SEGMENT "RBS20" ONLINE;

ALTER ROLLBACK SEGMENT "RBS21" ONLINE;

ALTER ROLLBACK SEGMENT "RBS22" ONLINE;

ALTER ROLLBACK SEGMENT "RBS23" ONLINE;

ALTER ROLLBACK SEGMENT "RBS24" ONLINE;

你好, 请问从oracel库里面导出整个库的数据? 可否指点一下呢