Can we create new additional database for Oracle Expression Edition? Because No DBCA available in express edition, However we can create database using manual script.
We can follow way for normal database creation manual steps only…
Consider new Database Name is “XETEST”
Step 1: Create suitable directory for new database.
like below
C:\oraclexe\app\oracle\admin\XETEST
C:\oraclexe\app\oracle\oradata\XETEST
Step 2: Create new instance for new database
C:\Windows\system32>oradim -new -sid xetest
Instance created.
Step 3 : Create password file for new database
C:\Windows\system32>orapwd file=C:\oraclexe\app\oracle\product\11.2.0\server\database\PWDXETEST.ora password=sysxe
Step 4 : Create New pfile like below
xe.__db_cache_size=117440512
xe.__java_pool_size=4194304
xe.__large_pool_size=8388608
xe.__oracle_base='C:\oraclexe\app\oracle'#ORACLE_BASE set from environment
xe.__pga_aggregate_target=188743680
xe.__sga_target=566231040
xe.__shared_io_pool_size=92274688
xe.__shared_pool_size=335544320
xe.__streams_pool_size=0
*.audit_file_dest='C:\oraclexe\app\oracle\admin\XETEST\adump'
*.compatible='11.2.0.0.0'
*.control_files='C:\oraclexe\app\oracle\oradata\XETEST\control.dbf'
*.db_name='XETEST'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\flash_recovery_area'
*.diagnostic_dest='C:\oraclexe\app\oracle'###########################################
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XETESTXDB)'
*.job_queue_processes=4
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=AZAR-PC.ace-ins.com)(PORT=1522))'
*.open_cursors=300
*.os_authent_prefix=''
*.pga_aggregate_target=180M
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.sga_target=540M
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
Step 5: Stratup nomount stage db using new pfile
.
C:\Windows\system32>SET ORACLE_SID=XETEST
C:\Windows\system32>sqlplus
SQL*Plus: Release 11.2.0.2.0 Beta on Sun May 22 15:23:42 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='d:\temp\initXETEST.ora';
ORACLE instance started.
Total System Global Area 564957184 bytes
Fixed Size 1384956 bytes
Variable Size 171970052 bytes
Database Buffers 385875968 bytes
Redo Buffers 5726208 bytes
Step 6: Create Database Script like below
create database XETEST
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\temp\redolog\REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'D:\temp\redolog\REDO02.LOG' SIZE 50M BLOCKSIZE 512
DATAFILE'C:\oraclexe\app\oracle\oradata\XETEST\SYSTEM.DBF' size 100m autoextend on
sysaux datafile 'C:\oraclexe\app\oracle\oradata\XETEST\SYSAUX.DBF' size 100m autoextend on
undo tablespace undotbs1 datafile 'C:\oraclexe\app\oracle\oradata\XETEST\UNDOTBS1.DBF' size 100m autoextend on
CHARACTER SET AL32UTF8
;
Step 7: After created it, you can run at sql prompt
SQL> @D:\temp\create.sql
Database created.
Step 8: Now database was created, Check Instance status & Version
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta
PL/SQL Release 11.2.0.2.0 - Beta
CORE 11.2.0.2.0 Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Beta
NLSRTL Version 11.2.0.2.0 - Production
SQL>
The above newly created database is Express edition version.
And also RUN
SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\catalog.sql
SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\catproc.sql
DBA 