删除和创建数据库的问题 --100分，急

[maxy]

不用dbca，在一段java程序里。数据库名：ora8
1。删除用drop database好像不行，请问用什么语句？
2。创建的时候create database 老说权限不足，用internal登陆后好像可以，
不过不知道怎么写。

很急很急，哪位高手帮着解决一下。

[dinya2003]

创建数据库这样的动作一般不会经常发生,建一次就够用一辈子了. :) 所以,用工具创建吧. 也免去了程序创建带来的麻烦.

[maxy]

那好，就算不让客户使用，谁能告诉我怎么解决也行啊，让我长点知识。

[bzszp]

完整的手工建立ORACLE数据库步骤

作者：Winder（杭州）
E_MAIL: [e-mail]
URL: http://www.qyhhschool.com

关键词：经过排错的 经过调试的 完整的 整理他人的 手工建库 ORACLE


数据库：　Oracle 8i R2 (8.1.6)
安装路径：/data/ora816
数据库实例：web
数据库全局名称：web.edi.com

1、手工创建相关目录
/data/ora816/admin/web
/data/ora816/admin/web/adhoc
/data/ora816/admin/web/bdump
/data/ora816/admin/web/cdump
/data/ora816/admin/web/create
/data/ora816/admin/web/exp
/data/ora816/admin/web/pfile
/data/ora816/admin/web/udump

/data/ora816/oradata/web
/data/ora816/oradata/web/archive

2、手工创建初始化启动参数文件：/data/ora816/admin/web/pfile/initweb.ora
检查$ORACLE_HOME/dbs目录下是否有初始化文件，比如如果您的sid是web,则在此目录下应该有一个initweb.ora的文件，如果没有，您可以将init.ora文件复制成initweb.ora文件：cp init.ora initweb.ora.如果您的sid是web,则在此目录下应该有一个initweb.ora的文件，如果没有，您可以将init.ora文件复制成initweb.ora文件：cp init.ora initweb.ora.
ln -s /data/ora816/admin/web/initweb.ora /data/ora816/dbs/initweb.ora

附:initweb.ora内容（见后）

3、使用orapwde命令，创建/data/ora816/dbs/orapwweb
命令：orapwd file=/data/ora816/dbs/orapwweb password=ORACLE entries=5

4、检查环境变量是否设置：ORACLE_HOME,ORACLE_SID，检查方法：输入命令echo $ORACLE_HOME,或者echo $ORACLE_SID，如果这两个环境变量没有设置，您将会发现输出一个空行，这时您可以通过以root权限登录，修改/etc/profile文件，在末尾填上：ORACLE_HOME=您安装ORACLE时的主路径，如/data/ora816 ORACLE_SID=您安装时所定的sid,如orcl,我在上面的文件中是web
export ORACLE_HOME ORACLE_SID
同时您还应该修改PATH环境变量，以便于使用工具svrmgrl,sqlplus等。将bin目录加入到PATH中。

5、分别检查是否存在下列文件$ORACLE_HOME/rdbms/admin/catdbsyn.sql，$ORACLE_HOME/rdbms/admin/catproc.sql，$ORACLE_HOME/sqlplus/admin/pupbld.sql，如果不存在，请重新安装您的oracle系统。

6、准备创建脚本：create_db.sql

rem 脚本开始
connect internal
startup nomount pfile="$ORACLE_HOME/admin/web/pfile/initweb.ora"
set echo on
spool makedb.log
create database web
datafile '$ORACLE_HOME/oradata/web/web_syst_01.dbf' size 500M reuse
logfile
'$ORACLE_HOME/oradata/web/web_redo_01.dbf' size 10M reuse,
'$ORACLE_HOME/oradata/web/web_redo_02.dbf' size 10M reuse,
'$ORACLE_HOME/oradata/web/web_redo_03.dbf' size 10M reuse
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
MAXDATAFILES 254
MAXINSTANCES 1
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET ZHS16GBK;

connect INTERNAL/oracle
rem --修改系统表空间
ALTER TABLESPACE SYSTEM DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 50);
ALTER TABLESPACE SYSTEM MINIMUM EXTENT 64K;
connect INTERNAL/oracle

rem --修改系统表空间
ALTER TABLESPACE SYSTEM DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 50);
ALTER TABLESPACE SYSTEM MINIMUM EXTENT 64K;

rem --创建回滚表空间
CREATE TABLESPACE RBS DATAFILE '/data/ora816/oradata/web/rbs01.dbf' SIZE 256M REUSE
AUTOEXTEND ON NEXT 5120K
MINIMUM EXTENT 512K
DEFAULT STORAGE ( INITIAL 512K NEXT 512K MINEXTENTS 8 MAXEXTENTS 4096);

rem --创建用户表空间
CREATE TABLESPACE USERS DATAFILE '/data/ora816/oradata/web/users01.dbf' SIZE 128M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);

[bzszp]

rem --创建临时表空间
CREATE TABLESPACE TEMP DATAFILE '/data/ora816/oradata/web/temp01.dbf' SIZE 32M REUSE
AUTOEXTEND ON NEXT 640K
MINIMUM EXTENT 64K
DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) TEMPORARY;

rem --创建工具表空间
CREATE TABLESPACE TOOLS DATAFILE '/data/ora816/oradata/web/tools01.dbf' SIZE 64M REUSE
AUTOEXTEND ON NEXT 320K
MINIMUM EXTENT 32K
DEFAULT STORAGE ( INITIAL 32K NEXT 32K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);

rem --创建索引表空间
CREATE TABLESPACE INDX DATAFILE '/data/ora816/oradata/web/indx01.dbf' SIZE 32M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);

rem --创建回滚段
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 );

rem --使回滚段在线
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;

rem --修改sys用户的临时表空间为TEMP
alter user sys temporary tablespace TEMP;

rem --创建数据字典表
connect system/manager
@$ORACLE_HOME/rdbms/admin/catdbsyn.sql

connect internal
@/data/ora816/rdbms/admin/catalog.sql;
@/data/ora816/rdbms/admin/catexp7.sql
@/data/ora816/rdbms/admin/catproc.sql
@/data/ora816/rdbms/admin/caths.sql

connect system/manager
@/data/ora816/sqlplus/admin/pupbld.sql

connect internal/oracle
@/data/ora816/rdbms/admin/catrep.sql

rem --修改system用户默认表空间和临时表空间
alter user system default tablespace SYSTEM;
alter user system temporary tablespace TEMP;

spool off
rem 脚本结束


7、执行脚本
您可以进行执行脚本了，转到目录：$ORACLE_HOME/dbs中去，并且将上面的create_db.sql也放到此目录中，下面开始了：

键入命令：svrmgrl回车
SVRMGR>connect internal
connected.
SVRMGR>startup回车
SVRMGR>@create_db.sql回车(ok,下面您就可以看到屏幕在不断的显时操作过程，大概需要半个多小时的时间或许还要长一点点)。
执行完毕之后您可能需要退出去
SVRMGR>exit
没有错误的话，现在您的数据库已经生成了。

8、生成SQL*Plus帮助系统
sqlplus SYSTEM/manager
@/data/ora816/sqlplus/admin/help/helpbld.sql helpus.sql

9、部分说明

1)、create database web
datafile '$ORACLE_HOME/oradata/web/web_syst_01.dbf' size 500M reuse
logfile
'$ORACLE_HOME/oradata/web/web_redo_01.dbf' size 10M reuse,
'$ORACLE_HOME/oradata/web/web_redo_02.dbf' size 10M reuse,
'$ORACLE_HOME/oradata/web/web_redo_03.dbf' size 10M reuse

请注意看一下生成的文件名字：web_syst_01.dbf，web_redo_01.dbf，web_redo_02.dbf，web_redo_03.dbf，请注意将您的sid换成相应的部分，在我的例子中我的sid是web。

另外还有生成表空间时的几个文件：
create tablespace rollback
datafile '$ORACLE_HOME/dbs/web_roll_01.dbf' size 8M reuse;

create tablespace temp
datafile '$ORACLE_HOME/dbs/web_temp_01.dbf' size 5M reuse
temporary;

create tablespace users
datafile '$ORACLE_HOME/dbs/web_user_01.dbf' size 10M reuse;

最好也要改成相应的名字

2)、connect system/manager
@$ORACLE_HOME/rdbms/admin/catdbsyn.sql
connect internal
@$ORACLE_HOME/rdbms/admin/catproc.sql
connect system/manager
@$ORACLE_HOME/sqlplus/admin/pupbld.sql

其中的connect system/manager 是假设您的system账号的密码就是manager(系统默认的)，如果您通过alter user system identified by 修改了密码，请换成正确的密码登录。

3)、initweb.ora文件内容：
db_name = "web"
instance_name = web
service_names = web.edi.com
db_files = 1024
control_files = ("/data/ora816/oradata/web/control01.ctl", "/data/ora816/oradata/web/control02.ctl", "/d
ata/ora816/oradata/web/control03.ctl")
open_cursors = 200
max_enabled_roles = 30
db_file_multiblock_read_count = 8
db_block_buffers = 4096
shared_pool_size = 52428800
large_pool_size = 78643200
java_pool_size = 20971520
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 115
parallel_max_servers = 5
log_buffer = 32768
max_dump_file_size = 10240
global_names = true
oracle_trace_collection_name = ""
background_dump_dest = /data/ora816/admin/web/bdump
user_dump_dest = /data/ora816/admin/web/udump
db_block_size = 16384
remote_login_passwordfile = exclusive
os_authent_prefix = ""
job_queue_processes = 4
job_queue_interval = 60
open_links = 4
distributed_transactions = 10
mts_dispatchers = "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
mts_dispatchers = "(protocol=TCP)"
compatible = 8.1.0
sort_area_size = 65536
sort_area_retained_size = 65536

# log_archive_start = true
# log_archive_dest_1 = "location=/data/ora816/oradata/oradb/archive"
# log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

[jack_4826]

用不了这么麻烦，用工具不就行了吗！！

[maxy]

我再等等看有没有更好的办法。 bzszp(SongZip）转的我看到过，可在程序里不能写这么多吧。

不用dbca，在一段java程序里。数据库名：ora8
1。删除用drop database好像不行，请问用什么语句？
2。创建的时候create database 老说权限不足，用internal登陆后好像可以，
不过不知道怎么写。

把1和2解决了我就结贴了。

[maxy]

昨天下午去海图转了一圈也没查到。drop数据库的sql该怎么写才合适，IBM的网站上说oracle没有直接drop的语句，"是通过删除所有关联的数据文件来删除数据库的",这个删除关联的文件是指手工删除还是用sql的，如果是sql怎么写？谢谢！

[postfxj]

有工具不用，為什麼搞得那麼麻煩。

[maxy]

用DBCA当然没问题，我就是想知道手工drop数据库的sql怎么写，不过分吧这位。
我相信还是有人会的，希望不吝赐教。

[a040liutao]

DBCA怎么用啊

[sunyt]

我也遇到了同样的问题 手动建库 如果是sqlserver就简单多了 因为有个默认的master库

但是oracle就很麻烦了 请高手指点

[wangjingjing390]

Database Creation


On your hard drive, you will see a folder called "Oracle." Inside the folder, you will want to create three additional folders, as follows:
Data
Log
Admin

Edit the init.ora file that you copied from the website. The first thing you must do is to change the name so that it matches the name of your new database. The SID for this database was "SHANGHAI." You must change that to match the name for your new database. Then you need to change the following items:
Make sure the paths to background_dump_dest, core_dump_dest, and user_dump_dest are correct. You will need to create the folders inside the Admin directory.
Change the path of the control files so that the control files will be placed in the Log folder.

If you are using a system with limited memory, you will want to make the following adjustments:
Set db_cache_size to 5000000
Set java_pool_size to 5000000
Set shared_pool_size to 5000000
Set large_pool_size to 0

Place the new init.ora file in the Oracle\Ora92\Database folder.

Create a password file using the ORAPWD utility. The best way to find out how to do this is to go to the command prompt and type:

orapwd
This will bring up the information showing the correct syntax, which is as follows:


orapwd file=pwdNAME.ora password=password entries=5
Place this file in the Database folder along with the init.ora file.


Create a registry string value as follows:
Open the registry editor(regedit)
Drill to HKEY_LOCAL_MACHINE/
SOFTWARE/ORACLE/HOME0
Select New/String Value and enter ORACLE_SID. The value for this string will be the name of your database.

Create a Windows service for the instance at the command prompt:

oradim -new -sid Name -startmode a -pfile c:\oracle\Ora92\database\initNAME.ora

Go to the command prompt and connect to SQL*Plus like this:

sqlplus/nolog

Connect to Oracle: connect sys/password as sysdba

Edit the createdb script in notepad. Be sure that the paths are correct.

Run the script to create the database.

Run the catalog and catproc scripts.

[wangjingjing390]

To remove all Oracle components from a computer on Windows NT, Windows 2000, and Windows XP:

Log in as a member of the Administrators group.
Go to Start Ø Settings Ø Control Panel Ø Administrative Tools Ø Services and stop all Oracle services.
Start the registry at the MS-DOS command prompt:
Go to HKEY_CLASSES_ROOT.
Delete any key that starts with Oracle, ORA, or ORCL.
Go to HKEY_LOCAL_MACHINE\SOFTWARE.
Delete the ORACLE and Apache Group keys.
Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services.
Delete all keys under here that begin with ORACLE.
Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services \Eventlog\Application.
Delete all keys under here that begin with ORACLE.
Go to HKEY_CURRENT_USER.
Delete ORACLE.
Go to HKEY_CURRENT_USER\SOFTWARE\ORACLE.
Delete keys that start with Oracle or ORCL (if any exist).
Delete any Oracle keys (if any exist).
Close the registry.
Restart your computer.
Update the System Variable Path
Go to Start > Settings > Control Panel > System > Environment tab.
Choose the system variable path and modify the Path variable.
Remove any Oracle entries from the path. For example, if JRE was installed by Oracle, remove the %ORACLE_HOME%\BIN path and the JRE path. You may see a path similar to this one:
C:\oracle\ora81\bin;C:\program files\oracle\jre\1.1.7\bin

Exit the Control Panel.
Remove Oracle from the Start Menu
Go to SYSTEM_DRIVE:\Documents and Settings\all users\start menu\programs.
Delete the following icons:
Oracle - HOME_NAME
Oracle Installation Products
where HOME_NAME is the previous Oracle home name.

Delete SYSTEM_DRIVE:\program files\oracle through Windows Explorer.
Delete all ORACLE_BASE directories on your hard drive. In most cases, this will include the Oracle folder that contains the "guts" of your database system, as well as the Oracle folder in "Program Files" on the partion that holds your operating system.
Restart your computer.

[baojianjun]

mark

[cl_oracle]

mark

[lwj_dxy]

up

[wzjcntlqs]

用自带的工具就可以了啊DBCA

[xxrl]

wangjingjing390(晶晶) 同学的东西适合9i以上的数据库吗？

[kyh1234567]

wangjingjing390(晶晶)
MM，你太帅了，有时间也教教俺English :-)

　　　　　　　　顶

[ashg_16700]

顶