IMP: ORA-12560 TNS:protocol adapter error

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

How to flush all data in temp tablespace?

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?

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;