oracle 初次安装oracle数据库11g快捷版后如何新建数据库？

How to create a new database after initally installing oracle database 11g Express Edition?

提问by Zo72

I have installed Oracle Database 11g Expressed Edition on my pc (windows 7) and I have installed Oracle SQL Developer as well.

我已经在我的电脑（Windows 7）上安装了 Oracle Database 11g Expressed Edition 并且我也安装了 Oracle SQL Developer。

I want to create a simple database to start with, maybe with one table or two and then use Oracle SQL Developer to insert data and query it.

我想创建一个简单的数据库开始，可能有一个或两个表，然后使用 Oracle SQL Developer 插入数据并查询它。

When I open Oracle SQL Developer, it asks me to create a new connection, therefore it assumes that a database has already been created.

当我打开 Oracle SQL Developer 时，它要求我创建一个新连接，因此它假定已经创建了一个数据库。

So my question is, how do I create an initial database in Oracle 11g?

所以我的问题是，如何在 Oracle 11g 中创建初始数据库？

采纳答案by Ollie

This link: Creating the Sample Database in Oracle 11g Release 2is a good example of creating a sample database.

此链接：在 Oracle 11g 第 2 版中创建示例数据库是创建示例数据库的一个很好的示例。

This link: Newbie Guide to Oracle 11g Database Common Problemsshould help you if you come across some common problems creating your database.

如果您在创建数据库时遇到一些常见问题，此链接：Oracle 11g 数据库常见问题新手指南应该会对您有所帮助。

Best of luck!

祝你好运！

EDIT: As you are using XE, you should have a DB already created, to connect using SQL*Plus and SQL Developer etc. the info is here: Connecting to Oracle Database Express Edition and Exploring It.

编辑：当您使用 XE 时，您应该已经创建了一个数据库，以便使用 SQL*Plus 和 SQL Developer 等进行连接。信息在这里：Connecting to Oracle Database Express Edition and Exploring It。

Extract:

提炼：

Connecting to Oracle Database XE from SQL Developer SQL Developer is a client program with which you can access Oracle Database XE. With Oracle Database XE 11g Release 2 (11.2), you must use SQL Developer version 3.0. This section assumes that SQL Developer is installed on your system, and shows how to start it and connect to Oracle Database XE. If SQL Developer is not installed on your system, see Oracle Database SQL Developer User's Guide for installation instructions.

Note:

For the following procedure: The first time you start SQL Developer on your system, you must provide the full path to java.exe in step 1.

For step 4, you need a user name and password.

For step 6, you need a host name and port.

To connect to Oracle Database XE from SQL Developer:

Start SQL Developer.

For instructions, see Oracle Database SQL Developer User's Guide.

If this is the first time you have started SQL Developer on your system, you are prompted to enter the full path to java.exe (for example, C:\jdk1.5.0\bin\java.exe). Either type the full path after the prompt or browse to it, and then press the key Enter.

The Oracle SQL Developer window opens.

In the navigation frame of the window, click Connections.

The Connections pane appears.

In the Connections pane, click the icon New Connection.

The New/Select Database Connection window opens.

In the New/Select Database Connection window, type the appropriate values in the fields Connection Name, Username, and Password.

For security, the password characters that you type appear as asterisks.

Near the Password field is the check box Save Password. By default, it is deselected. Oracle recommends accepting the default.

In the New/Select Database Connection window, click the tab Oracle.

The Oracle pane appears.

In the Oracle pane:

For Connection Type, accept the default (Basic).

For Role, accept the default.

In the fields Hostname and Port, either accept the defaults or type the appropriate values.

Select the option SID.

In the SID field, type accept the default (xe).

In the New/Select Database Connection window, click the button Test.

The connection is tested. If the connection succeeds, the Status indicator changes from blank to Success.

Description of the illustration success.gif

If the test succeeded, click the button Connect.

The New/Select Database Connection window closes. The Connections pane shows the connection whose name you entered in the Connection Name field in step 4.

You are in the SQL Developer environment.

To exit SQL Developer, select Exit from the File menu.

从 SQL Developer 连接到 Oracle Database XE SQL Developer 是一个客户端程序，您可以使用它访问 Oracle Database XE。对于 Oracle Database XE 11g 第 2 版 (11.2)，您必须使用 SQL Developer 3.0 版。本节假设您的系统上安装了 SQL Developer，并展示了如何启动它并连接到 Oracle Database XE。如果您的系统上未安装 SQL Developer，请参阅 Oracle Database SQL Developer 用户指南以获取安装说明。

笔记：

对于以下过程：首次在系统上启动 SQL Developer 时，必须在步骤 1 中提供 java.exe 的完整路径。

对于步骤 4，您需要用户名和密码。

对于第 6 步，您需要一个主机名和端口。

要从 SQL Developer 连接到 Oracle Database XE：

启动 SQL Developer。

有关说明，请参阅 Oracle 数据库 SQL 开发人员用户指南。

如果这是您第一次在系统上启动 SQL Developer，系统会提示您输入 java.exe 的完整路径（例如，C:\jdk1.5.0\bin\java.exe）。在提示后键入完整路径或浏览到它，然后按 Enter 键。

Oracle SQL Developer 窗口打开。

在窗口的导航框架中，单击连接。

将出现“连接”窗格。

在“连接”窗格中，单击图标新建连接。

新建/选择数据库连接窗口打开。

在“新建/选择数据库连接”窗口中，在“连接名称”、“用户名”和“密码”字段中键入适当的值。

为安全起见，您键入的密码字符显示为星号。

密码字段附近是复选框保存密码。默认情况下，它被取消选中。Oracle 建议接受默认值。

在新建/选择数据库连接窗口中，单击选项卡 Oracle。

出现 Oracle 窗格。

在 Oracle 窗格中：

对于连接类型，接受默认值（基本）。

对于角色，接受默认值。

在主机名和端口字段中，接受默认值或键入适当的值。

选择选项 SID。

在 SID 字段中，键入接受默认值 (xe)。

在新建/选择数据库连接窗口中，单击测试按钮。

已测试连接。如果连接成功，状态指示器将从空白变为成功。

插图成功说明.gif

如果测试成功，请单击“连接”按钮。

新建/选择数据库连接窗口关闭。“连接”窗格显示您在步骤 4 中在“连接名称”字段中输入其名称的连接。

您在 SQL Developer 环境中。

要退出 SQL Developer，请从文件菜单中选择退出。

回答by Albert T. Wong

When you installed XE.... it automatically created a database called "XE". You can use your login "system" and password that you set to login.

当您安装 XE.... 它会自动创建一个名为“XE”的数据库。您可以使用您设置的登录“系统”和密码登录。

Key info

关键信息

server: (you defined)
port: 1521
database: XE
username: system
password: (you defined)

服务器：（您定义）
端口：1521
数据库：XE
用户名：系统
密码：（您定义）

Also Oracle is being difficult and not telling you easily create another database. You have to use SQL or another tool to create more database besides "XE".

Oracle 也很困难，不会告诉您轻松创建另一个数据库。除了“XE”之外，您还必须使用 SQL 或其他工具来创建更多数据库。

回答by APC

"How do I create an initial database ?"

“如何创建初始数据库？”

You created a database when you installed XE. At some point the installation process prompted you to enter a password for the SYSTEM account. Use that to connect to the XE database using the SQL commandline on the application menu.

您在安装 XE 时创建了一个数据库。在某些时候，安装过程会提示您输入 SYSTEM 帐户的密码。使用应用程序菜单上的 SQL 命令行连接到 XE 数据库。

The XE documentation is online and pretty helpful. Find it here.

XE 文档在线并且非常有用。 在这里找到它。

回答by pinei

If you wish to create a new schema in XE, you need to create an USER and assign its privileges. Follow these steps:

如果您希望在 XE 中创建新模式，您需要创建一个 USER 并分配其权限。按着这些次序：

• Open the SQL*Plus Command-line

• 打开SQL*Plus 命令行

SQL> connect sys as sysdba

• Enter the password

• 输入密码

SQL> CREATE USER myschema IDENTIFIED BY Hga&dshja;
SQL> ALTER USER myschema QUOTA unlimited ON SYSTEM;
SQL> GRANT CREATE SESSION, CONNECT, RESOURCE, DBA TO myschema;
SQL> GRANT ALL PRIVILEGES TO myschema;

Now you can connect via Oracle SQL Developerand create your tables.

现在您可以通过Oracle SQL Developer进行连接并创建您的表。

回答by AVA

Save the following code in a batch file (ex. createOraDbWin.bat). Change the parameter values like app_name, ora_dir etc., Run the file with administrative privileges. The batch file creates a basic oracle database:

将以下代码保存在批处理文件中（例如 createOraDbWin.bat）。更改 app_name、ora_dir 等参数值，以管理权限运行文件。批处理文件创建了一个基本的 oracle 数据库：

Note: May take much time (say around 30mins)

注意：可能需要很长时间（比如大约 30 分钟）

REM ASSUMPTIONS
rem oracle xe has been installed
rem oracle_home has been set
rem oracle_sid has been set
rem oracle service is running

REM SET PARAMETERS
set char_set =al32utf8
set nls_char_set =al16utf16

set ora_dir=d:\app\db\oracle
set version=11.2.0.0
set app_name=xyz
set db_name=%app_name%_db
set db_sid=%db_name%_sid
set db_ins=%db_name%_ins
set sys_passwd=x3y5z7
set system_passwd=1x4y9z

set max_log_files=32
set max_log_members=4
set max_log_history=100
set max_data_files=254
set max_instances=1

set version_dir=%ora_dir%\%version%
set db_dir=%version_dir%\%db_name%

set instl_temp_dir=%db_dir%\instl\script

set system_dir=%db_dir%\system
set user_dir=%db_dir%\user
set undo_dir=%db_dir%\undo
set sys_aux_dir=%db_dir%\sysaux
set temp_dir=%db_dir%\temp
set control_dir=%db_dir%\control

set pfile_dir=%db_dir%\pfile
set data_dir=%db_dir%\data
set index_dir=%db_dir%\index
set log_dir=%db_dir%\log
set backup_dir=%db_dir%\backup
set archive_dir=%db_dir%\archive

set data_dir=%db_dir%\data
set index_dir=%db_dir%\index
set log_dir=%db_dir%\log
set backup_dir=%db_dir%\backup
set archive_dir=%db_dir%\archive
set undo_dir=%db_dir%\undo
set default_dir=%db_dir%\default

set system_tbs=%db_name%_system_tbs
set user_tbs=%db_name%_user_tbs
set sys_aux_tbs=%db_name%_sys_aux_tbs
set temp_tbs=%db_name%_temp_tbs
set control_tbs=%db_name%_control_tbs

set data_tbs=%db_name%_data_tbs
set index_tbs=%db_name%_index_tbs
set log_tbs=%db_name%_log_tbs
set backup_tbs=%db_name%_backup_tbs
set archive_tbs=%db_name%_archive_tbs
set undo_tbs=%db_name%_undo_tbs
set default_tbs=%db_name%_default_tbs

set system_file=%system_dir%\%db_name%_system.dbf
set user_file=%user_dir%\%db_name%_user.dbf
set sys_aux_file=%sys_aux_dir%\%db_name%_sys_aux.dbf
set temp_file=%temp_dir%\%db_name%_temp.dbf
set control_file=%control_dir%\%db_name%_control.dbf

set data_file=%data_dir%\%db_name%_data.dbf
set index_file=%index_dir%\%db_name%_index.dbf
set backup_file=%backup_dir%\%db_name%_backup.dbf
set archive_file=%archive_dir%\%db_name%_archive.dbf
set undo_file=%undo_dir%\%db_name%_undo.dbf
set default_file=%default_dir%\%db_name%_default.dbf

set log1_file=%log_dir%\%db_name%_log1.log
set log2_file=%log_dir%\%db_name%_log2.log
set log3_file=%log_dir%\%db_name%_log3.log

set init_file=%pfile_dir%\init%db_sid%.ora
set db_create_file=%instl_temp_dir%\createdb.sql
set db_drop_file=dropdb.sql

set db_create_log=%instl_temp_dir%\db_create.log
set db_drop_log=db_drop.log

set oracle_sid=%db_sid%

REM WRITE DROP DATABASE SQL COMMANDS TO FILE
echo shutdown immediate;>%db_drop_file%
echo startup mount exclusive restrict;>>%db_drop_file%
echo drop database;>>%db_drop_file%

REM EXECUTE DROP DATABASE SQL COMMANDS FROM THE FILE    
rem sqlplus -s "/as sysdba" @"%db_drop_file%">%db_drop_log%

REM DELETE WINDOWS ORACLE SERVICE
rem oradim -delete -sid %db_sid%

REM CREATE DIRECTORY STRUCTURE
md %system_dir%
md %user_dir%
md %sys_aux_dir%
md %temp_dir%
md %control_dir%

md %pfile_dir%
md %data_dir%
md %index_dir%
md %log_dir%
md %backup_dir%
md %archive_dir%
md %undo_dir%
md %default_dir%
md %instl_temp_dir%

REM WRITE INIT FILE PARAMETERS TO INIT FILE
echo db_name='%db_name%'>%init_file%
echo memory_target=1024m>>%init_file%
echo processes=150>>%init_file%
echo sessions=20>>%init_file%
echo audit_file_dest=%user_dir%>>%init_file%
echo audit_trail ='db'>>%init_file%
echo db_block_size=8192>>%init_file%
echo db_domain=''>>%init_file%
echo diagnostic_dest=%db_dir%>>%init_file%
echo dispatchers='(protocol=tcp) (service=%app_name%xdb)'>>%init_file%
echo shared_servers=4>>%init_file%
echo open_cursors=300>>%init_file%
echo remote_login_passwordfile='exclusive'>>%init_file%
echo undo_management=auto>>%init_file%
echo undo_tablespace='%undo_tbs%'>>%init_file%
echo control_files = ("%control_dir%\control1.ora", "%control_dir%\control2.ora")>>%init_file%
echo job_queue_processes=4>>%init_file%
echo db_recovery_file_dest_size = 10g>>%init_file%
echo db_recovery_file_dest=%log_dir%>>%init_file%
echo compatible ='11.2.0'>>%init_file%

REM WRITE DB CREATE AND ITS RELATED SQL COMMAND TO FILE    
echo startup nomount pfile='%init_file%';>>%db_create_file%
echo.>>%db_create_file%

echo create database %db_name%>>%db_create_file%
echo user sys identified by %sys_passwd%>>%db_create_file%
echo user system identified by %system_passwd%>>%db_create_file%
echo logfile group 1 ('%log1_file%') size 100m,>>%db_create_file%
echo group 2 ('%log2_file%') size 100m,>>%db_create_file%
echo group 3 ('%log3_file%') size 100m>>%db_create_file%
echo maxlogfiles %max_log_files%>>%db_create_file%
echo maxlogmembers %max_log_members%>>%db_create_file%
echo maxloghistory %max_log_history%>>%db_create_file%
echo maxdatafiles %max_data_files%>>%db_create_file%
echo character set %char_set %>>%db_create_file%
echo national character set %nls_char_set %>>%db_create_file%
echo extent management local>>%db_create_file%
echo datafile '%system_file%' size 325m reuse>>%db_create_file%
echo sysaux datafile '%sys_aux_file%' size 325m reuse>>%db_create_file%
echo default tablespace %default_tbs%>>%db_create_file%
echo datafile '%default_file%'>>%db_create_file%
echo size 500m reuse autoextend on maxsize unlimited>>%db_create_file%
echo default temporary tablespace %temp_tbs%>>%db_create_file%
echo tempfile '%temp_file%'>>%db_create_file%
echo size 20m reuse>>%db_create_file%
echo undo tablespace %undo_tbs%>>%db_create_file%
echo datafile '%undo_file%'>>%db_create_file%
echo size 200m reuse autoextend on maxsize unlimited;>>%db_create_file%
echo.>>%db_create_file%

echo @?\rdbms\admin\catalog.sql>>%db_create_file%
echo.>>%db_create_file%

echo @?\rdbms\admin\catproc.sql>>%db_create_file%
echo.>>%db_create_file%

echo create spfile from pfile='%init_file%';>>%db_create_file%
echo.>>%db_create_file%

echo shutdown immediate;>>%db_create_file%
echo.>>%db_create_file%

echo startup;>>%db_create_file%
echo.>>%db_create_file%

echo show parameter spfile;>>%db_create_file%
echo.>>%db_create_file%

REM CREATE WINDOWS ORACLE SERVICE
oradim -new -sid %db_sid% -startmode auto

REM EXECUTE DB CREATE SQL COMMANDS FROM FILE
sqlplus -s "/as sysdba" @"%db_create_file%">%db_create_log%

pause

Welcome your corrections and improvements!

欢迎您的指正和改进！