How To See Contents Of A Stored Procedure In Oracle?


Ever found yourself in a situation when you need to see the code/content of a Stored Procedure in Oracle? We need to check it for troubleshooting & database optimisation.

So, below is how to do it? 😉 Make sure to use set lines 500 & set pages 500 before executing this query, so as to fit the output in a more readable format.

SELECT line, text FROM dba_source WHERE owner = 'SHASHANK' AND name = 'MY_SP' AND type = 'PROCEDURE' ORDER BY line;
Advertisements

How To Clone An Oracle User


At times we need to add a new Oracle database user with just the same roles/rights on an existing user. There can be any number of reasons to add this user 😉 Most common use is to give the same role/rights/privileges to other user(s) from production database to a test database. Anyway, below are the steps that we take to clone an Oracle user 🙂

I don’t just write down the steps. My aim is to explain the things in an easy to understand language so that it doesn’t become a “just-another-copy-paste” blog.

Our first task will be to identify which user we want to clone. Its done by issuing below query. Make sure to issue set long 99999 before running any query. This will enable full output on your screen.

SELECT DBMS_METADATA.GET_GRANTED_DDL('USER', 'SHASHANK') FROM DUAL;

It will show you how my existing ID was created.

CREATE USER "SHASHANK" IDENTIFIED BY password;

Now we will run a few queries to see what all roles have been assigned to my ID SHASHANK.

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SHASHANK') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SHASHANK') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SHASHANK') FROM DUAL;

Now that we have all the roles & privileges for the user that we have to clone, lets start with creating a clone user SHASHANK_ADM. We will create a script called user_clone.sql & execute it. Just make sure to follow the correct / or \ depending upon your OS. Windows uses \ & Nix* uses /.

Below are the contents of my script. You can see that this is nothing but the output of above queries 😉 I have just replaced the user name with the new user.

CREATE USER "SHASHANK_ADM" IDENTIFIED BY password
GRANT CREATE TRIGGER TO "SHASHANK_ADM";
GRANT CREATE PROCEDURE TO "SHASHANK_ADM";
GRANT CREATE DATABASE LINK TO "SHASHANK_ADM";
GRANT CREATE SEQUENCE TO "SHASHANK_ADM";
GRANT CREATE VIEW TO "SHASHANK_ADM";
GRANT CREATE TABLE TO "SHASHANK_ADM";
GRANT UNLIMITED TABLESPACE TO "SHASHANK_ADM";
GRANT CREATE SESSION TO "SHASHANK_ADM";
GRANT "CONNECT" TO "SHASHANK_ADM";
GRANT "RESOURCE" TO "SHASHANK_ADM";
GRANT "SELECT_CATALOG_ROLE" TO "SHASHANK_ADM";
ALTER USER "SHASHANK_ADM" DEFAULT ROLE ALL;

Then execute this script : –

@/path_to_user_clone.sql

or

@\path_to_user_clone.sql

That’s it 🙂 We now have a clone user SHASHANK_ADM with just the same rights as user SHASHANK. Hope, this was useful & easy to understand.

Create a database in Oracle11g on Linux (RHEL6)


In the previous tutorial I explained how to Install Oracle 11g Database on a Linux machine. In this tutorial, I will explain how to create a new database. Last time we had created & configured a default database (orcl) that comes with Oracle. This time we will create our own database & then we can create tables there. Let’s start with this tutorial 🙂 Make sure you have at least 2GB of free space where your Oracle is installed.

Declare new shell variable.

This will be the name of your new database. In this tutorial, I chose the name db_new as I had run out of nice names 😉 Please note that Oracle accepts database names of maximum 8 characters. So don’t go for longer names. Open your bash shell or terminal & issue : – ORACLE_SID=db_new (make sure you use your own name that you have chosen for your database). Then issue : – export ORACLE_SID. This will export ORACLE_SID variable to your profile. It also means, your Oracle will now run targeting this database.

Create a directory for your database.

Issue this command: – mkdir -p $ORACLE_BASE/oradata/db_new. This is where control & data-files for your database will be kept. Refer to previous post to know about ORACLE_BASE & HOME.

Create an Initialisation Parameter file.

This parameter file or pfile determines the database name, location of control files & a variety of parameters. Its a text file, so easiest way to create it is to copy it from init.ora file found in $ORACLE_HOME/dbs location. Copy it to the same location & give it the name initdb_new.ora (name of your database, just make sure you prefix init as it’s an initialisation file). Now edit with the contents shown below. Make sure to comment the lines mentioned in screenshot (open the image in new tab if you find text too small) because we are preparing a minimal file. Anything extra will result into failure. Be extra cautions about the control files location. Control files are the files that determine the number & location of database data files. Data files are actual files what store the actual data.

pflie contents.

pflie contents.

Create Server Parameter File.

Server parameter file or spfile is a binary file based on our initialisation parameter file(pfile) & can’t be read using a text editor. This is what Oracle reads before mounting the database. So, start Oracle by issuing sqlplus /nolog command. And then connect using your credentials as sysdba (for eg. sqlplus / as sysdba). This will connect you to an idle instance. From this point, we can create our spfile or server parameter file. Issue command : – create spfile from pfile. This will create an spfile. Now armed with spfile, database can now be started with nomount stage. Nomount stage is where Oracle reads the spfile & starts the Oracle instance. Issue startup nomount command to do this.

spfile created & database mounted to nomount stage.

spfile created & database mounted to nomount stage.

Issue Create Database script.

Now we can execute a script that will create our database. This is the most important step, so be extra cautious here. This is the step that got me exhausted the most 😦 I failed almost 8-10 times going past this. Potential pitfalls are : – lack of adequate storage space for data-files, wrong tablespace names in pfile, specifying inadequate size to various parameters, etc. I suggest following the values strictly that are mentioned in screenshot, if you are a novice in Oracle. Grab the script from my GitHub gist or type it as shown below.

Database creation script executed.

Database creation script executed.

Mount the database.

Now issue alter database mount command. Mount is the stage where Oracle read the contents on control-files to know about size, number & location of data-files. Your database is now mounted.

Open the database.

Now issue alter database open command to open the database. This is the stage where Oracles reads from & writes to actual database files & database becomes ready for transactions.

Run Oracle scripts to build database dictionary views.

This is a time-taking step (script #2), so its better to execute these scripts & then go for a cup of tea or coffee or some snacks 😉

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql

scripts executed1 scripts executed

Test the database.

Now that database has been created, we can create our tables here. You can also test it by querying database dictionary views. Below are two such queries.

querying database dictionary view

querying database dictionary view

So, we have successfully created our new database to play with. In next tutorial, I will explain you how to back-up & restore a database 🙂 See you later 😉

Installing Oracle 11g Database on Linux (RHEL6)


Installing, setting-up & administering Oracle database is one of the most overwhelming things I have ever come across during my short career as a Linux Admin. While it is somewhat easier on a Windows platform, it a relatively tough nut to crack on Linux servers. A lot of tweakings need to be done 😦 But with a careful approach, installing Oracle 11g can become a cake-walk 😉 In this tutorial I will show you how to install it on an RHEL 6.5 Server. You are free to choose any similar Linux distribution, but a few things do vary. So, be careful 🙂 Let’s start with the tutorial. I assume, you have downloaded Oracle 11g zip file & extracted to hard-disk.

Lab Description : –

  • OS – Red Hat Enterprise Linux 6.5 64-bit
  • RAM – 3GB
  • Free Space on /opt – 25Gb
  • Oracle Database version – 11.2.0(11g)

Edit Kernel parameters.

Do it as root. Edit /etc/sysctl.conf file (always take backup of configuration files before editing them). Append with contents shown in the screenshot. I want you to type it manually & not paste, so that you get the idea what you are putting in. Once you have appended the file, save it & run the command sysctl -p to reload kernel parameters. If these values are incorrectly set, installer will throw an error & will show you what was the expected value. So, you can correct those values & continue with installation.

Sysctl kernel parameters

Sysctl kernel parameters

Edit /etc/security/limits.conf file.

Append the file with below contents.

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1027
oracle hard nproc 65536

Create required groups & oracle user.

Create oinstall & dba groups & oracle user.

groupadd oinstall groupadd dba useradd -g oinstall -G dba oracle

Login as oracle user. Issue su - oracle to login as oracle user.

Run the Installer.

Go to the location where you extracted the tarball. It must be Oracle11g/database directory. You will find runInstaller.sh script here. Run it. It will open the installer GUI.

Installer Location

Installer Location

Installation Screen

Installation Screen

Click Next & you will be shown a warning message. Click Yes as its just a warning that you have not entered any email-address. You won’t need it anyway 😉

Install2

Click Next & then Skip Software Updates. Of Course, this is optional. Choose accordingly.

Install3

Click Next & proceed with selecting Create & Configure a database, since this is your first installation. I assume it to be on a test server or at home. This database can act as guide to create other databases, so I prefer using this option if this is your first ever experience of Oracle installation.

Install4

Click Next & choose Desktop Class as this allows for a minimal set-up.

Install5

Choosing Installation Location & Database Name.

This is very important step. Pay extra care here. I am explaining what needs to be done here. Oracle base is where your Oracle is installed. Choose a partition with at-least 12GB free space. While, it can be moved to any directory later on, I recommend choosing your partition wisely to simplify the things. Next 2 options will be dynamically changed based on option 1. Leave other fields as default. Then type in Administrator password. Do remember that Oracle doesn’t accept @ in passwords. Confirm it & Click Next.

Install6

If the password chosen is weak, it will display a warning message. Click Yes to continue.

Install7

Now choose an Inventory directory. You may leave it as default & then click Next.

Install8

Now the step that taught me a few things 😉 If kernel parameters were incorrectly set (step 1) & pre-required packages were not installed, you will see the error message shown below. Thank God, Oracle is intelligent enough to tell (refer to the screenshot below) us what should be the values in /etc/sysctl.conf 🙂 If you run into this issue, make sure to set the correct values & also to install required packages. I could have told this in the beginning, but I didn’t, since resolving errors is a good habit, rather than not getting any error 😉 It helps in learning. Once the dependencies are resolved, click Next.

Install9

The next screen shows the confirmation about the stuff we selected in the beginning. At this point, if your are satisfied with the parameters like installation directory, database name etc, you can click Install to begin installation process.

Install10

Below screenshots show you the installation progress. If you are patient enough, you can sit & watch the proceedings 😉 If you’re not, go get a cup of coffee 🙂 It will take some time.

Install11

Install12

Just before installation completion, you might see a warning message as seen below. It tells you that, to use a database, you must set ORACLE_SID to database name. I will cover Database creation part in next post. As of now, open your bash shell & issue :

ORACLE_SID=orcl export ORACLE_SID echo $ORACLE_SID

Install13

Execute Oracle Scripts. Now its time to run 2 scripts as root as shown in the below screenshots. Click Ok here & execute scripts.

Install17

Install15

Install16

That’s it. You will see below screen after successful completion of installation. Take a screenshot of the screen so as to have information about the web-console. If your memory is sharp, you may skip it 😉

Install14

So, you can see we have successfully installed Oracle 11g on our Linux server. Now, before you can start using Oracle, you need to do one last thing. Edit your .bash_profile file & add below text. Skip what’s already there. Make sure to enter your Oracle installation directory for ORACLE_BASE & HOME. Below is where mine was installed.

PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/opt/oracledb/u01/app/oracle
export ORACLE_HOME=/opt/oracledb/u01/app/oracle/product/11.2.0/db_home1
export LD_LIBRARY_PATH=$ORACLE_HOME/lob
PATH=$PATH:$ORACLE_HOME/bin

After doing this, save this file & then issue sql / as sysdba command. It will launch SQL > prompt telling you are connected to an Oracle Database (orcl). If these things are confusing, then don’t worry. Next tutorial explains all these things in detail along with how to create a new database 🙂 See you there 😉