Scripts – Page 2 – Technology Geek

Scripts / part 1

Posted on August 9, 2012December 23, 2018 by Osama Mustafa in Scripts

Find Below Links for two scripts show information about
1-Table information .
2-User Information .

Enjoy
Osama mustafa

Script to Extract Code to Recreate Materialized

Posted on August 9, 2012December 23, 2018 by Osama Mustafa in Scripts

This function will extract the DDL for an existing materialized view

SET serveroutput on
SET feedback off
UNDEF v_sql

DECLARE
   v_task_name       VARCHAR2 (100);
   v_mview_owner     VARCHAR2 (30)   := UPPER ('&&ENTER_MVIEW_OWNER');
   v_mview_name      VARCHAR2 (30)   := UPPER ('&&ENTER_MVIEW_NAME');
   v_mview_sql       VARCHAR2 (4000);
   v_mview_log_sql   VARCHAR2 (4000);
BEGIN
   -- get mview text from data dictionary
   SELECT DBMS_METADATA.get_ddl ('MATERIALIZED_VIEW', mview_name, owner)
     INTO v_mview_sql
     FROM dba_mviews
    WHERE owner = v_mview_owner AND mview_name = v_mview_name;

   SELECT DBMS_METADATA.get_dependent_ddl ('MATERIALIZED_VIEW_LOG',
                                           referenced_name,
                                           referenced_owner
                                          )
     INTO v_mview_log_sql
     FROM dba_dependencies
    WHERE referenced_type = 'TABLE'
      AND referenced_name != v_mview_name
      AND owner = v_mview_owner
      AND NAME = v_mview_name;

   DBMS_OUTPUT.put_line ('MVIEW SQL Is: ' || v_mview_sql);
   DBMS_OUTPUT.put_line ('MVIEW LOG SQL Is: ' || v_mview_log_sql);
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

Get Database Parameters even Hidden One

Posted on August 5, 2012December 23, 2018 by Osama Mustafa in Scripts

You can use this Scripts to check the init.ora in Oracle but not from v$parameter this Time

First Query : (display all init.ora parameter including the hidden parameters):

SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT
a.ksppinm “Parameter”,
decode(p.isses_modifiable,‘FALSE’,NULL,NULL,NULL,b.ksppstvl) “Session”,
c.ksppstvl “Instance”,
decode(p.isses_modifiable,‘FALSE’,‘F’,‘TRUE’,‘T’) “S”,
decode(p.issys_modifiable,‘FALSE’,‘F’,‘TRUE’,‘T’,‘IMMEDIATE’,‘I’,‘DEFERRED’,‘D’) “I”,
decode(p.isdefault,‘FALSE’,‘F’,‘TRUE’,‘T’) “D”,
a.ksppdesc “Description”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
ORDER BY a.ksppinm;

Second Query (list of parameter which are not default):

SET linesize 235 pagesize 200
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT * FROM (SELECT
a.ksppinm “Parameter”,
decode(p.isses_modifiable,‘FALSE’,NULL,NULL,NULL,b.ksppstvl) “Session”,
c.ksppstvl “Instance”,
decode(p.isses_modifiable,‘FALSE’,‘F’,‘TRUE’,‘T’) “S”,
decode(p.issys_modifiable,‘FALSE’,‘F’,‘TRUE’,‘T’,‘IMMEDIATE’,‘I’,‘DEFERRED’,‘D’) “I”,
decode(p.isdefault,‘FALSE’,‘F’,‘TRUE’,‘T’) “D”,
a.ksppdesc “Description”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
ORDER BY a.ksppinm) WHERE d=‘F’;

Scripts : Find Oracle Parameters

Find User with unlimited Tablespace Quota

Posted on August 5, 2012December 23, 2018 by Osama Mustafa in Scripts

First find the user with direct quota on tablespace SYSTEM.

SELECT username,tablespace_name, bytes, max_bytes
FROM dba_ts_quotas
WHERE max_bytes = –1 AND tablespace_name =‘SYSTEM’;

USERNAME TABLESPACE_NAME BYTES MAX_BYTES
————————- ————————- ———- ———-
SCOTT SYSTEM 0 –1
TEST SYSTEM 0 –1

why do we want to know who has unlimited quota on the SYSTEM tablespace?

User who have unlimited quota on SYSTEM as well the privilege to create tables or clusters could do some kind of denial of service attack to the database. The just have to fill up the free space in the SYSTEM tablespace. If there default tablespace is as well SYSTEM the could even do this without intention.

Find user with system privilege UNLIMITED TABLESPACE.

SELECT * FROM dba_sys_privs WHERE privilege = ‘UNLIMITED TABLESPACE’

GRANTEE PRIVILEGE ADM
—————————— —————————— —
WMSYS UNLIMITED TABLESPACE NO
RRDOMREG UNLIMITED TABLESPACE NO
HR UNLIMITED TABLESPACE NO
OE UNLIMITED TABLESPACE NO
SYS UNLIMITED TABLESPACE NO
LOGSTDBY_ADMINISTRATOR UNLIMITED TABLESPACE NO
SCOTT UNLIMITED TABLESPACE NO
BI UNLIMITED TABLESPACE NO
OUTLN UNLIMITED TABLESPACE NO
DBSNMP UNLIMITED TABLESPACE NO
IX UNLIMITED TABLESPACE NO
SH UNLIMITED TABLESPACE NO
DBA UNLIMITED TABLESPACE YES
SYSTEM UNLIMITED TABLESPACE YES

What about cascaded roles?

Mmh, but since Oracle 11g it is possible to grant UNLIMITED TABLESPACE to a role and this can be granted to an other role which is granted again to an other role. It could be a role concept or somebody who want to hide a little bit some privileges. To test it I’ve created three roles DBA3, DBA2 and DBA1, granted UNLIMITED TABLESPACE to DBA3.

SELECT
grantee,
privilege,
DECODE(p,‘=>’||grantee,‘direct’,p) path
FROM (
SELECT
grantee,
privilege,
SYS_CONNECT_BY_PATH(grantee, ‘=>’) p
FROM (
SELECT
grantee,
privilege
FROM dba_sys_privs
UNION ALL
SELECT
grantee,
granted_role privilege
FROM
dba_role_privs)
START WITH privilege = ‘UNLIMITED TABLESPACE’
CONNECT BY PRIOR grantee = privilege )
WHERE
(grantee IN (SELECT username FROM dba_users)
OR grantee = ‘PUBLIC’);

GRANTEE PRIVILEGE PATH
——— ———————– ——————————-
BI UNLIMITED TABLESPACE direct
SYS DBA =>DBA=>SYS
SYSTEM DBA =>DBA=>SYSTEM
SCOTT DBA1 =>DBA3=>DBA2=>DBA1=>SCOTT
SYS DBA1 =>DBA3=>DBA2=>DBA1=>SYS
SYS DBA2 =>DBA3=>DBA2=>SYS
SYS DBA3 =>DBA3=>SYS
DBSNMP UNLIMITED TABLESPACE direct
HR UNLIMITED TABLESPACE direct
IX UNLIMITED TABLESPACE direct
SYS LOGSTDBY_ADMINISTRATOR =>LOGSTDBY_ADMINISTRATOR=>SYS
OE UNLIMITED TABLESPACE direct
OUTLN UNLIMITED TABLESPACE direct
RRDOMREG UNLIMITED TABLESPACE direct
SH UNLIMITED TABLESPACE direct
SYS UNLIMITED TABLESPACE direct
SYSTEM UNLIMITED TABLESPACE direct
WMSYS UNLIMITED TABLESPACE direct

18 ROWS selected.

create one to find user’s with direct quotas as well through a system privilege will give something like this.

SELECT
username,
tablespace_name,
privilege
FROM (
SELECT
grantee username, ‘Any Tablespace’ tablespace_name, privilege
FROM (
— first get the users with direct grants
SELECT
p1.grantee grantee, privilege
FROM
dba_sys_privs p1
WHERE
p1.privilege=‘UNLIMITED TABLESPACE’
UNION ALL
— and then the ones with UNLIMITED TABLESPACE through a role…
SELECT
r3.grantee, granted_role privilege
FROM
dba_role_privs r3
START WITH r3.granted_role IN (
SELECT
DISTINCT p4.grantee
FROM
dba_role_privs r4, dba_sys_privs p4
WHERE
r4.granted_role=p4.grantee
AND p4.privilege = ‘UNLIMITED TABLESPACE’)
CONNECT BY PRIOR grantee = granted_role)
— we just whant to see the users not the roles
WHERE grantee IN (SELECT username FROM dba_users) OR grantee = ‘PUBLIC’
UNION ALL
— list the user with unimited quota on a dedicated tablespace
SELECT
username,tablespace_name,‘DBA_TS_QUOTA’ privilege
FROM
dba_ts_quotas
WHERE
max_bytes = –1 )
WHERE tablespace_name LIKE UPPER(‘SYSTEM’)
OR tablespace_name = ‘Any Tablespace’;

USERNAME TABLESPACE_NAME PRIVILEGE
————————- ————————- ——————————
…
SYSTEM Any Tablespace UNLIMITED TABLESPACE
SYS Any Tablespace DBA
SYSTEM Any Tablespace DBA
SCOTT Any Tablespace DBA1
SYS Any Tablespace DBA1
SYS Any Tablespace DBA2
SYS Any Tablespace DBA3
SYS Any Tablespace LOGSTDBY_ADMINISTRATOR
TEST SYSTEM DBA_TS_QUOTA

19 ROWS selected.

You Can Download Script from here : Tablespace-scripts
Links :
1-Find TableSpace Quota .
Thank you
Osama Mustafa

Tablespace growth

Posted on July 25, 2012December 23, 2018 by Osama Mustafa in Scripts

Sometimes you need to know how much your tablespace grow this month find below some scripts to do this :

Script-1 :

SELECT TO_CHAR (sp.begin_interval_time,’DD-MM-YYYY’) days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN (‘SYSAUX’,’SYSTEM’)
GROUP BY TO_CHAR (sp.begin_interval_time,’DD-MM-YYYY’), ts.tsname
ORDER BY ts.tsname, days;

Script-2:

column “Percent of Total Disk Usage” justify right format 999.99
column “Space Used (MB)” justify right format 9,999,999.99
column “Total Object Size (MB)” justify right format 9,999,999.99
set linesize 150
set pages 80
set feedback off
set line 5000
column “SEGMENT_NAME” justify left format A30
column “TABLESPACE_NAME” justify left format A30
select * from (select c.TABLESPACE_NAME,c.segment_name,to_char(end_interval_time, ‘MM/DD/YY’) mydate, sum(space_used_delta) / 1024 / 1024 “Space used (MB)”, avg(c.bytes) / 1024 / 1024 “Total Object Size (MB)”,
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) “Percent of Total Disk Usage”
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) – 10
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.segment_name = ‘S_PARTY’
group by c.TABLESPACE_NAME,c.segment_name,to_char(end_interval_time, ‘MM/DD/YY’)
order by c.TABLESPACE_NAME,c.segment_name,to_date(mydate, ‘MM/DD/YY’));

Script-3:

set pages 80
set feedback off
column “OBJECT_NAME” justify left format A30
column “SUBOBJECT_NAME” justify left format A30
column “OBJECT_TYPE” justify left format A30
column “Tablespace Name” justify left format A30
set line 5000
SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
t.NAME “Tablespace Name”, s.growth/(1024*1024) “Growth in MB”,
(SELECT sum(bytes)/(1024*1024)
FROM dba_segments
WHERE segment_name=o.object_name) “Total Size(MB)”
FROM DBA_OBJECTS o,
( SELECT TS#,OBJ#,
SUM(SPACE_USED_DELTA) growth
FROM DBA_HIST_SEG_STAT
GROUP BY TS#,OBJ#
HAVING SUM(SPACE_USED_DELTA) > 0
ORDER BY 2 DESC ) s,
v$tablespace t
WHERE s.OBJ# = o.OBJECT_ID
AND s.TS#=t.TS#
AND o.OWNER=’SIEBEL’
ORDER BY 6 DESC
/

Script-4:

set feedback on
select * from (select c.TABLESPACE_NAME,c.segment_name “Object Name”,b.object_type,
sum(space_used_delta) / 1024 / 1024 “Growth (MB)”
from dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) – &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner =’SIEBEL’
group by c.TABLESPACE_NAME,c.segment_name,b.object_type)
order by 3 asc;

Thank you

Table Locks

Posted on July 24, 2012December 23, 2018 by Osama Mustafa in Scripts

query to get the locked tables in oracle :

SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||’.’||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,’NONE’,
1,’NULL’,
2,’ROW SHARE’,
3,’ROW EXCLUSIVE’,
4,’SHARE’,
5,’SHARE ROW EXCLUSIVE’,
6,’EXCLUSIVE’,
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id ;

Or you can do the below :

select * From v$locked_object;
select * From v$session where SID = ”;
select * from dba_objects where object_id = ”;

Locked objects :

Select object_name, owner, object_type from dba_objects
Where object_id in (select object_id from v$locked_object);

KILL SESSION COMMAND

ALTER SYSTEM KILL SESSION ‘SID,Serial#’ IMMEDIATE;

Osama Mustafa

drop all tables in a tablespace

Posted on July 23, 2012December 23, 2018 by Osama Mustafa in Scripts

set echo off 
prompt 
prompt this script is used to generate SQL file for deleting all tables in a tablespace.
prompt 
prompt specify tablespace name 1: 
define tsname=&1 
prompt Add purge clause, y for yes, n for no 2: 
define prg=&2
prompt enter output sql file name 3: 
define filename=&3 
set heading off 
set verify off 
set feedback off
start del_ts_tb.sql 
set verify on 
set heading on 
set echo on
set feedback on

spool &filename 
select 'drop table ' || owner || '.' || table_name || decode(upper('&prg'),'Y',' purge','') || ';' DropState
from dba_tables
where tablespace_name = upper('&tsname');
spool off

Osama mustafa

knowing the transaction status

Posted on July 23, 2012December 23, 2018 by Osama Mustafa in Scripts

**All tranasactions/sid/username

select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec
from v$transaction t, v$session s
where t.ses_addr=s.saddr;

**All tranasactions/sid/username/first 64 bytes of SQL:

select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec,sql.sql_text
from v$transaction t, v$session s, v$sql sql
where t.ses_addr=s.saddr
and s.sql_address=sql.address and s.sql_hash_value=sql.hash_value;

Enjoy

Osama mustafa

Shell Script For Cold Backup

Posted on July 22, 2012December 23, 2018 by Osama Mustafa in Scripts

This shell script selects the datafiles, logfiles and control files,

tars and gzips them and then sends them to a remote host via rsh.

Download Scripts : Cold_backup.sh

I have to upload the script since its contain codes can’t be appeared on Blog .

<eofsql 0="" 120="" <eofsql eofsql

Enjoy
osama mustafa

Category: Scripts