oracle11g建库语句,LInux Oracle11g手工建库教程

1、修改Oracle系统配置文件vi /etc/oratab

格式如下：

database_sid:oracle_home_dir:Y|N

SID：数据库主目录：Y或N(指系统在启动时是否可以自动启动数据库，如果设置为Y，才可以使用dbstart或者dbshut脚本)

输入新数据库名字，用新的db name复制orcl的路径追加在最后(请勿直接复制此处)：newdb:/opt/oracle/app/product/11.2.0/dbhome_1:N

2、修改环境变量. oraenv

输入db name：newdb

3、进入参数文件目录cd $ORACLE_HOME/dbs

4、生成新的初始化参数文件cp init.ora initnewdb.ora

vi initnewdb.ora

修改内容，并注意替换新数据库名称。db_name='newdb'

sga_target=1G

processes = 150

audit_file_dest='/opt/oracle/app/admin/newdb/adump'

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/opt/oracle/app/flash_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest='/opt/oracle/app'

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

# You may want to ensure that control files are created on separate physical

# devices

control_files = /opt/oracle/app/oradata/newdb/control1.dbf,/opt/oracle/app/oradata

/newdb/control2.dbf

compatible ='11.2.0'

5、创建系统文件夹mkdir -p /opt/oracle/app/admin/newdb/adump

mkdir -p /opt/oracle/app/flash_recovery_area

mkdir -p /opt/oracle/app/oradata/newdb

6、生成服务器参数文件

spfile是二进制的参数文件，可以从initSID.ora文件创建。sqlplus "/ as sysdba"

startup nomount

shutdown immediate

create spfile from pfile='/opt/oracle/app/product/11.2.0/dbhome_1/dbs/initnewdb.ora'

exit

7、创建密码文件orapwd file=orapwnewdb password=oracle

8、写一个创建数据库的脚本vi create_db.sql

注意替换数据库名。create database newdb

maxloghistory 1

maxlogfiles 16

maxlogmembers 3

maxdatafiles 1024

maxinstances 8

character set utf8

national character set al16utf16

datafile '/opt/oracle/app/oradata/newdb/system.dbf' size 1000m extent management local

sysaux datafile '/opt/oracle/app/oradata/newdb/sysaux.dbf' size 1000m

default temporary tablespace temp tempfile '/opt/oracle/app/oradata/newdb/temp01.dbf' size 1000m

extent management local uniform size 1m

undo tablespace undotbs1 datafile '/opt/oracle/app/oradata/newdb/undo1.dbf' size 1000m

logfile

group 1 ('/opt/oracle/app/oradata/newdb/log1.dbf') size 100m,

group 2 ('/opt/oracle/app/oradata/newdb/log2.dbf') size 100m,

group 3 ('/opt/oracle/app/oradata/newdb/log3.dbf') size 100m

user sys identified by 1234567

user system identified by 1234567

9、运行脚本

创建完成后为了保证正常使用必须再运行几个脚本，这里并没有全部运行。

当初在做的时候因为没有运行导致导入数据老是失败……

catalog.sql:

创建数据字典视图

catblock.sql:

创建ORACLE中一些锁机制相关的视图

catproc.sql:

创建ORACLE一些存储过程和包

catoctk.sql:

创建需要使用PL/ SQL 工具的接口

catexp.sql:

重置exp/impsqlplus /nolog

conn /as sysdba

startup nomount

@create_db.sql

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

@$ORACLE_HOME/rdbms/admin/catexp.sql

完成！