What is Oracle BBED Tool? How to make it Available in Oracle 11g?

BBED: Block Browser and Edit Tool

It is an Oracle tool to check the internal structure of the Oracle blocks. You have to use this tool very carefully as it can damage the Oracle blocks as well as the Oracle database too. This tool allows editing the blocks of the datafiles and it overrides the oracle access.

How to Recover / Restore / Recreate Dropped Dual Table in Oracle? ORA-01775: looping chain of synonyms

DUAL table is owned by SYS schema and it can be dropped only by a DBA user. As a DBA you are not supposed to drop the DUAL table as it will affect the database badly. In case if the DUAL table gets dropped by mistake, you can follow below steps to recover the DUAL table and the database operations.

Difference between BIGFILE and SMALLFILE tablespace, Advantages. How to Create and Resize Bigfile Tablespace?

There are 2 kinds of tablespace allowed. One is traditional tablespace which is known as SMALLFILE tablespace and the second one is the BIGFILE tablespace. For bigfile tablespace we should consider with ASM or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.

Select from System Package DBMS_METADATA Failed: ORA-25153: Temporary Tablespace is Empty, ORA-06512

Error Description:

Select from system packages getting failed with following error

SQL> select dbms_metadata.get_ddl ('TABLE', 'DUAL','SYS') from dual;

ERROR:

ORA-25153: Temporary Tablespace is Empty

ORA-06512: at "SYS.DBMS_METADATA", line 4018

Insert, Delete, Truncate Operations with Oracle Dual Table. Just Not Fun

Don’t play with dual table in an active database. Really it is not fun.

Insert Row in Dual

If you insert a row into dual, it will not list in the select command. But if you try to create a dummy table from the dual it will show the actual content of the dual table.

How to Query / Generate Multiple Rows from Oracle DUAL Table?

Method 1: Using Connect By Clause

Number Series from DUAL

SQL> SELECT rownum

FROM DUAL

CONNECT BY rownum <= 10;

Siebel Database Configuration Failed: C102: The Username / Password Combination.00000: cannot be validated at this

Error Description:

Siebel database configuration failed with following error message.

  

Enter a valid ODBC (Open Database Connectivity) Data Source Name to access the

Siebel Database connection. Default value is Siebel_DSN.

How to Check What is Getting / Being Audited in Oracle Database?

Below mentioned 3 data dictionary views can be used to fetch the details of the auditing.

1. dba_obj_audit_opts: Data dictionary view will give the details of auditing options on all objects. user_obj_audit_opts view will provide the details of the auditing enabled

Will Oracle Unique Key Constraint Allow / Accept more than One NULL Value?

What is Unique Key Constraint?

Unique key is one of the 5 integrity constraints. Oracle Unique key can be the combination of one or more than one (upto 32) columns. This key ensures the data uniqueness for the columns specified in the key. Which means the same combination cannot be repeated for a specified unique key.

crontab Commands Fails in Linux: You (%s) are not allowed to use this program (crontab)

Error Description:

crontab –e, crontab –l commands are failing with following error.

$ crontab -l

You (brmsftp) are not allowed to use this program (crontab)

See crontab(1) for more information

$ crontab -e

STOP / Disable Oracle 11g ADDM, AWR using control_management_pack_access Parameter

You can stop the ADDM in Oracle 11g using control_management_pack_access initialization parameter. The values permitted for this parameter are

1. NONE – Both packs are not available
2. TUNING – Only tuning packs (SQL Tuning advisor, SQLAccess Advisor) are avilable

How to Change / Move Oracle Inventory Location to another Directory?

Step 1. Find out the oraInst.loc file location either using find command or opatch utility.

$ ./opatch lsinventory

Invoking OPatch 11.2.0.1.1

dbms_stats.gather_table_stats : ORA-00600: internal error code, arguments: [smboPut:fixedlen2]

Error Description:

dbms_stats.gather_table_stats command fails with ORA-00600: internal error code, arguments: [smboPut:fixedlen2]

SQL> exec dbms_stats.gather_table_stats (ownname=>'xxx', TABNAME=>’YYYY’, estimate_percent=>100,cascade=>TRUE);

Oracle SQL Query to Print / Display Calendar Using 11gR2 LISTAGG Function

The input for this query is the first date of the year. It will display the calendar. Interesting one.. Try this J

SQL Query:

How to Disable / Escape / Avoid / Stop & and && Variable Substitutions in Oracle SQL Plus

Difference between & and &&

‘&’ is used to substitute values for one time to the variable. So whenever you execute the sql with & it will prompt for the variable values.

Procedure to Apply and get OCP Certificate and Logo who Completed Exams Hands on Training

I have received my OCP certificate recentlyJ. I have successfully completed my OCP certification examinations in 2009. But I was not able to attend the Hand-on course during same year. Recently I had a chance to complete the hands-on course and I applied for OCP certificates.

Data Pump Impdp TRANSFORM option SEGMENT_ATTRIBUTES, STORAGE, OID and PCTSPACE examples

Data pump impdp has many options to transform the metadata during the import operation. Those are REMAP_DATAFILE, REMAP_SCHEMA, REMAP_TABLESPACE and the TRANSFORM.

How to Prepare awk Data Report? Example

I have a text file with following details. I wanted to create a simple report as formatted in the output. See a simple awk program to generate the report.

How to Check Which Child Directory is consuming More Space in Unix/Linux?

In many cases as a DBA or Unix admin you have to find out which directory is consuming more space. Probably in some directories consuming more space because of huge log files or some trace files.

Data Pump impdp remap_table Option to Change the Table Name During Import

This impdp parameter is used to rename the table during the import operation. These are the main activities can be performed by this parameter.

1. Rename the one or more tables into the same schema during import

Using vi, awk, sed - erase / remove / cut Few First , Last Characters in all lines of Linux file

I have a text file called dat.txt and it having some generic text characters in the first and last of every line. I wanted to remove these charcters. See different options by using vi, awk, sed.

Count & Remove Repeating / Duplicate lines in Linux/Unix file

I have a file called dat.txt with few lines. I wanted to count and remove the duplicate lines.

$ cat dat.txt

vi, awk, sed - Substitute / Replace Few First , Last Characters in all lines of Linux file

I have a text file called dat.txt and it having some text data which is highly confidential. I wanted to replace some few characters with x to make it invalid. See different options with vi, awk, sed.

Implement Delay or Sleep in Oracle PL SQL Scripts Using DBMS_LOCK.SLEEP?

Oracle provides a DBMS_LOCK.Sleep procedure to suspend the session for given period of time. The argument for the procedure is time in seconds. Seconds can be in decimals also.

How to Copy File without Changing Last Modified Date, Time stamp and ownership in Linux / Unix?

cp command provides an option –p for copying the file without changing the mode, ownership and timestamps.

--preserve option gives you the provision to choose the options from

cp , ln Commands to Create soft Links for all Files in Linux / Unix Directory

You can create soft links in Linux using cp and ln commands. Both commands will create soft links with –s option.

$ pwd

/home/oracle/test

How to Copy Only New / Modified / Changed Files in Unix / Linux?

There are two commands to perform this task – rsync and cp command with –u option. In both cases it will copy all the files in the initial run and only modified file in the subsequent run.

Truncate failed ORA-24005: Inappropriate utilities used to perform DDL on AQ table

Error Description:

Delete command on one table is working properly. But truncate command is failing with following error.

SQL> SELECT COUNT(*) FROM SCOTT.QII_TSKERR_CMSQTAB;

What is Data Pump Expdp SOURCE_EDITION and Impdp TARGET_EDITION Options? Examples

In Oracle 11gR2 you can create and keep different versions or editions of objects. The objects are limited to some set of objects which is not having the storage. You can see more details here with examples.Oracle Object Editions

How to Create Different Editions or Versions for Oracle Objects? Its Advantages

In oracle you can create different versions for the objects which are not requiring the storage. For example Table requires storage and View doesn’t require the storage. These are the types of the objects can be editionable.

Data Pump IMPDP failed: ORA-31655: no data or metadata objects selected for job and ORA-39154

Error Description:

I have a dump file which is taken from different database with DBA user. I am trying to import the same dump to different database with different user.  Data Pump import job failed with following error.

Oracle 11gR2 Impdp DATA_OPTIONS SKIP_CONSTRAINT_ERRORS: How to Skip Constraint Errors during Import?

DATA_OPTIONS parameter comes with impdp utility. By default this parameter is disabled during the import. You have to invoke it specifically to handle some special kind of data during the import operations.

RMAN Configure Backup Optimization ON / OFF : Features & Example

As the name indicates the feature is for optimizing the RMAN backups. When you turn on this backup optimization parameter, RMAN skips the backing up the files which has been already been backed up to the same device. The main benefit out of this is it will reduce the unwanted space usage.

Fix ORA-15097: cannot SHUTDOWN ASM instance with connected client (process %s)

Error Description:

ASM database instance shutdown failed with following error.

SQL> conn / as sysasm

Connected.

SQL> shut immediate;

Fix Oracle 11g Database Block Corruption? - Recovery Using RMAN Backup ORA-01578: ORACLE data block corrupted

Error Description:

Oracle database aler.log file shows following error. RMAN logs also reported the same error.

ORA-01578: ORACLE data block corrupted (file # 6, block # 881945)

ORA-01110: data file 6: '/u01/app/oracle/data/DEVQA/datafile/mgmt.dbf'

Add or Insert a Heading Extra Line after Every nth Line of File in Unix / Linux

Here is my input data file and see the output as per the requirements below.

cat inp.txt

clark            2450

king             5000

miller           1300

smith             800

How to Open and Save UNIX / Linux man Pages to vi Editor File?

Suppose if you wanted to print the Unix man pages, it has to be copied to a single file. Normally man pages displays page by page. You can easily open the man pages in vi editor using combination following commands.

How to Create a Fixed Huge Size File in Unix / Linux Using dd Command?

dd if=/dev/zero of=dummyfile bs=26214400 count=1

1+0 records in

1+0 records out

26214400 bytes (26 MB) copied, 0.063799 seconds, 411 MB/s

$ ls -ltr

Why Oracle DBA_TEMP_FILES view Bytes Column is NULL: How to Drop, Shrink Offline / Online Temp File

Some Facts on Oracle Tempfiles:

If the database tempfile is in offline status then the bytes column of the dba_temp_files will be null. If the file system of the temporary file is having some problem then the corresponding tempfile status will move to offline

Oracle 11g Database Startup Failed: /dev/shm and ORA-00845: MEMORY_TARGET not supported on this system

Error Description:

I have modified or increased the value for the parameter MEMORY_TARGET and then Oracle 11g database startup failed with following error.

SQL> startup

ORA-00845: MEMORY_TARGET not supported on this system

Datapump IMPDP Partition_Options = MERGE , DEPARTITION Examples

Partition_Options parameter is available with data pump impdp utility.  This parameter determines how the partitions available in the export dump are handled in the import operation.

Duplicate Command Failed: RMAN-05001: auxiliary filename %s conflicts with a file used by the target database

Error Description:

RMAN Duplicate database command failed with RMAN-05001 error message

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 21/10/2012 23:09:54
RMAN-05501: aborting duplication of target database

OC4J Connection Pool Error Message: java.sql.SQLException: ORA-01017: invalid username/password; logon denied

Error Description:

OC4J connection pool configuration testing to Oracle 11g database is giving following error.

OC4J Error message: Exception occurred testing connection. Exception: java.sql.SQLException: ORA-01017: invalid username/password; logon denied 

Fix ORA-02032: clustered tables cannot be used before the cluster index is built

Error Description:

Insert SQL statement fails with following error message.

SQL> insert into emp values (7369, 'smith', 'clerk', 7902,to_date('17-dec-1980', 'dd-mon-yyyy'), 800, null, 20);

Truncate Failed: ORA-03292: Table to be truncated is part of a cluster

Error Description:

Truncate table command failed with following error message.

SQL> TRUNCATE TABLE DEPT;

TRUNCATE TABLE DEPT

               *

Shell Script to Run RMAN Backup in Background with nohup

Step 1. Prepare a RMAN command file

vi bkup.cmd

run

Move Subpartition for an Interval Partitioned Table Failed: ORA-00600 [kkpod nextFrag]

Error Description:

Alter table move subpartition on an interval partitioned table is failed with following error. Also the session got disconnected.

Fix IMP-00013: only a DBA can import a file exported by another DBA

Error Description:

Import using imp utility is failing with following error.

Export file created by EXPORT:V11.02.00 via conventional path

IMP-00013: only a DBA can import a file exported by another DBA

IMP-00000: Import terminated unsuccessfully

Fix IMP-00058: ORACLE error 1950 encountered

Error Description:

Import using conventional imp utility is failing with following error.

IMP-00058: ORACLE error 1950 encountered

ORA-01950: no privileges on tablespace 'USERS'

Import terminated successfully with warnings.

Blogger Blogspot Page views Reset to Zero Today - Resolved

It is a surprise to me that my blogger page views are reset to zero today(15-Oct-2012) and a fresh counter started. This is the first time I am seeing this problem in my blogging life. As per the blogger support team it is a known issue.

Rail Radar: How to Live Locate Indian Railway Trains in Google Map!!

Indian railway launched live locator for the trains running on the Indian railway tracks with the help of the Google Maps. When you launch the web site you will get the Indian map with train indicators and you can zoom-in and zoom-out as like in Google Maps.

Index Rebuild Online Failed Oracle 11gR2: ORA-01450: maximum key length (3215) exceeded

Problem Description: Index creation or rebuild with online clause failed with following error in Oracle 11gR2.

ORA-00604: error occurred at recursive SQL level 1

ORA-01450: maximum key length (3215) exceeded

Fix Oracle 11g Client sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

Error Description:

Oracle 11g Client SQL* PLUS is failing with following error.

$ORACLE_HOME/bin/sqlplus
/opt/oracle/client_11_2/bin/sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

How to Find out / Check / Set Values for Hidden Underscore _ Parameters in Oracle?

Short Note on Hidden/Undocumented Parameters:

Undocumented parameters need to be tested in test database before implementing in production. Also, better to get support from oracle before going to production. Hidden parameters are for internal purpose of the oracle. You have to be very careful before implementing as they are very dangerous. It starts with ‘_’.

Validate Exception Queue: ORA-24017: cannot enable enqueue on QUEUE

Error Description:

exec DBMS_AQADM.START_QUEUE('AQ$_TEST_TABQ_E'); command is failing with following error.

SQL>  exec DBMS_AQADM.START_QUEUE('AQ$_TEST_TABQ_E');

BEGIN DBMS_AQADM.START_QUEUE('AQ$_TEST_TABQ_E'); END;

Data Pump EXPDP Error: ORA-39181: Only partial table data may be exported due to fine grain access control on

Error Description:

Data pump expdp log shows following error for some tables during the export.

ORA-39181: Only partial table data may be exported due to fine grain access control on "SCOTT"."CONTROL_ITEMS"

Solution Description:

This error occurs when a unprivileged user tries to export the tables which are enabled with Fine Grain Access Control policies. You can verify these kind of tables using the below query.

select * from dba_policies where OBJECT_OWNER='SCOTT' AND OBEJCT_NAME=’CONTROL_ITEMS’

Fix ORA-01157: cannot identify/lock data file # - see DBWR trace file ORA-01110: data file #: ORA-01136: specified size of file # (n blocks) is less than original size

Error Description: One of my applications is failing with following oracle error message.

"ORA-01157: cannot identify/lock data file 76 - see DBWR trace file ORA-01110: data file 76:

Oracle SQL Select Statement / Function to get Server Memory, CPU and Processor details

Once I got a requirement to fetch the server details. Initially I thought of login to each and every box and get the details. But if it is one or ten box it’s okay to get the details manually. But if you think about 100s of servers and also periodic activity,

ORA-27125: unable to create shared memory segment Linux-x86_64 Error: 2: No such file or directory

Error Description:

Database startup failed with following error after increasing the value for SGA_TARGET and SGA_MAX_SIZE
ORA-27125: unable to create shared memory segment

dbms_lob.write Failed with ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275

Error Description:

PLSQL Block with dbms_lob.write command is getting failed with following error. Command: dbms_lob.write (tempblob,length(tempname), 1,utl_raw.cast_to_raw (substr(tempname,1,length(tempname))));

ORA-06502: PL/SQL: numeric or value error: raw variable length too long: Convert Varchar2 to Blob with utl_raw.cast_to_raw

Error Description:

Oracle datatype conversion from VARCHAR2 to BLOB using the function utl_raw.cast_to_raw  is getting failed with following error.

SQL>  update bl_test set TR_PIC=utl_raw.cast_to_raw(TR_NAME) WHERE LENGTH(TR_NAME)>2000;

Convert Oracle Varchar2 column to Blob data type Using utl_raw.cast_to_raw – Example

Here is the example for normal Oracle VARCHAR2 datatype to BLOB database.

In this example EMP is my sample table and I trying to convert ENAME column to blob.

Fix ORA-19870 ORA-19863: device block size 4186112 is larger than max allowed: 1048576

Error Description:

RMAN Duplicate database command failed with following error message.

RMAN-00571: ==============================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ==

RMAN-00571: ==============================================

RMAN-03002: failure of Duplicate Db command at 07/20/2012 09:50:30

RMAN-05501: aborting duplication of target database

HISTTIMEFORMAT: How to See the Linux commands History with Date and Time?

Linux command prompt provides history of all commands which got executed under that login. If you give normal history command it will not show the date and time for the command execution. But in many cases you would need the date and time of the command execution which we can easily get it by formatting the HISTTIMEFORMAT environment variable.

Siebel Error SBL-ODC-00105 ORA- 24345 A Truncation or null fetch error occurred

Error Description:

Siebel application screen is throwing the following error while querying.

SBL-ODC-00105 ORA- 24345 A Truncation or null fetch error occurred

Oracle EM Alert: Critical:DBNAME - There have been n failed login attempts in the last x minutes: Identify the LOGIN failure with 1017 returncode

This is an Oracle EM alert which says the database has n unsuccessful login attempts in x minutes.

In order to identify the login details which are getting failed you should have enable the database auding especially enable the “connect audit”.

How to Perform Multiple Comparisons in Oracle SQL Query Using Select .. Case .. When .. Else

Oracle 10g R1 onwards the case .. when clause has been added to the select statement which can perform several comparisons in a single query. Prior to that we should have to use the DECODE function to achieve this goal.

ORA-04031: unable to allocate 2048024 bytes of shared memory: 11 g RAC Fix error

The following error is getting logged frequently in alert log and as per the metalink suggestion the SGA_TARGET has to be increased to fix the same. To increase the SGA_TARGET value it requires the database bounce. If you are running in Oracle RAC environment you can perform this change without full database outage. Please find the steps to change value instance by instance.

Error: ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")

How to Fix PLS-00201: identifier 'DBMS_AQ' must be declared

Error Description:

Procedure compilation failed with following error.

PLS-00201: identifier 'DBMS_AQ' must be declared

Solution Description

You have to grant the execute privilege on package DBMS_AQ to the particular user who owns the procedure.

How to Renew the Domain Registered From Blogger Settings – Domain Auto Renewal via Google Apps

The domain which is registered from the blogger is valid only for one year from the date of registration. You must have to renew the domain to retain your visitors. If you forget to renew your domain after a year, someone else can take the domain. If you miss the existing domain name it is very tough to achieve the same amount visitors with different domain name.

11g Deferred Segment: ORA-01950: no privileges on tablespace

Normally "ORA-01950: no privileges on tablespace" error occurs due to the no space quota allocated to the user on the particular tablespace. I wish to explain the difference in ORA-01950 error with deferred segment creation values with TRUE and FALSE. 

Fix ORA-14402: updating partition key column would cause a partition change

Error Description:

Application is getting following error while accessing the partitioned table.

The SQL Exception is: "ORA-14402: updating partition key column would cause a partition change

Normally this error happens after making the partitions to the tables.

How to Configure Tablespace / Monitoring Alert Emails in Oracle 11g OEM Rules

You can configure the monitoring alerts using Oracle EM.

Here I am going demonstrate OEM configuration for tablespace monitoring alert. 

Please see the steps. 

1. Login to OEM console with username (sysman) and password

How to Avoid / Remove Adsense Ads from Homepage / Archive Page in Blog

As per Google Adsense policy you are supposed to display 3 image and 3 links ads per page. But, if you set your blog home page with multiple items, normally the blogger will display ads for the first few posts and after the 3 ads it will show the empty slots in the homepage. This is applicable for the archive page with multiple items. The empty slots will give a bad look for your blog; moreover it is Adsense policy violation. You can get rid of this by implementing this sample code in your blog HTML.

Steps for RMAN Recovery from Old Incarnation without Catalog

Here is one example for RMAN recovery from old previous incarnation without catalog.

Step 1

Find out your control file backup file name from RC_BACKUP_FILES table with respect your DBID / Old incarnation.

Connect to RMAN catalog and run

RMAN> LIST INCARNATION OF DATABASE DEVDB01;

ORA-20021: database not set: Select From RC_BACKUP_FILES Table failed

Error Description

SQL> select distinct BS_TAG from rc_backup_files;

select distinct BS_TAG from rc_backup_files

*

ERROR at line 1:

ORA-20021: database not set

ORA-06512: at "RMAN.DBMS_RCVMAN", line 22314

ORA-06512: at "RMAN.RC_LBRECSETIMPL_T", line 58

How to Use ORADEBUG to Find out Trace file name

Facts on ORADEBUG

1. You have to connect as sysdba to use/execute the ORADEBUG
2. It can be executed from the SQLPlus prompt

How to Use ORADEBUG CURRENT_SQL to See Other Session SQL

Facts on ORADEBUG

1. You have to connect as sysdba to use/execute the ORADEBUG
2. It can be executed from the SQLPlus prompt

Example

Here I am giving you an example of ORADEBUG to check the SQL statement running on other session using a session with sysdba privilege.

FireFox: Uninstall / Disable / Remove Yahoo Axis Search Bar from Browser

Installation of the Yahoo Axis search bar on  FireFox browser is easy from the axis.yahoo.com. Yahoo Axis is not a normal browser like Google or Bing. This will install a permanent search bar on the browser like an ad-on. The search bar will be in the bottom left corner of the browser. This search bar stretch or pop-up when the cursor move on top it.

Internet Explorer: Uninstall / Disable / Remove Yahoo Axis Search Bar from IE Browser

Installation of the Yahoo Axis search bar on Internet Explorer (IE) browser is easy from the axis.yahoo.com. Yahoo Axis is not a normal browser like Google or Bing. This will install a permanent search bar on the browser like an ad-on.

Google Chrome: Uninstall / Disable / Remove Yahoo Axis Search Bar from Browser

Installation of the Yahoo Axis search bar on Google Chrome browser is easy from the axis.yahoo.com. Yahoo Axis is not a normal browser like Google or Bing. This will install a permanent search bar on the browser like an ad-on. The search bar will be in the bottom left corner of the browser. This search bar stretch or pop-up when the cursor move on top it.

Traffic after Registering Custom Domain for Blogger Blogspot - My Experience

It is always good to have a custom domain for your blogs. This will give better credibility to your blog. I wanted to share my experience on blog traffic after registering the custom domain.

Benefits of Registering Custom Domain

1. Search engine indexing time.

Since the domain is new to the search engine, it will take time to crawl and index all the pages in your blog again. Sometimes it may take weeks time to index the entire blog content.

Work Around for ORA-00600: [kkpo_rcinfo_defstg:objnotfound], [75312]

This error occurs because of the oracle bug. Most of the time it happens with the database with deferred_segment_creation=true and the error will be specific to a database object. The object id for the particular object will be the second argument in the ORA-00600 error. Object name can be identified from the trace file mentioned in the first ora-00600 error in the alert.log file.

Find out Maximum, Current Limit of Oracle Resources (Session, Process, Lock) using v$resource_limit

V$resource_limit dynamic view provides the details of resources like sessions, processes, locks etc. It has the initialization values for the resources, maximum limit reached after the last database startup and current utilization of the resource.

Purpose of ORAENV_ASK=NO / YES variable in Oracle

It is mandatory to set ORACLE_SID, ORACLE_HOME environments to connect to the oracle database. Oracle provides a script called oraenv to set or change these variables. ORAENV_ASK environment variable is used to make the oraenv (/usr/local/bin/oraenv) script to interactive or non-interactive.

If you set the parameter value as YES (default) it will ask for the values ORACLE_SID, ORACLE_HOME. If you set the value as NO it will take blank or the values which is set already for the ORACLE_ parameters.

Data Pump EXPDP IMPDP content=METADATA_ONLY to Extract the Skeleton of the Oracle Database

Purpose of Content Option: Using this parameter you can choose the export / import utility for taking the metadata/metadata&data/data_only in to the dump file. The one of the benefit of this option is you can create the similar kind of database/ schema without data using the option content=METADATA. i.e. you can easily create a skeleton of the source. The dump size will not be a big one and you can easily import into the target schema or database.

You can take the data only dump also by setting the parameter value as DATA_ONLY.

How to Check / Find out the Privileges and Roles for My Own Session Oracle

Most of the time, as a SQL programmer user might not be having the privilege to select the data dictionary views to check the permissions of the user sessions. In Oracle any of the users can check their own privileges using following commands after connecting to the SQLPLUS using their credentials.

a.  SELECT * FROM SESSION_ROLES;

Above query will give you which and all roles assigned to your user.

b.  SELECT * FROM SESSION_PRIVS;

This one will give you which and all privileges assigned to your user.

ORA-00059: maximum number of DB_FILES exceeded - Fix / Resolve Oracle Add Datafile Error

Error Description: Create tablespace, add data file command failed with following error.

SQL> CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/testdb/TEST01.dbf' size 50m;

CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/testdb/TEST01.dbf' size 50m

*

ERROR at line 1:

ORA-00059: maximum number of DB_FILES exceeded

ORA-28001: the password has expired - Permanent Fix for NOEXPIRY for Oracle User

Error Description: ORA-28001: the password has expired message is getting while connect to the user.

SQL> CONNECT TEST/TEST;

ERROR:

ORA-28001: the password has expired

Data pump IMPDP SKIP_UNUSABLE_INDEXES=Y/N Parameter Option Example

Purpose of this parameter is to skip the import of a table or a table partition with index in UNUSABLE state. Which means the table should be there in the database with an index in unusable state. This will reduce the unnecessary time for index creation which is in unusable state during the import. By default value for this parameter is ‘Y’. It will come into action when its value is N.

Example on Data Pump EXPDP IMPDP Parfile Option

Advantages of using parfile expdp impdp

1. You can hide the passwords from the command prompt by putting the password in the parfile. Otherwise if you mention the password in the command prompt, it will show up in the ps –ef command.
2. The length of the command which you can type in the Operating system command line is limited. You cannot type a very lengthy expdp / impdp command with more number of arguments. In this situation you can create a parameter file(parfile) and run with the datapump.

How to Schedule / Execute / Run an Oracle Procedure through DBMS_JOB?

1.   Create a PLSQL procedure

SQL>

create or replace procedure pr_dtl (a in varchar2) is

begin

dbms_output.put_line('Name:'||a);

end;

/

Procedure created.

Check Who is Accessing / Locking / Blocking the Oracle Session / Table

If the table is locked by some session in the database you cannot create index or make any changes to that particular table. You will end up with “ORA-00054 resource busy and acquired with NOWAIT specified” error message. You can find out which session is locking your targeted table by using following queries. You may have to kill (in case not needed) the session by following queries.

How to Run / Schedule a PLSQL Procedure In Shell Script and Crontab?

1. Create a PLSQL procedure

SQL>

create or replace procedure pr_dtl (a in varchar2) is

begin

dbms_output.put_line('Name:'||a);

end;

/

Procedure created.

How to Keep / Pin / Check PLSQL Objects in Buffer Cache / Memory Using DBMS_SHARED_POOL.KEEP

It will take time to load huge packages to the memory because the smaller objects needs to be moved out of shared pool to make a room for the new big plsql object. To avoid these kind of delays you can keep the PLSQL codes like procedure, function, etc permanently using the DBMS_SHARED_POOL system package. This package allows you to keep the sequences also in the shared pool which will avoid the sequence number missing situation.

Find / Check Hot/Frequently Accessed Objects in Oracle 10g 11g Database Using v$db_object_cache

V$DB_OBJECT_CACHE gives the details of database objects in the shared pool. It includes all objects like tables, indexes, plsql objects-procedure, function, package and etc. The execution column provides you the number of execution for each object.

Below mentioned EXAMPLE provide you the object type wise details of the object under APPUSER.

How to Set / Fix / Limit the Oracle 10g 11g Trace File Size

The size of the trace file in Oracle determined by the parameter is max_dump_file_size. If you set the value of this parameter to UNLIMITED the trace file will occupy the total available size in the file system if it is required. You can limit the value in session or system level by changing the value of the max_dump_file_size.

Script to Generate and Spool DDL / SQL statements for Tablespaces in Oracle Database

As a DBA sometimes we need to spool the scripts for the tablespace for creating the new databases. For that you can use DBMS_METADATA package to extract the DDL script for the tablespace easily.

Below mentioned script you can use for the same purpose.

Script to generate all tablespaces. 

Syntax to Find / Check / Change / Set Default Permanent Tablespace for Oracle 10g, 11g Database

Default Permanent tablespace Syntax

SQL> create tablespace default_tbs datafile '/u01/app/oracle/oradata/dbrepoinf/default_tbs.dbf' size 100m;

Tablespace created.

SQL> alter database default tablespace default_tbs;

Different methods to find out NLS Parameters from Oracle Database

1. Using SHOW PARAMETER

Connect / as sysdba

SQL> show parameter nls_

NAME                                 TYPE        VALUE

------------------------------------ ----------- -------------

nls_language                         string      AMERICAN

nls_territory                        string      AMERICA