Oracle

IMP: ORA-12560 TNS:protocol adapter error

noreply@blogger.com (Sachin) — Mon, 15 Jun 2009 09:45:11 PDT

Problem: I am trying to import a schema dump on Oracle XE database on my Windows XP machine. Here is the command I am using

imp myuser file=mydump.dmp fromuser=myuser touser=mynewuser

I didn't get any problem when I use the same command for importing another schema on local database. Not sure why I am getting this error now.

IMP: ORA-12560 TNS:protocol adapter error

Solution: You need to specify the user and database name when you have more then one databases. So here is the command format you need to use for import.

imp myuser/mypass@MyDB file=mydump.dmp fromuser=myuser touser=mynewuser

You can try below command if you dont like to specify the password on the command itself.

imp myuser@MyDB file=mydump.dmp fromuser=myuser touser=mynewuser

Alternative for sending Multiple values to Oracle IN Clause

noreply@blogger.com (Sachin) — Fri, 13 Feb 2009 22:10:15 PST

Usually when connecting we have a query which has a
IN clause and it requires multiple values then you may need to go for creating a
String of all values and pass it.

Solution 1: You can try INSTR function of ORACLE
which can check for a value inside the comma separated String
value.

SELECT myid
FROM
mytable
WHERE INSTR(stringOfnumbers,','|| myid ||',') !=
0;

This solution may not be faster as it will not use
index for the myid column.

Solution 2: Create a PL/SQL function (say
str2table ) which converts comma separated String value to a table.
To store
the data in a table we need to create a data type as table type and then use the
same type as return type for our function.

create or replace type
myTableType as table of number;

create or replace function str2tbl(
p_str in varchar2 ) return myTableType

as
l_str long default
p_str || ',';

l_n
number;
l_data
myTableType := myTabletype();

begin

loop
l_n
:= instr( l_str, ','
);
exit
when (nvl(l_n,0) =
0);

l_data.extend;

l_data( l_data.count ) :=
ltrim(rtrim(substr(l_str,1,l_n-1)));

l_str := substr( l_str, l_n+1 );
end
loop;
return l_data;

end;

Here is how you can use above function

select * from all_users
where
user_id in ( select *
from THE ( select cast(
str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual )
)

Oracle function to covert comma separated value to table

noreply@blogger.com (Sachin) — Mon, 01 Sep 2008 21:39:08 PDT

Usually when connecting we have a query which has a IN clause and it requires multiple values then you may need to go for creating a String of all values and pass it.

Solution 1: You can try INSTR function of ORACLE which can check for a value inside the comma separated String value.

SELECT myid
  FROM mytable
  WHERE INSTR(stringOfnumbers,','|| myid ||',') != 0;

This solution may not be faster as it will not use index for the myid column.

Solution 2: Create a PL/SQL function  (say str2table ) which converts comma separated String value to a table.

To store the data in a table we need to create a data type as table type and then use the same type as return type for our function.
create or replace type myTableType as table of number;

create or replace function str2tbl( p_str in varchar2 ) return myTableType
    as
        l_str   long default p_str || ',';
        l_n        number;
        l_data    myTableType := myTabletype();
    begin
        loop
            l_n := instr( l_str, ',' );
            exit when (nvl(l_n,0) = 0);
           l_data.extend;
           l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
           l_str := substr( l_str, l_n+1 );
       end loop;
       return l_data;
   end;

 
Here is how you can use above function
 
select * from all_users
    where user_id in ( select *
      from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) )

Why can't I create a PreparedStatement that does select * from tab where id in (?, ?, ?, ...)?

noreply@blogger.com (Sachin) — Thu, 14 Aug 2008 14:18:10 PDT

The problem is that the RDBMS does not support bind parameters for the elements in the IN clause. This is a limitation of the database, not the driver.

What is JDBC?

noreply@blogger.com (Sachin) — Thu, 14 Aug 2008 14:17:55 PDT

JDBC technology is an API that lets you access virtually any tabular data source from the Java programming language. It provides cross-DBMS connectivity to a wide range of SQL databases, and now, with the new JDBC API, it also provides access to other tabular data sources, such as spreadsheets or flat files.

What is the difference between Primary key, Unique key, Sorrogate key?

noreply@blogger.com (Sachin) — Sun, 13 Jul 2008 08:56:49 PDT

Primary Key: A column in a table whose values uniquely identify the rows in the table. A primary key value cannot be NULL.
- There is only one Primary key for a table.
- Primary key can not contain null value.

Unique Key: Unique Keys are used to uniquely identify each row in an Oracle table. There can be one and only one row for each unique key value.
- There may be more then 1 unique keys for a table.
- It can contain a null value.

Surrogate Key: A system generated key with no business value. Usually implemented with database generated sequences.

What is the difference between cold and hot backup?

noreply@blogger.com (Sachin) — Sun, 13 Jul 2008 08:50:11 PDT

Cold Backup- We can take the Backup while DB(eg. Oracle) is down.
Hot Backup-We can take the Backup while DB(eg. Oracle) is running.

Cold backup is a physical backup. During a cold backup the database is closed and not available to users. All files of the database are copied (image copy). The datafiles do not change during the copy so the database is in sync upon restore.

Used when:
- Service level allows for some down time for backup

Hot backup is a physical backup. In a hot backup the database remains open and available to users. All files of the database are copied (image copy). There may be changes to the database as the copy is made and so all log files of changes being made during the backup must be saved too. Upon a restore, the changes in the log files are reapplied to bring the database in sync.

Used when:
- A full backup of a database is needed
- Service level allows no down time for the backup

What is Life of SQL Statement?

noreply@blogger.com (Sachin) — Sun, 13 Jul 2008 08:49:59 PDT

Step 1: Oracle Create a Cursor
For every SQL, first a cursor is created (cursor creation can occur implicitly, or explicitly by declaring a cursor).

Step 2: Oracle Parse SQL Statement

A SQL statement is parsed only if an identical SQL statement does not exist in the library cache (shared pool-System Global Area). In this case, a new shared SQL area is allocated and the statement is parsed (hard parse), otherwise existing stored information in library cache is used (soft parse).

Hard Parsing is the process of

Translating a SQL statement and verify the syntax.
Checking data dictionary to check table and column definitions
Acquiring parse locks on required objects so that their definitions do not change during the statementâ€™s parsing
Checking privileges to access referenced schema objects
Determining the optimal execution plan for the statement
Loading it into a shared SQL area
For distributed statements, routing all or part of the statement to remote nodes that contain referenced data

Stage 3: Describe Results (FOR SELECT STATEMENT ONLY)

The describe phase is used to determine the characteristics (datatypes, lengths, and names) of a queryâ€™s result.

Stage 4: Defining Output (FOR SELECT STATEMENT ONLY)

Specify the location, size, and datatype of variables defined to receive each fetched value. Oracle performs datatype conversion if necessary.

Stage 5: Bind Any Variables

At this point, Oracle needs values for any variables listed in the statement;

for example, In SQL statement â€œSELECT * FROM EMP WHERE EMPNO = p_empnoâ€, Oracle needs a value for p_empno.

This process is called binding variables. A program must specify the location (memory address) where the value can be found.

Stage 6: Execute the Statement

At last, Statement is executed.

Oracle checks if the data it needs for the query are already in the buffer cache. If not, it reads the data off the disk into the buffer cache.
The record(s) that are changed are locked i.e. in case of DELETE or UPDATE statement. Also, before and after images describing the changes are written to the redo log buffer and the rollback segments. The original block receives a pointer to the rollback segment. Then, the data is changed.

For some statements you can specify a number of executions to be performed. This is called array processing. Given n number of executions, the bind and define locations are assumed to be the beginning of an array of size n.

Stage 7: Parallelize the Statement

When using the parallel query option, Oracle can parallelize queries and certain DDL operations. Parallelization causes multiple query servers to perform the work of the query so that the query can complete faster.

Stage 8: Fetch Rows of a Query Result (FOR SELECT STATEMENT ONLY)

Rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result, until the last row has been fetched

How to compile Invalid Objects in Oracle?

noreply@blogger.com (Sachin) — Fri, 11 Jul 2008 11:53:36 PDT

There are five ways to recompile invalid objects in Oracle.

1. DBMS_DDL.ALTER_COMPILE
2. DBMS_UTILITY.COMPILE_SCHEMA
3. UTL_RECOMP
4. UTLRP.SQL - Located: $ORACLE_HOME/rdbms/admin
5. Manually Recompile

Oracle Frequently Asked Questions

noreply@blogger.com (Sachin) — Sat, 10 May 2008 19:58:36 PDT

How to compile Invalid Objects in Oracle?

How to flush all data in temp tablespace?

Oracle temporary tablespace is full. How do we flush it and create more space?

How to flush all data in temp tablespace?

noreply@blogger.com (Sachin) — Fri, 11 Jul 2008 11:53:18 PDT

Following steps will flush all the data .

1. Create a new temporary tablespace (with the different name e.g. tmp_temp) in the database

2. Now alter all users to use that temporary tablespace (tmp_temp). Use ALTER USER statement.

3. Now drop the earlier temporary tablesapace.

4. Now recreate the temporary tablespace with same name as step 3.
5. Now alter all users to use temporary tablespace. Use ALTER USER statement.

Oracle temporary tablespace is full. How do we flush it and create more space?

noreply@blogger.com (Sachin) — Fri, 11 Jul 2008 11:52:46 PDT

A "Temporary" Tablespace will likely always appear FULL. This is because in a temporary tablespace segments are not allocated, dropped, and re-allocated as they used to be. The segments are merely reused so we do not have the overhead of constant allocation/deallocation. Most of the time they will actually appear full.

If you are getting failed to extend error. Then they are actually in use and can not be reused. Then we should be concerned with either of two things:

1. Do we need more space on temporary tablespace?
2. Why we are using so much space?

You can use the script to find out detail on temp tablespace allocation:

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;