Tips

--------------------------------------------------
Display a String Vertically

SELECT SUBSTR('tarek', ROWNUM, 1)
FROM all_objects
WHERE ROWNUM <= LENGTH(TRIM('tarek'))

Display Calendar of the Year

select lpad( Month, 20-(20-length(month))/2 ) month,
"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
from (select to_char(dt,'fmMonthfm YYYY') month,
CASE when to_char(dt,'fmMonthfm YYYY') like 'Dec%' AND to_char(dt+1,'iw') = '01' then '53'
when to_char(dt,'fmMonthfm YYYY') like 'Jan%' AND to_char(dt+1,'iw') = '53' then '.5'
else to_char(dt+1,'iw')
END week,
max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su",
max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo",
max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu",
max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We",
max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th",
max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr",
max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
from (select trunc(sysdate,'y')-1+rownum dt
from all_objects
where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y'))
group by to_char(dt,'fmMonthfm YYYY'), to_char( dt+1, 'iw' )
order by to_char(dt+1,'iw'))
order by to_date( month, 'Month YYYY' ), to_number(week)

Get Information on Current Queries

select t.tablespace_name as tablespace,us.username,s.status,s.sid,s.serial#,s.program,sql.sql_text,us.blocks,s.logon_time
from v$tempseg_usage us, v$session s, v$sqlarea sql, dba_tablespaces t
where t.contents = 'TEMPORARY' and
us.tablespace=t.tablespace_name and
us.session_addr = s.saddr and
s.sql_address = sql.address
order by blocks

Open Cursor Count

select substr(a.sid,1,10) sid,
substr(nvl(b.program,machine),1,30) program,
count(*)
from v$open_cursor a, v$session b
where a.saddr=b.saddr
group by substr(a.sid,1,10),
substr(nvl(b.program,machine),1,30)
order by 3 desc ;

Script to Find User Information and More

SELECT
' User '||username||'''S Account Information',
' Account Status => '||Account_status,
' Default Tablespace => '||default_tablespace,
' Temp Tablespace => '||temporary_tablespace
from dba_users where username=upper('SYS')

prompt -------------------------------------- OBJECTS OWNED BY USER --------------------------------

select a.username LoginId,
'User '||nvl(b.owner,'Does Not Own any objects..') ||' Owns ' ||nvl(b.cou,0)|| ' Nos Of ' ||nvl(b.object_type,'Objects')
FROM
dba_users a,
(SELECT OWNER,OBJECT_TYPE,COUNT(OBJECT_NAME) cou FROM DBA_OBJECTS GROUP BY OBJECT_TYPE,OWNER ORDER BY OWNER,OBJECT_TYPE) b
where a.username=b.owner (+) and a.username=upper('! &&B') and b.object_type(+)=upper('&&obj_type')
/

Prompt -------------------------------------- SYSTEM PRIVIELGES GRANTED TO THE USER ----------------

SELECT
nvl(b.grantee,'No_System_Privileges_Granted'),nvl(b.privilege,'null')
from
dba_users a,dba_sys_privs b
where a.username =b.grantee(+) and grantee=upper('&&B')
/

Prompt ------------------------------------ ROLES GRANTED TO THE USER ------------------------------

SELECT
nvl(b.grantee,'No_Roles_Granted'),nvl(b.granted_role,'null')
from
dba_users a,dba_role_privs b
where a.username =b.grantee(+) and grantee=upper('&&B')
/

Count Repeated Strings

This tip comes from Jose Luis Messina, a Programmer/Analyst at La Segunda SA, in Santa Fe, Argentina.


This code counts repeated strings.


create or replace function f_CountChr(p_instring VARCHAR2,
p_inpattern VARCHAR2) return NUMBER is


/*|||||||||||||||||||||||||||||||||||||||||||||||||
This process returns the times that the first parameter "p_inpattern" appears
in the second parameter "p_instring"

For example:
Example 1
-SELECT f_CountChr('ASASDSDS','ASA') FROM dual
- Return 1

Example 2
-SELECT f_CountChr('Radiation found on 2 jets in spy probe','on') FROM dual
- Return 2

Author: Jose Luis Messina
|||||||||||||||||||||||||||||||||||||||||||||||||*/

v_numChr NUMBER;

begin
v_numchr:= (length(p_instring)-length(REPLACE(p_instring,p_inpattern,'')))
/length(p_inpattern);
return(v_numchr);
end f_CountChr;