Search Results


Thursday, February 26, 2015

SQL Notes and Tips

SQL Tips and Notes

Some SQL queries and useful sql statements for ADF/SOA Developers or at least I use them so often.

Database size


Find out table sizes
select table_name,(num_rows*avg_row_len)/(1024*1024) MB from user_tables order by 2 desc;

Gather Schema status
EXEC DBMS_STATS.gather_schema_stats('DEV_SOAINFRA');

Find out the schema sizes
SELECT *
FROM
( SELECT
   c.tablespace_name,
   ROUND(a.bytes/1048576,2)                    MB_Allocated,
   ROUND(b.bytes/1048576,2)                    MB_Free,
   ROUND((a.bytes-b.bytes)/1048576,2)          MB_Used,
   ROUND(b.bytes/a.bytes * 100,2)              tot_Pct_Free,
   ROUND((a.bytes-b.bytes)/a.bytes,2) * 100    tot_Pct_Used
 FROM
   ( SELECT
       tablespace_name,
       SUM(a.bytes) bytes
     FROM
       sys.DBA_DATA_FILES a
     GROUP BY
       tablespace_name
   ) a,
   ( SELECT
       a.tablespace_name,
       NVL(SUM(b.bytes),0) bytes
     FROM
       sys.DBA_DATA_FILES a,
       sys.DBA_FREE_SPACE b
     WHERE
       a.tablespace_name = b.tablespace_name (+)
       AND a.file_id = b.file_id (+)
     GROUP BY
       a.tablespace_name
   ) b,
   sys.DBA_TABLESPACES c
 WHERE
   a.tablespace_name = b.tablespace_name(+)
   AND a.tablespace_name = c.tablespace_name
 )
WHERE
 tot_Pct_Used >=0
ORDER BY
 tablespace_name;


Database Back and Restore


Cold Backup
Find out files:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;

Shutdown the Database
alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate


Change DB password

select username, account_status from dba_users;
alter user dev_soainfra identified by welcome1;
alter user test2_soainfra account unlock;
ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED;

change password for all expired users

set hea off
select 'alter user '||username||' identified by welcome1;' from dba_users where account_status like 'EXPIRED%';
select 'alter user '||username||' account unlock;' from dba_users where account_status like 'LOCK%';

-- To find out the profiles
select profile, username from dba_users;

-- To find out LOGIN ATTEMPT LIMIT for the profiles
select LIMIT, RESOURCE_NAME from dba_profiles
where PROFILE = (select profile
                 from dba_users
                where username = 'DEV_SOAINFRA');

Logging in SQL

drop table test_log;
create table test_log (a number(10), b varchar2(4000));
create sequence test_seq;

create or replace PROCEDURE test_logger(p_text IN varchar2)
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
 insert into test_log values (test_seq.nextval, p_text);
 commit;
END;
/

CALL TEST_LOGGER('HELLO');
SELECT * FROM TEST_LOG ORDER BY A DESC;
TRUNCATE TABLE TEST_LOG;

Redo Log generation rate

SELECT TRUNC(NEXT_TIME,'HH24'),ROUND(SUM(BLOCKS*BLOCK_SIZE/1024/1024/1024),2) REDO_GB
FROM GV$ARCHIVED_LOG
where next_time > sysdate -4
GROUP BY TRUNC(NEXT_TIME,'HH24')
ORDER BY TRUNC(NEXT_TIME,'HH24');

Creating procedure objects to use with SOA

CREATE TYPE security_challenge_obj
AS
 OBJECT
 (user_name varchar2(254),
  max_val_attempts number(3),
  val_attempts_period varchar2(3),
  activity_type varchar2(30));   
 /

set serveroutput on;

DECLARE
 P_SECURITY_CHALLENGE AR_SOA.SECURITY_CHALLENGE_OBJ;
 P_PERMIT_VALIDATION VARCHAR2(200);
 P_STATUS VARCHAR2(200);
 P_ERROR_MESSAGE VARCHAR2(200);
 p_count number;
BEGIN
 -- Modify the code to initialize the variable
 p_security_challenge := ar_soa.SECURITY_CHALLENGE_OBJ(null,null,null,null);
 P_SECURITY_CHALLENGE.user_name := 'helasdfasdfasdfasdfasdfasdfasdfasdflo';
 P_SECURITY_CHALLENGE.max_val_attempts := '3';
 P_SECURITY_CHALLENGE.val_attempts_period := '24';
 P_SECURITY_CHALLENGE.activity_type := 'accounter';

  USER_VALIDATION_PKG.CHECK_VALIDATION_ATTEMPTS(
   P_SECURITY_CHALLENGE => P_SECURITY_CHALLENGE,
   P_PERMIT_VALIDATION => P_PERMIT_VALIDATION,
   p_validation_count => p_count

  );

  --:P_PERMIT_VALIDATION := P_PERMIT_VALIDATION;

DBMS_OUTPUT.PUT_LINE('P_STATUS = ' || P_STATUS);

  --:P_STATUS := P_STATUS;

DBMS_OUTPUT.PUT_LINE('P_ERROR_MESSAGE = ' || P_ERROR_MESSAGE);
DBMS_OUTPUT.PUT_LINE('P_count = ' || p_count);
DBMS_OUTPUT.PUT_LINE('P_PERMIT_VALIDATION  = ' || P_PERMIT_VALIDATION);

--  :P_ERROR_MESSAGE := P_ERROR_MESSAGE;
END;
/

Find and Kill Table Locks

SELECT s.username dbuser,owner || '.' || object_name "Object",
s.osuser,
s.PROGRAM,
lk.sid,
lk.type lock_type,
decode(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.lmode)) lockmode,
decode(request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.request)) requestmode
FROM v$lock lk,
v$session s,
dba_objects ao
WHERE lk.lmode > 1
AND object_name LIKE 'USER%'
AND s.username IS NOT NULL
AND lk.sid = s.sid
AND ao.object_id(+) = lk.id1;

select session_id from dba_dml_locks where name = 'USER_REMINDER_STATUS' ;

select sid, serial# from v$session where sid in ( 9);

alter system kill session '9,10679';

Monitor JDBC connections

select
   inst_id,
   machine,
   program,
   username,
   osuser,
   failed_over,
   to_char(logon_time,'DD/MM/YYYY HH:MI'),
   count(*) COUNT
from gv$session
where username IN ('PERF_SOAINFRA','AR_SOA_RW','PERF_MDS')
group by inst_id,
   machine,
   program,
   username,
   osuser,
   failed_over,
   to_char(logon_time,'DD/MM/YYYY HH:MI')
order by inst_id,
   machine,
   program,
   username,
   osuser,
   failed_over,
   to_char(logon_time,'DD/MM/YYYY HH:MI')

  INST_ID MACHINE                   PROGRAM                        USERNAME        OSUSER          FAI  COUNT
---------- ------------------------- ------------------------------ --------------- --------------- --- ------
        1 fang                      JDBC Thin Client               AR_SOA_RW       oracle          NO       2
        1 feral.ref.london.02.net   JDBC Thin Client               AR_SOA_RW       oracle          NO       2
        2 fang                      JDBC Thin Client               AR_SOA_RW       oracle          NO       2
        2 feral.ref.london.02.net   JDBC Thin Client               AR_SOA_RW       oracle          NO       2

         1 fang                      JDBC Thin Client               PERF_SOAINFRA   oracle          NO      29
        1 feral.ref.london.02.net   JDBC Thin Client               PERF_SOAINFRA   oracle          NO      29
        2 fang                      JDBC Thin Client               PERF_SOAINFRA   oracle          NO      28
        2 feral.ref.london.02.net   JDBC Thin Client               PERF_SOAINFRA   oracle          NO      29


Sleep Function using Java


CREATE OR REPLACE PROCEDURE Sleep_fn(sec1 NUMBER)
AS LANGUAGE JAVA
NAME 'java.lang.Thread.sleep(long)';
/

Find Database processes limit

select resource_name, current_utilization, max_utilization, limit_value
   from v$resource_limit
   where resource_name in ('sessions', 'processes');


Purge AQ table


DECLARE
 po_t dbms_aqadm.aq$_purge_options_t;
BEGIN
  dbms_aqadm.purge_queue_table('XXCUST_TABLE', NULL, po_t);
END;
/

Post a Comment