Database Admin Commands

Use the following commands to test the connection and configurations. 

• Check the listener status
• lsnrctl status <Alias_FROM_listener_Or_TNSnames_ORA>
• iSQL command to access Postgres database.
• isql <alias_name> -v

Unix - Command Execution Status Check

 #!/bin/bash

mvn deploy

STATUS=$?

if [ $STATUS -eq 0 ]; then

echo "Deployment Successful"

else

echo "Deployment Failed"

fi

DATABASE - DROP OBJECTS SCRIPT

 BEGIN

for i in (select 'drop ' || object_type || ' SCHEMA_OWNER.' || object_name as stmt

from all_objects

where object_type in ('PACKAGE') and owner = 'SCHEMA_OWNER') loop

execute immediate i.stmt;

end loop;

end;

/

BEGIN

for i in (select 'drop ' || object_type || ' SCHEMA_OWNER.' || object_name || ' Cascade constraints purge' as stmt

from all_objects

where object_type in ('TABLE') and owner = 'SCHEMA_OWNER') loop

execute immediate i.stmt;

end loop;

END;

/

BEGIN

for i in (select 'drop ' || object_type || ' SCHEMA_OWNER.' || object_name || ' force' as stmt

from all_objects

where object_type in ('TYPE') and owner = 'SCHEMA_OWNER' ) loop

execute immediate i.stmt;

end loop;

end;

/

BEGIN

for i in (select 'drop ' || object_type || ' SCHEMA_OWNER.' || object_name  as stmt

from all_objects

where object_type in ('SEQUENCE') and owner = 'SCHEMA_OWNER' ) loop

execute immediate i.stmt;

end loop;

END;

/

Convert PEM file to PPK file

We cant use PEM file to access servers using PUTTY.  You need to convert the PEM file to PPK and then you can use the PPK file to connect to unix machine using PUTTY. 

Use the following steps to convert the PEM files to PPK file.

• Download the PEM file 
• Open the PuttyGen app
• Select Type of key to generate option at the bottom as RSA.
• Select Load
• Select the downloaded PEM file. 
• Putty gen will generate both PPK and Public Key. 
• Download the ppk using Save private key option.
• Copy the public key content from the top window and save it as public key. 
• Use Putty and add ppk file under Auth option. 
• Connect to Server. 

-- Happy Learning

ORACLE DBA

QUERY TO GET SESSION DETAILS

SELECT * -- SID, SERIAL#, STATUS, SERVER , USERNAME
FROM V$SESSION
WHERE SID in
(
'43',
'58',
'197',
'223'
);

QUERY TO GET LONG RUNNING SESSIONS

select * from v$lock where type='TX' and lmode>0;


QUERY TO GET SID  ON A SPECIFIC DB OBJECT

select * from V$ACCESS where OBJECT='TABLE_NAME';


COMMAND TO KILL  A SESSION

ALTER SYSTEM KILL SESSION '58,29498';


COMMAND TO GET LONG RUNNING QUERIES
select sess.sid, sess.username, sql_text
from v$sqlarea sqlarea, v$session sess
where sess.sql_hash_value = sqlarea.hash_value
and   sess.sql_address    = sqlarea.address
and   sess.username is not null;


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

Delete Duplicate records.

delete 
from exchange_rate f
where  f.RATE_DATE = TRUNC(SYSDATE-53)
 AND exchange_rate_id not in (
  select min(exchange_rate_id)
  from   exchange_rate s
  where  f.FROM_CURRENCY = s.FROM_CURRENCY
  and    f.TO_CURRENCY = s.TO_CURRENCY
  and    f.RATE_DATE = s.RATE_DATE
);

Check the existing duplicate records:

select * from exchange_rate
where  (FROM_CURRENCY, TO_CURRENCY,RATE_DATE) in (
  select FROM_CURRENCY, TO_CURRENCY,RATE_DATE
  from   exchange_rate
  WHERE RATE_DATE > TRUNC(SYSDATE-4)
  group  by FROM_CURRENCY, TO_CURRENCY,RATE_DATE
  having count(*) > 1
);