How to create Database Manually in oracle

CREATING   MANUAL  DATABASE

Create the following directories in a folder where u want to create database:

--          c:\oracle\admin\dbname

--          c:\oracle\oradata\dbname

--          c:\oracle\admin\dbname\pfile

--          c:\oracle\admin\dbname\cdump

--          c:\oracle\admin\dbname\bdump

--          c:\oracle\admin\dbname\udump

--          c:\oracle\admin\dbname\create

            Copy the default pfile from C:oracle\ora90\admin\sample\pfile\initsmpl.ora to                                            --          c:\oracle\admin\dbname\pfile\initdbname.ora

            Edit the pfile c:\oracle\admin\dbname\pfile\initdbname.ora as per the following:

            (check if the parameters are there in the file. If not add them manually.

           

--          db_name = dbname

--          control_files = ("C:\oracle\oradata\dbname\control01.ctl")

--          background_dump_dest = c:\oracle\admin\dbname\bdump

--          core_dump_dest = c:\oracle\admin\dbname\cdump

--          user_dump_dest = c:\oracle\admin\dbname\udump

--          remote_login_passwordfile = exclusive

--          undo_management=AUTO

--          undo_tablespace=undotbs1

Copy the initdbname.ora from ur database to

C:oracle\ora90\DATABASE\initdbname.ora.

Creating the Instance of your Database

Note: The following command is to be executed from the Cmd prompt

oradim -new -sid dbname  -startmode a –pfile c:\oracle\admin\dbname\pfile\initdbname.ora

Note : Execute this command from the direcory where you should create this file i.e. the default directory is %ORACLE_HOME%\database  directory.

--          cd C:\oracle\ora92\bin

--          orapwd.exe file=C:\oracle\ora92\database\PWDdbname.ora password=change_on_install entries=5

Getting connected.

Go to Command Prompt and set the Oracle_SID to the value same as the DB_NAME

            set oracle_sid=dbname

            sqlplusw /nolog

            connect sys/pasword as sysdba

(IT WILL CONNECT US TO AN IDLE INSTANCE)

Creating / Starting Database

startup nomount

Type the following command in a notepad & run it on SQL prompt.

CREATE DATABASE dbname

MAXINSTANCES 1

MAXLOGHISTORY 1

MAXLOGFILES 5

MAXLOGMEMBERS 3

MAXDATAFILES 100

DATAFILE 'C:\oracle\oradata\dbname\system01.dbf' SIZE 250M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'C:\oracle\oradata\dbname\temp01.dbf' SIZE 40M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED

UNDO TABLESPACE UNDOTBS1 DATAFILE 'C:\oracle\oradata\dbname\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED

LOGFILE GROUP 1 ('C:\oracle\oradata\dbname\redo01.log') SIZE 102400K,

GROUP 2 ('C:\oracle\oradata\dbname\redo02.log') SIZE 102400K,

GROUP 3 ('C:\oracle\oradata\dbname\redo03.log') SIZE 102400K;

After creating the database run the following three scripts

To create the Data Dictionary Views

            @c:\oracle\ora92\rdbms\admin\catalog.sql;

To run the scripts for PL/SQL Procedures

@c:\ORACLE\ora92\rdbms\admin\catproc.sql;

connect system/manager

@c:\oracle\ora92\sqlplus\admin\pupbld.sql;

Read More

Differences Between Anonymous Blocks and Subprograms

1. Anonymous is unnamed plsql block

A stored procedure or a named block is a pl/sql block.

2. Anonymous cannot save in database,

oracle stores pl/sql block in the database.

3. We cannot call Anonymous

blocks. We can recall named block

Whenever program require it.

4 Anonymous Block cannot allow any mode of parameter.

Named block accepts the mode of

Parameter like in,inout,out. 

5 Anonymous blocks are to be compiled each time when we

Require it.

But once we compile a named pl/sql block it is

Permanently stored as p-code after compilation in the

Shared pool of the system global area.

6. Anonymous block: Starts with

                DECLARE or BEGIN.

                Named block: Starts with Header Block.

Like name of block, type of block, parameter.

 Named Block has NAME: like PROCEDURE, FUNCTION, and PACKAGES

7.The anonymous block statement is an executable statement

That can contain PL/SQL control statements and SQL

Statements. It can be used to implement procedural logic in

a scripting language.

named block or stored procedure is a pl/sql block that

oracle stores in the database and can be called by name

From any application examples are function, procedure and packages,etc..

8.Anonymous block are created on client and subprograms are stored on server. It is possible to call subprograms from anonymous block.

9.Anonymous blocks do not return values.

Subprograms called functions must return values.

10. Anonymous blocks cannot take parameters.

Subprograms can take parameters..

Syntax:-


Whenever you will execute the procedure nth time would appear the following message 
and not showing any output anonymous block completed.

Anonymous Blocks
DECLARE(Optional)
Variables,Cursors,etc.
BEGIN(Mandatory)
SQL and PL/SQL cOMMANDS
EXCEPTION(Optional)
Exceptional_Handling actions;
END;(Mandatory)

11. example  Anonymous blocks

DECLARE

l_message

VARCHAR2 (100) := 'Hello World!';

BEGIN

DBMS_OUTPUT.put_line (l_message);

END;

 SUBPROGRAMS PROCEDURE

Create [Or Replace ] procedure name [parameters] is|as
(Mandatory)
Variables,Cursors,etc(Optional)
BEGIN   (Mandatory)
SQL & plsql STATEMENTS;
EXCEPTION(Optional)
When Exception_handling actions;
END[NAME] ; (Mandatory)

Let us take one example of Named Block given below.

CREATE OR REPLACE FUNCTION FINDAREA(LEN IN NUMBER, WID IN NUMBER)

RETURN NUMBER

AS VAREA NUMBER;

BEGIN

VAREA := LEN * WID;

RETURN VAREA;

END;

SQL>  SELECT FIND_AREA(25,15) AREA FROM DUAL;

AREA

375

               

Read More

About Me

Thank you for your interest in my profile! Here is some detail about myself, in case you wondered.

I am an Oracle Trainer  working for Aptech-in Mumbai. In addition to this web blog, I have published five  

other blogs. I have also written hemaplsql.blogspot.in.

I’am currently teaching oracle 12c administration in mumbai, now-I started working with the Java  in 2016.

Feel free to contact me at hemalatatech@gmail.com, whether it's in relation to this site, its contents, hiring 

my services or indeed anything to do with Oracle. 

All comments are gratefully received.

Read More