Oracle: August 2012

Thursday, August 16, 2012

ORA-12162: TNS:net service name is incorrectly specified

oracle@oradev801:/opt/oracle INT$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 16 16:57:18 2012

ERROR:

ORA-12162: TNS:net service name is incorrectly specified

Enter user-name:

Strange!!

I thought it is a problem with the TNS entry, but after doing some research I came to know that it was a problem with ORACLE_SID, May be ORACLE_SID is not properly set or it was not exported.

oracle@oradev801:/opt/oracle INT$ export ORACLE_SID=XXXXXX

oracle@oradev801:/opt/oracle INT$ echo $ORACLE_SID

XXXXXX

oracle@oradev801:/opt/oracle INT$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 16 16:57:18 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

XXXXXX

On WINDOWS: set ORACLE_SID=XXXXXX

Wednesday, August 8, 2012

ORA-12906, DROPPING DEFAULT TEMPORARY TABLESPACE

First find the Default temporary tablespace in your database

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL>

SQL > select property_value from database_properties Where property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE

--------------------------------------------------------------------------------

TEMP

Check the size of TEMP tablespace:

SQL > SELECT tablespace_name, file_name, bytes/1024/1024/1024 GB FROM dba_temp_files WHERE tablespace_name like 'TEMP%';

TABLESPACE_NAME FILE_NAME GB

-------------------------- -------------------------------- ------------

TEMP /opt/oracle/oradata/temp01.dbf 6

For Oracle 8 and above, the following query will return all users and their SIDs which are doing a sort:

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;

To see USED and FREE space of it:

SQL> select SUM(bytes_used)/1024/1024/1024 GBused, SUM(bytes_free)/1024/1024/1024 GBfree from v$temp_space_header;

GBUSED GBFREE

------------ ---------

1.25 4.75

Now, try to drop the Default temp tablespace

SQL> DROP TABLESPACE temp;

DROP TABLESPACE temp

ERROR at line 1:

ORA-12906: cannot drop default temporary tablespace

In order to drop and recreate the default temp tablepsace first thing is to create another temporary tablespace and make that tablespace as default tablespace for the database and then drop the old default tablespace(TEMP)

SQL> CREATE TEMPORARY TABLESPACE temp_new TEMPFILE '/opt/oracle/oradata/temp_new01.dbf' size 5G reuse AUTOEXTEND ON NEXT 1M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;

Database altered.

Now drop the TEMP tablespace

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Monday, August 6, 2012

Find the delay of standby from primary

Here is a quick way to find the current timestamp from the current SCN.

http://www.chandu208.blogspot.com/2012/03/finding-current-scn-of-database.html

On primary:

SQL> select to_char(CURRENT_SCN) from v$database;

TO_CHAR(CURRENT_SCN)

————————————

12345678909

SQL> select scn_to_timestamp(48133107062) from dual;

SCN_TO_TIMESTAMP(12345678909)

—————————————————

19-JUN-11 08.40.27.000000000 AM

SQL>

And do the same on standby database, then find the difference in time.

If you set the delay parameter then find it in the parameter file

Thanks !!

Thursday, August 2, 2012

import only one table ROWS

TASK : I want to import 1 schema table rows from a full or full schema dump

SCHEMA: Chandra

Table : Demo

Dumpfile : dummy.dmp

Directory = TEST

$ impdp dumpfile=dummy.dmp directory=TEST content=data_only tables=DEMO logfile=impdp_demo.log

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 14:59:52 2012

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39002: invalid operation

ORA-39166: Object SYS.DEMO was not found.

REASON : Here we are running this import as sysdba user so oracle will find and import the sys.table rows , since it is not found thrown an error above

$ impdp dumpfile=dummy.dmp directory=TEST content=DATA_ONLY tables=DEMO logfile=impdp_demo.log schemas=chandra

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 15:00:46 2012

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDI-00010: multiple job modes requested, schema and tables

REASON : I tried to import table rows by using both SCHEMAS at the same time

$ impdp dumpfile=dummy.dmp directory=TEST content=DATA_ONLY tables=DEMO logfile=impdp_demo.log

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 15:03:50 2012

Username: Chandra

Password : *********

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_TABLE_02": /******** AS SYSDBA dumpfile=chandra_0731.dmp directory=EXPORT_DIR content=data_only tables=demo logfile=impdp_0802.log

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "CHANDRA"."DEMO" 692.9 KB 15700 rows

Job "SYS"."SYS_IMPORT_TABLE_02" successfully completed at 15:03:58

Here it searched for Chandra.demo table and imported rows

(or)

impdp dumpfile=dummy.dmp directory=TEST content=DATA_ONLY tables=chandra.DEMO logfile=impdp_demo.log

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 15:03:50 2012

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_TABLE_02": /******** AS SYSDBA dumpfile=chandra_0731.dmp directory=TEST content=data_only tables=chandra.demo logfile=impdp_0802.log

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "CHANDRA"."DEMO" 692.9 KB 15700 rows

Job "SYS"."SYS_IMPORT_TABLE_02" successfully completed at 15:03:58

NOTE : schema “CHANDRA” from which you are importing should have ‘imp_full_database’ role granted, also READ and WRITE permissions on TEST Directory

Thank you !!! :-)