Oracle Database - Creation of database query

Hi ,

How to create database in oracle 9i, with example.

i am need of query

i used create database DMS

it is throwing error as

insufficient privileges

Thanks,

shanti.

The following is the initialization parameter file used to create the mynewdb database.

Sample Initialization Parameter File

# Cache and I/O
DB_BLOCK_SIZE=4096
DB_CACHE_SIZE=20971520

# Cursors and Library Cache
CURSOR_SHARING=SIMILAR
OPEN_CURSORS=300

# Diagnostics and Statistics
BACKGROUND_DUMP_DEST=/vobs/oracle/admin/mynewdb/bdump
CORE_DUMP_DEST=/vobs/oracle/admin/mynewdb/cdump
TIMED_STATISTICS=TRUE
USER_DUMP_DEST=/vobs/oracle/admin/mynewdb/udump

# Control File Configuration
CONTROL_FILES=("/vobs/oracle/oradata/mynewdb/control01.ctl",
               "/vobs/oracle/oradata/mynewdb/control02.ctl",
               "/vobs/oracle/oradata/mynewdb/control03.ctl")

# Archive
LOG_ARCHIVE_DEST_1='LOCATION=/vobs/oracle/oradata/mynewdb/archive'
LOG_ARCHIVE_FORMAT=%t_%s.dbf
LOG_ARCHIVE_START=TRUE

# Shared Server
# Uncomment and use first DISPATCHES parameter below when your listener is
# configured for SSL 
# (listener.ora and sqlnet.ora)
# DISPATCHERS = "(PROTOCOL=TCPS)(SER=MODOSE)",
#               "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)"
DISPATCHERS="(PROTOCOL=TCP)(SER=MODOSE)",
            "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)",
             (PROTOCOL=TCP)

# Miscellaneous
COMPATIBLE=9.2.0
DB_NAME=mynewdb

# Distributed, Replication and Snapshot
DB_DOMAIN=us.oracle.com
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

# Network Registration
INSTANCE_NAME=mynewdb

# Pools
JAVA_POOL_SIZE=31457280
LARGE_POOL_SIZE=1048576
SHARED_POOL_SIZE=52428800

# Processes and Sessions
PROCESSES=150

# Redo Log and Recovery
FAST_START_MTTR_TARGET=300

# Resource Manager
RESOURCE_MANAGER_PLAN=SYSTEM_PLAN

# Sort, Hash Joins, Bitmap Indexes
SORT_AREA_SIZE=524288

# Automatic Undo Management
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=undotbs

details here http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/create.htm

Hi,

Creating an Oracle 9i database from the command line only
       
This article is the successor to Creating an Oracle database on NT from the command line only. This article's successor is this one.
On the command line only refers to the fact, that no GUI tool is needed to create a database. For Unix users, the command line is their shell while for Windows users, the command line is the dos box. Windows users usually start the dos box with start->run and then enter cmd and press enter.
Creating a database consists basically of the following steps:

    * Creating the database's datafiles, its controlfiles and its online redo log files.
    * Creating the system tablespace
    * Creating the system rollback segment
    * Creating the data dictionary
    * Specifying the character set for textual data in the database
    * Setting the database time zone

All these steps are executed through the create database statement. The location of the control files is specified in the init.ora file.
First, a SID must be defined. This is in order to distinguish several Instances that run on the same machine. The SID's value is stored in the ORACLE_SID environment variable:
Setting ORACLE_HOME and PATH

ORACLE_HOME=/appl/oracle/product/9.2.0.2
export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin

ORACLE_SID=adpdb
export ORACLE_SID

Windows Users will set the variable like so:

set ORACLE_SID=adpdb

Location of the control files
The location of the control files is specified in the init.ora file.
Here's a minimal init.ora (under $ORACLE_HOME/dbs if it is Unix, or %ORACLE_HOME%\database, if it is windows) just to demonstrate how the control files are found. Of course, you will add more init params into the init.ora file.

control_files = (/db2/adpdb/control01.ctl,
                 /redolog1/adpdb/control02.ctl,
                 /redolog2/adpdb/control03.ctl)


undo_management = auto


compatible = 9.2.0

db_name     = adpdb

The undo_management parameter is necessary if we want to use automatic undo management.
Although the above seems to be the bare required minimum, you probably also want do define background_dump_dest, core_dump_dest and user_dump_dest.
Starting the instance
On Windows like systems, starting the instance requires oradim:

oradim -new -sid %ORACLE_SID% -pfile c:\path\to\some\init.ora

On Unix like systems, there is no oradim required. On both, Windows and Unix like systems, it must be proceeded like this:

adpdb:/appl/oracle/product/9.2.0.2/dbs >sqlplus "/ as sysdba"


SQL*Plus: Release 9.2.0.2.0 - Production on Fri Jul 25 14:08:37 2003


Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to an idle instance.


SQL>startup nomount pfile=/path/to/the/init.ora
ORACLE instance started.


Total System Global Area  160925320 bytes
Fixed Size                   730760 bytes
Variable Size             109051904 bytes
Database Buffers           50331648 bytes
Redo Buffers                 811008 bytes
SQL>CREATE DATABASE adpdb
  LOGFILE group 1 ('/redolog1/adpdb/redolog1a.dbf','/redolog2/adpdb/redolog1b.dbf' ) SIZE 10M,
          group 2 ('/redolog1/adpdb/redolog2a.dbf','/redolog2/adpdb/redolog2b.dbf' ) SIZE 10M,
          group 3 ('/redolog1/adpdb/redolog3a.dbf','/redolog2/adpdb/redolog3b.dbf' ) SIZE 10M
  DATAFILE '/db1/adpdb/system.dbf' SIZE 200M
  CHARACTER SET WE8ISO8859P1
  national character set utf8
  EXTENT MANAGEMENT LOCAL
  undo tablespace ts_undo
    datafile '/db1/adpdb/undo.dbf'
    size 50M
  default temporary tablespace ts_temp
    tempfile '/db1/adpdb/temp01.dbf'
    size 50M autoextend on next 50M maxsize 300M;

If an ORA-01031: insufficient privileges is returned, that means most likely, that the current user is not in the dba group (on unix), or the ORA_DBA (windows).
If the init.ora file is not at its default location or has not been found with the pfile attribute, an ORA-01078: failure in processing system parameters and an LRM-00109: could not open parameter file '/appl/oracle/product/9.2.0.2/dbs/initadpdb.ora' error is issued.
The create database command also executes a file whose name is determined by the (hidden) init parameter _init_sql_file.
After the creation of the database, it can be mounted and opened for use.
Completing the work
Run

    * ?/rdbms/admin/catalog.sql
    * ?/rdbms/admin/catproc.sql and
    * ?/rdbms/admin/caths.sql

as sys.
catalog.sql calls, for example, catexp.sql which is a requirement for exp, or dbmsstdx.sql which is a requirement to create triggers.
The user system might also want to run ?/sqlplus/admin/pupbld.sql. pupbld.sql creates a table that allows to block someone from using sql plus.
The ? shortcut will be replaced with the value of $ORACLE_HOME by SQL PLUS.
These scripts do quite a bit of work:

    * They create predefined roles
    * They create the data dictionary, consisting of dynamic (aka v$) and static (aka dba_) views.
    * to be completed...

Of course, tablespaces and users and tables and so on must be created according to the use of the database.
Also check this link:
http://www.devarticles.com/c/a/Oracle/Creating-a-Database-in-Oracle-9i/

Hi,
Insufficient privileges means you donot have admin permissions:

Also try this :

There are various ways of creating databases in oracle 9i and you can set various options and on the otherhand you need to have proper privileges to create one though..

check here for more inputs and procedures to create a database ..

http://www.devarticles.com/c/a/Oracle/Creating-a-Database-in-Oracle-9i/2/

and

http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/create.htm#1003614

Check this link on how to overcome the issue with inadequate privileges -
http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c24privs.htm

hey Hi,

 

unable to connect to oracle 9i through .net using oledb provider

i am need of connectionstring what i hv to place

reply me as soon as possible

 

thanks in advance

shanti

 

the connection string looks like this

Provider=MSDAORA.1;Password=urPwd;User
ID=urUserId;Data Source=DSName;Persist Security Info=True

If you are facing issues with tsnnames and some other, feel free to check this thread which has the issues discussed..

http://www.xtremedotnettalk.com/showthread.php?t=73954

try this link

http://www.supinfo-projects.com/en/2006/db_creation_oracle_9i/

 By default, if you install Oracle and don't use the network configuration assistance you get a ORA_HOME without an SQLNET.ORA file. Without it, Oracle will not be able to use O/S authentication.

To solve this problem, simply create a file named sqlnet.ora in your ORA_HOME\network\network\admin directory and add to it the following line:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Save the file.

After this, try starting the instance using the same procedure described above and your O/S based logon should work just fine!