手动创建oracle数据库

安装oracle时，只安装软件
---------------------------
创建密码验证文件
orapwd file=/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/orapwbilly password=billy entries=10

创建初始化参数文件
[oracle@localhost dbs]$ pwd
/u01/app/oracle/oracle/product/10.2.0/db_1/dbs
cat initbilly.ora
billy.__db_cache_size=96468992
billy.__java_pool_size=4194304
billy.__large_pool_size=4194304
billy.__shared_pool_size=58720256
billy.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/billy/adump'
*.background_dump_dest='/u01/app/oracle/admin/billy/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/billy/control01.ctl','/u01/app/oracle/oradata/billy/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/billy/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='billy'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=billyXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/u01/app/oracle/admin/billy/udump'

创建相应的目录:
mkdir -p /u01/app/oracle/oradata/billy
mkdir -p /u01/app/oracle/admin/billy
cd /u01/app/oracle/admin/billy
mkdir adump cdump udump bdump

设置SID及其他环境变量
export ORACLE_SID=billy

创建数据库
sqlplus /nolog
SQL> conn / as sysdba
SQL> startup pfile=$ORACLE_HOME/dbs/initbilly.ora nomount

注意：undo tablespace UNDOTBS2-->这个名字要和initbilly.ora中定义的一致
SQL>create database billy
maxinstances 4
maxloghistory 2
maxlogfiles 16
maxlogmembers 2
maxdatafiles 8
character set zhs16gbk
national character set al16utf16
datafile '/u01/app/oracle/oradata/billy/system.dbf' size 20m autoextend on next 1m maxsize unlimited
sysaux datafile '/u01/app/oracle/oradata/billy/sysaux.dbf' size 20m autoextend on next 1m maxsize unlimited
default temporary tablespace temp tempfile '/u01/app/oracle/oradata/billy/temp.dbf' size 20m extent management local uniform size 32k
undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/billy/UNDOTBS2.dbf' size 50m
logfile
group 1 ('/u01/app/oracle/oradata/billy/redo0101.dbf') size 10m,
group 2 ('/u01/app/oracle/oradata/billy/redo0201.dbf') size 10m;

或把创建命令放在一个文件里 @filename调用
在创建过程的日志记录在/u01/app/oracle/admin/billy/bdump/alert_billy.log文件

如果要删除数据库，方法:
1、DBCA
2、删除相应的目录
3、Oracle 10g命令drop database。
SQL> conn / as sysdba
SQL> startup pfile=$ORACLE_HOME/dbs/initbilly.ora nomount restrict exclusive mount;
SQL> drop database;.

创建数据库内部对象

SQL> @ ?/rdbms/admin/catalog.sql;
SQL> @ ?/rdbms/admin/catproc.sql;

以上在执行的时候，如想记录执行的LOG，可用
SQL> spool catproc.log
SQL> @ ?/rdbms/admin/catproc.sql;
SQL> spool off

(以上创建DATABASE时，创建的是系统表空间)
创建用户表空间
create tablespace users datafile '/u01/app/oracle/oradata/billy/users.dbf' size 50M uniform size 64k;

创建用户
create user vincent identified by vincent default tablespace users;
grant create session to vincent;
grant create table to vincent;
alter user vincent quota unlimited on users;
SQL> create table stu(
2 id int,
3 name varchar2(20));
Table created.

SQL> insert into stu values(1,'vincent');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from stu;

ID NAME
---------- --------------------
1 vincent
1 row selected.