Categories
database oracle tips n trick

Query Locked Object in Oracle

The below query can be used for determining the if any object locked in an oracle database.

select   c.owner,  c.object_name,  c.object_type,  b.sid,  b.serial#,
b.status, b.osuser, b.machine
from
v$locked_object a , v$session b, dba_objects c
where
b.sid = a.session_id
and a.object_id = c.object_id;

And if you need to kill any session you can use the below query to do the same.

alter system kill session sid,serial;
Categories
database Linux oracle tips n trick

ORA-00600 (its good feature, don’t be scared :D)

When I trying to extract data using Oracle Data Integrator, suddenly my laptop go ‘blue screen’, and when startup again, my oracle database doesn’ open, here is situation, and how to solve it:

[blockquote border_color=”#00a9e0″ bg_color=”#f2f2f2″ effect=”none”]


SQL> select name from v$database;

NAME
———
JTEK

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[503], [3], [56], [], [], [], [], [], [], []
SQL> shutdown;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size 2176088 bytes
Variable Size 1023413160 bytes
Database Buffers 536870912 bytes
Redo Buffers 7548928 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[503], [3], [56], [], [], [], [], [], [], []
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[503], [3], [56], [], [], [], [], [], [], []
SQL> show parameter control_files;

NAME TYPE VALUE
———————————— ———– ——————————
control_files string C:\ORACLE-EE\ORADATA\JTEK\CONT
ROL01.CTL, C:\ORACLE-EE\FLASH_
RECOVERY_AREA\JTEK\CONTROL02.C
TL
SQL> select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=
b.group# and b.status=’CURRENT’
2 ;

MEMBER
——————————————————————————–

GROUP# STATUS
———- —————-
C:\ORACLE-EE\ORADATA\JTEK\REDO02.LOG
2 CURRENT
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size 2176088 bytes
Variable Size 1023413160 bytes
Database Buffers 536870912 bytes
Redo Buffers 7548928 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 5038568 generated at 09/26/2013 22:20:59 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE-EE\FLASH_RECOVERY_AREA\JTEK\ARCHIVELOG\2013_09_26\O1_MF_1_503_%U_.ARC
ORA-00280: change 5038568 for thread 1 is in sequence #503
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\ORACLE-EE\ORADATA\JTEK\REDO02.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL>

[/blockquote]

Categories
database PostgreSQL tips n trick

PostgreSQL – Dump Data as Insert Statement

Sometimes we need to export postgreSQL data using sql insert statement for simplest backup – restore mechanism, to do this you can execute bellow command:

[blockquote border_color=”#00a9e0″ bg_color=”#f2f2f2″ effect=”fadein”]

$ pg_dump -U user_name -h localhost -t table_name --data-only --column-inserts db_name > data_dump.sql

[/blockquote]

Categories
Linux tips n trick

Using tar gz in linux

Following are 5 commonly used options – c, z, f, v, x

[blockquote border_color=”#00a9e0″ bg_color=”#f2f2f2″ effect=”none”]

  • c = create a new tar file
  • v = verbose , display file to compress or uncompress
  • f = create the tar file with filename provided as the argument
  • z = use gzip to zip it
  • x = extract file

[/blockquote]

[title text=”Compress/zip” icon=”fa-compress” icon_color=”green”][/title]

$ tar -cvzf example-jtek.tar.gz example-jtek

[title text=”Extract/unzip” icon=”fa-expand” icon_color=”red”][/title]

$ tar -xvzf example-jtek.tar.gz