Tablespace Name miss match for Export - Import

Oracle offers no parameter to specify a different tablespace to import data into. Objects will be re-created in the tablespace they were originally exported from. One can alter this behaviour by following one of these procedures:

Pre-create the table(s) in the correct tablespace:

• Import the dump file using the INDEXFILE= option. imp /@ file= indexfile=index.sql full=y
• Edit the indexfile. Remove remarks and specify the correct tablespaces.
• Run this indexfile against your database, this will create the required tables in the appropriate tablespace. sqlplus /@ @index.sql
• Import the table(s) with the IGNORE=Y option. imp /@ file= fromuser= touser= ignore=y

Change the default tablespace for the user:

• Revoke the "UNLIMITED TABLESPACE" privilege from the user
• Revoke the user's quota from the tablespace from where the object was exported. This forces the import utility to create tables in the user's default tablespace.
• Make the tablespace to which you want to import the default tablespace for the user
• Import the table

Monitor your sequences

Never let your sequence exceed your column precision. Here is simple way to generate a check..

select ds.sequence_name,
dtc.table_name,
dtc.column_name,
ds.last_number,
dtc.data_precision,
round(ds.last_number/power(10,dtc.data_precision),2)*100||'%' percent_used
from all_sequences ds,
all_tab_columns dtc
where ds.sequence_name like 'SEQ_'||trim(upper(dtc.column_name))
order by percent_used desc, sequence_name;

Avoid Use of Sequence in the CASE, DECODE, NVL

While using the NEXTVAL function in a select query (CASE) , inside a false condition, it increases the current value of the sequence.

Create Sequence TEST_SEQ Start with 1 increment by 1;

SELECT TEST_SEQ.CURRVAL FROM DUAL
------------
2

SELECT CASE WHEN 1>2 THEN TEST_SEQ.NEXTVAL ELSE TEST_SEQ.CURRVAL END SEQ_NO FROM DUAL;
------------
3
In second select statement, NEXTVAL is used inside a FALSE condition

The process of evaluating a sequence reference in an SQL statement is somewhat unique. It's almost like Oracle treats them as substitution variables on a per-row basis, prior to any other expression/condition evaluations, evaluating .NEXTVAL first (if referenced), then .CURRVAL, then the other conditions/expressions.

This is in part due to the need to return the same sequence.nextval value within a row, regardless of the number of sequence.nextval references. To do this, it probably goes ahead and evaluates .NEXTVAL even if it is not "needed" (due to a false condition in a CASE, DECODE, etc. statement). Interestingly that's how sequences work, and its documented that way. The SQL REFERENCE manual (Chapter 3 "Pseudo columns", section for "sequences") states "Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence only once: For each row returned by the outer query block of a SELECT statement."
so, if the query block references NEXTVAL and returns a row, the sequence is incremented. doesn't matter if you use it in a case, decode, nvl or anything else.

Suggested to use the function which will not be direct reference to the sequence and therefore it will not increment for the FALSE condition. GettestSequence custom function will get the next value from the SEQUENCE.

SELECT CASE WHEN 1>2 THEN GettestSequence('INSTRUMENT') ELSE c14_ads.TEST_SEQ.CURRVAL END SEQ_NO FROM DUAL;

How to get Age in Years and Months? (Using MONTHS_BETWEEN)

Simple query to get age in Years and Months..

select trunc(months_between(<(today)>,<(birth Date)>)/12) years,
FLOOR(months_between(<(today)>,<(birth Date)>)-12*trunc(months_between(<(today)>,<(birth Date)>)/12)) months
from dual;

So for e.g. If somebody was born on 01/01/2009 and you want to know the age then run the following

select trunc(months_between(SYSDATE,to_date('01/01/2009','mm/dd/yyyy'))/12) years,
FLOOR(months_between(SYSDATE,to_date('01/01/2009','mm/dd/yyyy'))-12*trunc(months_between(SYSDATE,to_date('01/01/2009','mm/dd/yyyy'))/12)) months
from dual;

Use of Bind Variables in Dynamic SQLs

One need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL. It, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. We need to avoid the hard parse when it is submitted:

"create or replace procedure dsal(p_empno in number)
as
begin
execute immediate 'update emp set sal = sal*2 where empno = '||p_empno; commit;
end;

The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:
create or replace procedure dsal(p_empno in number) as
begin
execute immediate 'update emp set sal = sal*2 where empno = :x' using p_empno;
commit;
end;
/"

Use Bulk Collect/ Object Type for batch Processing

Whenever there is a need to fetch large volumes of data BULK Collect with Limits is one the best ways. The most important thing to remember when you learn about and start to take advantage of features such as BULK COLLECT is that there is no free lunch. There is almost always a trade-off to be made somewhere. The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "run faster but consume more memory." Fortunately, PL/SQL makes it easy for developers to control the amount of memory used in a BULK COLLECT operation by using the LIMIT clause.

Enable Oracle to perform hash joins

In cases where a very small table is being joined to a large table, the Oracle hash join will often dramatically speed-up the query. Hash joins are far faster than nested loop joins in certain cases, often in cases where your SQL is joining a large table to a small table. However, in a production database with very large tables, it is not always easy to get your database to invoke hash joins without increasing the RAM regions that control hash joins. For large tables, hash joins requires lots of RAM.