Oracle SQL to Check the Database / Instance Name to Which the Current Session Is Connected using a Non DBA user

There are couple of options to find out the database name for the current session. If you are a DBA a privileged user/session you can query any data dictionary views. But it is not the same case for non-dba user session.

Here are the options.

Data Pump Expdp Impdp Metrics=Y Undocumented Parameter

This is an undocumented parameter for the data pump. The Matrics=y option will log the list of objects exported or imported in each category and the time taken for each category objects. All the details will be logged in the logfile. See an example below.

$ expdp dumpfile=tab_exp.dmp logfile=tab_exp.log schemas=sthomas metrics=Y directory=exp_dir

Invoke Data Pump EXPDP IMPDP export / import Using SQL Developer

Using SQL Developer 4, you can initiate the data pump export or import job. It has export wizard under the menu view -> DBA. Using this you don’t need to aware about the data pump options to invoke a data pump job. All those data pump options can be invoked through the wizard. Here I will show you an example for one export job.

Data Pump Impdp show=y Replaced with SQLFILE Option

Most of the legacy mode options will work in the data pump with its corresponding options. In legacy mode (imp utility) we have show=y option to spool the content of the dump file into sql scripts without doing the actual import. The same command can be used in data pump impdp also but it will get replaced with sqlfile option.

See one example below.

Create File or Modify the Existing UNIX/LINUX File With Old / New Timestamp

Use touch command to change the timestamp of a file to newer or older one. Also, this command can be used to copy the timestamp between files.

touch –t  [[CC]YY]MMDDhhmm [.SS] filename

where          MM - The month of the year [01-12].

           DD - The day of the month [01-31].

Unix Shell Script to Find out Files Created within x Hours Without Using mmin / cmin for Solaris

HW=3 # Modify this variable value. In this example it will find out the files created within last 3 hours.
a=$( date +%H )
if [ $a -gt $HW ]; then
    a=$(( $a - $HW ))
else if [ $a -lt $HW ]; then
    a=$(( $a + 24 - $HW ))

Replace SQL Null Values with SQLPLUS Set Null Command

You can use the SQL NVL function to handle the null values in the query. Second option is SQLPLUS set null feature to replace the null values with user defined values.

See an example here.
We have a table DEPT with some of the HOD column values are null. 

Create a Case Sensitive Table in Oracle / Table Name in Lower Case

In the same oracle schema you can create 2 tables with same name one is in small case and second one with upper case. By default all the DML, DDL command applicable to the table name with upper case. If you wanted to access the table with lower case you have to specify the table name with double quote (“)

See some examples here.

How to Check the Number of Concurrent Users in the Oracle Database Dynamically?

There are two ways or two dynamic views to check this -v$license and v$session

The sessions_current column from the v$license gives the current count of the users in the database. This count will not have the oracle by default connection whereas it has only the application user connections are on live as of now.

Fix ORA-01927: cannot REVOKE privileges you did not grant

Error Description:
Revoke privilege operation/SQL command failed with following error message
ORA-01927: cannot REVOKE privileges you did not grant

Solution Description:
As the error message explains the privilege is not granted by the user which is trying to revoke.

Compress/zip Sqlplus Spool Output Automatically with Sql Execution Using UNIX Pipe

We generally use the oracle sqlplus spooling feature to log the output of the sql commands. If the output of the SQL command is too big in size, in such cases better to zip/compress the spool file. Again the available free space in the file system is too low to make the zip after creating the spool file is bit risky. Sometimes there will not be room for hold the actual and zip file during the compress process.

SQLPLUS: Create Spool File with DB Schema Name and Current Time and Date/Sysdate

Sometimes it is better to give database/schema and timestamp for the spool files which get created from the sqlplus prompt. If you are keeping all the spool files in single directory this method will be useful to pick up the right spool file out from all files.

Here is the method to spool a file with database name, schema name and current timestamp.

Rename Oracle 9i, 10g, 11g Datafile with Blank Space/White Space/Special/New line Characters

There are chances of mistake in life and the same kind of mistakes can happen in the DBA life also. The mistake on the production database is not advisable. J But anyway, there are chances of adding special characters by mistake in the data file name while adding the datafile to the tablespace. You can rename the datafile using following steps.

Here in this example I am adding one datafile with special character and new line character.

How to Append Command Outputs To Oracle SQLPLUS Spool File

Generally we use sqlplus spool command to record the sql command outputs to a file. It will record the command outputs between spool on and spool off. Generally once you done with the spool off command the output file will be closed from the further logging.

Oracle 10g onwards one more option added to the spool command to append the sql command to an existing file.

Fix ORA-14400: inserted partition key does not map to any partition

Problem description:

Insert to a partitioned table from the application is getting failed with following error message.

java.sql.SQLException: ORA-14400: inserted partition key does not map to any partition

Solution Description

First of you have to check whether in your partitioned table has a default partition.

How to Check Oracle 9i, 10g, 11g Scheduler Jobs Status

Oracle 9i and above you can use following query from the DBA_JOBS view to get the details like description, next execution, last successful execution etc.

       JOB SUBSTR(WHAT,1,45)                             LAST_DATE           NEXT_DATE           NEXT_SEC B
---------- --------------------------------------------- ------------------- ------------------- -------- -
        41 pk_cost.p_start_cost_batch;                   2014-01-30 11:35:30 2014-01-30 11:55:30 11:55:30 N
        48 DBMS_JOB.NEXT_DATE ( 41,to_date(sysdate + 5/4 2014-01-29 23:44:17 2014-01-30 23:44:17 23:44:17 N

dba topics. Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google