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
shutdown immediate
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;
/
No comments :
Post a Comment