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.
Oracle SQL to Check the Database / Instance Name to Which the Current Session Is Connected using a Non DBA user
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 ))
else
a=00
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
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.
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.
SQL>SELECT JOB, SUBSTR(WHAT,1,45), LAST_DATE, NEXT_DATE, NEXT_SEC, BROKEN FROM DBA_JOBS;
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
