Saturday, June 27, 2009

PL/SQL Developer- Setting Path in Preferences

What is PL/SQL Developer?
Well, PL/SQL developer is like an IDE for ORACLE. You can connect PL/SQL Developer to ORACLE database and then create and compile procedures and function, create users, views,tables and much more that you do with ORACLE.

Setting the path
When you use it for the first time, you need to set the path in the Preferences. Set the following paths:
Oracle Home: This path contains the path of your database:
For example - C:\oracle\product\10.2.0\db_1
OCI Library: This contains the path of the file oci.dll in the BIN directory.
C:\oracle\product\10.2.0\db_1\BIN\oci.dll

Reopen PL/SQL developer so that the changes take place effectively.
Thats it, happy developing !!!

Friday, June 26, 2009

ORACLE- Tablespaces and datafiles





Tablespaces:
It is a unit of storage where ur database is kept. There may be one or more tablespaces depending on the size of ur database.
Datafiles:
Each tablespace comprises of one or more datafiles.To increase tablespace, you need to add datafiles to the tablespace.

Types of tablespaces:
---- SYSTEM tablespace contains the data dictionary.
----USERS tablespace is used to store user data(table, indexes etc)
----The SYSAUX tablespace provides a single location for all non-essential database metadata. In the past the schema objects to support many database features were located in the SYSTEM tablespace. These have now been moved to the SYSAUX tablespace. As a result the SYSTEM tablespace is less cluttered and suffers less contention.
----TEMPORARY tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory space will be allocated in a temporary tablespace for doing the sort operation
----UNDO tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces.

ORACLE- Import and Export

Lets say u have a huge database created in Machine A. Now you want a replica of the database in machine B at a different location.. What r u gonna do? Copy the SQL CREATE-INSERT statements, then execute these. What if u miss out on an insert statement???
Well, to perform these tasks, ORACLE provides import and export commands.

If you want to create a replica of ur database, create a dump(.dmp) using exp command
exp file=(location where dump is to be created) Log=(location where log is to be created) owner=(user whose dump is being exported)

Now to import this dump into a new user:

imp file=(location where dump is kept) fromuser=(user whose dump is being exported) touser=(user where dump is being imported)

There u go... The database gets created successfully.

Saturday, June 20, 2009

Checking 32-bit/64-bit ORACLE

Have u ever installed 32-bit ORACLE on a 64-bit system by mistake nd u never realized it. Well, use this command to know whether 32 bit or 64 bit ORACLE is installed on ur system.

SELECT DISTINCT ADDRESS FROM v$sql where rownum<2;

If the address is 16 characters long like 00000000345BA23E, uve got 64 bit oracle on ur system.

But if the address is 8 characters long like 098765EA, then its 32 bit ORACLE.

Tuesday, June 16, 2009

ORACLE Installation

Have you ever tried to install ORACLE on Linux. Well, its a bit of struggle if you don't know the correct settings.Believe me, I had a hard time installing ORACLE for the first time on Linux. But I think, you guys shouldn't waste time. So, here we go:

First of all you need to have the required packages and RPMs. You can find these easily from manual.
After installing all the packages, begin with the following steps:

Step 1: Add/Modify the following lines to /etc/sysctl.conf

kernel.shmmni =4096
kernel.sem=250 32000 100 128
fs.file_max=65536
net.ip_local_port_range= 1024 65000
net.core.rmem_default=1048576
net.core.rmem_max=1048576
net.core.wmem_default=262144
net.core.wmem_max=262144
net.ipv4.tcp_wmem=262144 262144 262144
net.ipv4.tcp_rmem=4194304 4194304 4194304

Step 2: #sysctl -p
So that changes take place effectively.

Step 3: Create oracle user and groups
#groupadd oinstall
#groupadd dba
#useradd -g oinstall -a dba oracle -- This server as username.
#passwd

Step 4: Create required directories
#mkdir -p /home/oracle
#
chown -R oracle:oinstall /home/oracle
#chmod -R 755 /home/oracle

Step 5: Add the following lines to /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16348
oracle soft nofile 1024
oracle hard nofile 65536

Step 6: Add the following lines to /etc/passwd/login
session required pam_limits.so

Step 7: Add following to /etc/profile
if[$USER="oracle"]; then
ulimit -u 16384
ulimit -n 65536

Step 8: Add/modify /home/oracle/.bash_profile
umask 022
export ORACLE_BASE=/home/oracle
export ORACLE_SID=orcl
export ORACLE_HOME= $ORACLE_HOME/oracle/product/10.2.0/db_1
LD_LIBRARY_PATH=$ORACLE_HOME/bin:$PATH
export PATH

Step 9:
xhost +
xclock

Step 10: Run the GUI Installer
./runInstaller

There you go, now just do a couple of nexts and ull have oracle installed... CHEERS :)