Generate Stats
begin
dbms_stats.gather_table_stats(
ownname => 'owner',
Tabname => 'Tablename',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns size auto',
degree => 4,
cascade => true
);
end;
/
Table and index size
select tab.OWNER , tab.SEGMENT_NAME , tab.tabsz , idx.idxsz
from
(select OWNER,SEGMENT_NAME,SEGMENT_TYPE, sum(BYTES)/1024/1024 tabsz
from dba_segments
where --OWNER in ('') and
SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION')
group by OWNER,SEGMENT_NAME,SEGMENT_TYPE ) tab ,
(select s.OWNER , i.TABLE_NAME --,s.SEGMENT_TYPE
, sum(BYTES)/1024/1024 idxsz
from dba_segments s, dba_indexes i
where --s.OWNER in (' ') and
s.OWNER = i.OWNER
and s.SEGMENT_NAME = i.INDEX_NAME
and s.SEGMENT_TYPE in ('INDEX','INDEX PARTITION')
group by s.OWNER ,i.TABLE_NAME ) idx
where tab.OWNER = idx.OWNER(+)
and tab.SEGMENT_NAME = idx.TABLE_NAME(+)
and tab.OWNER not in ('SYS','SYSTEM','OEM','PERFSTAT','SYSMAN')
order by tab.OWNER;
select tab.OWNER , tab.SEGMENT_NAME , tab.tabsz tabsz_GB, idx.idxsz idxsz_GB, tab.tabsz + nvl(idx.idxsz,0) Total_GB
from
(select OWNER,SEGMENT_NAME,SEGMENT_TYPE, round(sum(BYTES)/1024/1024/1024) tabsz
from dba_segments
where --OWNER in ('') and
SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION')
group by OWNER,SEGMENT_NAME,SEGMENT_TYPE ) tab ,
(select s.OWNER , i.TABLE_NAME --,s.SEGMENT_TYPE
, round(sum(BYTES)/1024/1024/1024) idxsz
from dba_segments s, dba_indexes i
where --s.OWNER in (' ') and
s.OWNER = i.OWNER
and s.SEGMENT_NAME = i.INDEX_NAME
and s.SEGMENT_TYPE in ('INDEX','INDEX PARTITION')
group by s.OWNER ,i.TABLE_NAME ) idx
where tab.OWNER = idx.OWNER(+)
and tab.SEGMENT_NAME = idx.TABLE_NAME(+)
and tab.OWNER not in ('SYS','SYSTEM','OEM','PERFSTAT','SYSMAN')
order by tab.tabsz + nvl(idx.idxsz,0) desc;
List all users/ roles which have access to a particular table
select * from
(
SELECT table_name, grantee, (select 'Role ' from dba_roles where role= Grantee) ,
MAX(DECODE(privilege,'SELECT','SELECT,')) ||
MAX(DECODE(privilege,'INSERT','INSERT,')) ||
MAX(DECODE(privilege,'UPDATE','UPDATE,')) ||
MAX(DECODE(privilege,'DELETE','DELETE,')) ||
MAX(DECODE(privilege,'EXECUTE','EXECUTE,')) ||
MAX(DECODE(privilege,'DEQUEUE','DEQUEUE,')) ||
MAX(DECODE(privilege,'QUERY REWRITE','QUERY REWRITE,')) ||
MAX(DECODE(privilege,'REFERENCES','REFERENCES,')) ||
MAX(DECODE(privilege,'ON COMMIT REFRESH','ON COMMIT REFRESH,')) ||
MAX(DECODE(privilege,'READ','READ,')) ||
MAX(DECODE(privilege,'ALTER','ALTER,')) ||
MAX(DECODE(privilege,'INDEX','INDEX,')) ||
MAX(DECODE(privilege,'DEBUG','DEBUG,')) ||
MAX(DECODE(privilege,'FLASHBACK','FLASHBACK,')) ||
MAX(DECODE(privilege,'UNDER','UNDER,')) ||
MAX(DECODE(privilege,'WRITE','WRITE,')) "PRIVILEGEs"
FROM dba_tab_privs
WHERE grantee IN (
SELECT role FROM dba_roles
union
select username from dba_users
)
GROUP BY table_name, grantee)
where table_name=upper('&1');
List whether a particular user have any access through role on a given table.
select dtp.table_name,dtp.grantee role,dtp.priv,drp.GRANTEE usr from
(SELECT table_name, grantee, (select 'Role ' from dba_roles where role= Grantee) role,
MAX(DECODE(privilege,'SELECT','SELECT,')) ||
MAX(DECODE(privilege,'INSERT','INSERT,')) ||
MAX(DECODE(privilege,'UPDATE','UPDATE,')) ||
MAX(DECODE(privilege,'DELETE','DELETE,')) ||
MAX(DECODE(privilege,'EXECUTE','EXECUTE,')) ||
MAX(DECODE(privilege,'DEQUEUE','DEQUEUE,')) ||
MAX(DECODE(privilege,'QUERY REWRITE','QUERY REWRITE,')) ||
MAX(DECODE(privilege,'REFERENCES','REFERENCES,')) ||
MAX(DECODE(privilege,'ON COMMIT REFRESH','ON COMMIT REFRESH,')) ||
MAX(DECODE(privilege,'READ','READ,')) ||
MAX(DECODE(privilege,'ALTER','ALTER,')) ||
MAX(DECODE(privilege,'INDEX','INDEX,')) ||
MAX(DECODE(privilege,'DEBUG','DEBUG,')) ||
MAX(DECODE(privilege,'FLASHBACK','FLASHBACK,')) ||
MAX(DECODE(privilege,'UNDER','UNDER,')) ||
MAX(DECODE(privilege,'WRITE','WRITE,')) priv
FROM dba_tab_privs
WHERE grantee IN ( SELECT role FROM dba_roles)
GROUP BY table_name, grantee
) dtp, dba_role_privs drp
where dtp.grantee = drp.GRANTED_ROLE
and table_name=upper('&tabname')
and drp.GRANTEE=upper('&usr');
Index :
select OWNER,INDEX_NAME,TABLE_NAME,TABLE_OWNER,INDEX_TYPE,TABLESPACE_NAME from dba_indexes where TABLE_NAME='&table_name' and TABLE_OWNER='&owner';
select INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME from ALL_IND_COLUMNS where TABLE_NAME='&table_name' and TABLE_OWNER='&owner';
select dbms_metadata.get_ddl('INDEX', '&index_name','&owner') from dual;
Index Size and tablespace size
select OWNER,SEGMENT_NAME,SEGMENT_TYPE,seg.TABLESPACE_NAME , seg.sz, df.sz
from
(select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME, BYTES/1024/1024/1024 sz
from dba_segments ) seg ,
( select TABLESPACE_NAME , sum(BYTES/1024/1024/1024) sz
from dba_data_files group by TABLESPACE_NAME ) df
where seg.TABLESPACE_NAME = df.TABLESPACE_NAME
and seg.SEGMENT_TYPE in ('INDEX','INDEX PARTITION')
and seg.OWNER not in ('SYS','SYSTEM','SYSMAN','OEM','PERFSTAT','OUTLN','DBSNMP')
and df.sz - seg.sz > 1
Indexes not being used :
select index_name from dba_indexes where table_owner='&&owner' --table_name='&tablename'
and index_name not in (select c1 from(
select p.object_name c1, p.operation c2, p.options c3, count(1) c4
from dba_hist_sql_plan p, dba_hist_sqlstat s
where p.object_owner = '&owner' and p.operation like '%INDEX%' and p.sql_id = s.sql_id
group by p.object_name, p.operation, p.options
order by 1,2,3));
Constraints :
select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints where TABLE_NAME='&table_name' and OWNER='&owner';
select * from dba_constraints
where r_constraint_name in (select constraint_name from all_constraints where table_name='&1');
select dbms_metadata.get_ddl('CONSTRAINT','&const_name','&owner') from dual;
select dbms_metadata.get_ddl('REF_CONSTRAINT','&const_name','&owner') from dual;
User :
SET ECHO OFF
SET PAGESIZE 0
SET LINES 3000
SET LONG 200000
SET FEEDBACK OFF
SET HEADING OFF
SET VERIFY OFF
SET SERVEROUTPUT ON SIZE 1000000
COLUMN txt FORMAT a2480 WORD_WRAPPED
set trimspool on
set sqlprompt ""
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
col name noprint new_val nm
select name from v$database;
def username=&1
spool mapuser_&nm\.sql
SELECT dbms_metadata.get_ddl('USER','&&username') txt FROM dual;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&username') txt FROM DUAL;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&username') txt FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&username') txt from dual;
SELECT dbms_metadata.get_granted_ddl ('DEFAULT_ROLE','&username') txt from dual;
SELECT dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&username') txt from dual;
spoo off
!cat mapuser_&nm\.sql |egrep -iv 'spoo|dual' > mapuser_&nm\.lst
List all roles/sys privileges given to a user or role
select * from dba_role_privs where grantee = upper('&user');
select
lpad(' ', 2*level) ||ty || granted_role "User, his roles and privileges"
from
(
select ' ' ty, null grantee,username granted_role from dba_users where username like upper('&enter_username')
union
select '*' ty, null grantee, role granted_role from dba_roles where role = upper('&role')
union
select '*' , grantee, granted_role from dba_role_privs
union
select ' ', grantee, privilege from dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
List object privileges given to a user/role
select ' grant ' || privilege || ' on ' || owner||'.'|| table_name || ' to '|| grantee||';'
from dba_tab_privs where grantee like upper('&GRANTEE_NAME') order by privilege;
Roles :
SELECT dbms_metadata.get_ddl('ROLE','&role_name') FROM DUAL;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&role_name') FROM DUAL;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', '&role_name') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&role_name') from dual;
Objects with size greater than 10GB
select OWNER,SEGMENT_NAME,round(sum(bytes)/1024/1024/1024) from dba_segments
group by OWNER,SEGMENT_NAME
having sum(bytes)/1024/1024/1024 > 50
order by sum(bytes)/1024/1024/1024 desc ;
select OWNER,SEGMENT_NAME,round(sum(bytes)/1024/1024/1024) from dba_segments
group by OWNER,SEGMENT_NAME
having sum(bytes)/1024/1024/1024 between 10 and 50
order by sum(bytes)/1024/1024/1024 desc;
Count of objects in each schema
select OWNER,count(SEGMENT_NAME)
from dba_segments
where OWNER not in ('SYS','SYSMAN','DBSNMP')
group by OWNER
having count(SEGMENT_NAME) > 20
order by count(SEGMENT_NAME) desc;
Schema using maximum space
select OWNER,round(sum(bytes)/1024/1024/1024) from dba_segments
group by OWNER
order by sum(bytes)/1024/1024/1024 desc;
Tables whose indexes are not analyzed
SELECT 'Index '||i.index_name||' not analyzed but table '||
i.table_name||' is.'
FROM dba_tables t, dba_indexes i
WHERE t.table_name = i.table_name
AND t.num_rows IS NOT NULL
AND i.distinct_keys IS NULL
and t.OWNER = '&owner';
Check How Much Table data has Changed Since the Last Analyze :
SELECT *
FROM ( SELECT m.table_owner
, m.table_name
, t.last_analyzed
, m.inserts
, m.updates
, m.deletes
, t.num_rows
, ( m.inserts + m.updates + m.deletes ) / CASE WHEN t.num_rows IS NULL OR t.num_rows = 0 THEN 1 ELSE t.num_rows END "Change Factor"
FROM dba_tab_modifications m
, dba_tables t
WHERE t.owner = m.table_owner
AND t.table_name = m.table_name
AND m.inserts + m.updates + m.deletes > 1
--AND m.table_owner='&Enter_Table_Owner'
--AND m.table_name like '&Enter_Table_Name'
and t.OWNER = '&owner'
ORDER BY "Change Factor" DESC
);
Indexes to rebuild :
. Check the size of index if it's greater than the data size in table.
. Check if there's large waits on sequential reads on index tablespace.
. Larger block sizes for index can help in few conditions.
. Check if there's too many blocks read for an index range scan or an index fast-full-scan.
. Even if deletion is 5% in a big table with millions of records can degrade performance.
. Before the batch jobs , drop the index and rebuils after loading.
. Check if deleted entries are more than 20% of current entries.
. Chckif index depth is more then 4 levels.
select * from index_stats where --btree_space > 8192 and
(height > 3
or pct_used < 75
or (del_lf_rows/(decode(lf_rows,0,1,lf_rows)) *100) > 20);
ALTER INDEX index_name REBUILD tablespace new_tablespace;
If you have 16K data cache , use tablespace index of 16K
http://www.dba-oracle.com/t_plan9i_sql_full_table_scans.htm
Last time the table accessed :
SELECT du.username,
s.sql_text,
MAX(ash.sample_time) AS last_access ,
sp.object_owner ,
sp.object_name ,
sp.object_alias as aliased_as ,
sp.object_type ,
COUNT(*) AS access_count
FROM v$active_session_history ash
JOIN v$sql s ON ash.force_matching_signature = s.force_matching_signature
LEFT JOIN v$sql_plan sp ON s.sql_id = sp.sql_id
JOIN DBA_USERS du ON ash.user_id = du.USER_ID
WHERE ash.session_type = 'FOREGROUND'
AND ash.SQL_ID IS NOT NULL
AND sp.object_name IS NOT NULL
AND ash.user_id <> 0
AND sp.object_owner not in ('SYS')
GROUP BY du.username,
s.sql_text,
sp.object_owner,
sp.object_name,
sp.object_alias,
sp.object_type
ORDER BY 3 DESC;
begin
dbms_stats.gather_table_stats(
ownname => 'owner',
Tabname => 'Tablename',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns size auto',
degree => 4,
cascade => true
);
end;
/
Table and index size
select tab.OWNER , tab.SEGMENT_NAME , tab.tabsz , idx.idxsz
from
(select OWNER,SEGMENT_NAME,SEGMENT_TYPE, sum(BYTES)/1024/1024 tabsz
from dba_segments
where --OWNER in ('') and
SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION')
group by OWNER,SEGMENT_NAME,SEGMENT_TYPE ) tab ,
(select s.OWNER , i.TABLE_NAME --,s.SEGMENT_TYPE
, sum(BYTES)/1024/1024 idxsz
from dba_segments s, dba_indexes i
where --s.OWNER in (' ') and
s.OWNER = i.OWNER
and s.SEGMENT_NAME = i.INDEX_NAME
and s.SEGMENT_TYPE in ('INDEX','INDEX PARTITION')
group by s.OWNER ,i.TABLE_NAME ) idx
where tab.OWNER = idx.OWNER(+)
and tab.SEGMENT_NAME = idx.TABLE_NAME(+)
and tab.OWNER not in ('SYS','SYSTEM','OEM','PERFSTAT','SYSMAN')
order by tab.OWNER;
select tab.OWNER , tab.SEGMENT_NAME , tab.tabsz tabsz_GB, idx.idxsz idxsz_GB, tab.tabsz + nvl(idx.idxsz,0) Total_GB
from
(select OWNER,SEGMENT_NAME,SEGMENT_TYPE, round(sum(BYTES)/1024/1024/1024) tabsz
from dba_segments
where --OWNER in ('') and
SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION')
group by OWNER,SEGMENT_NAME,SEGMENT_TYPE ) tab ,
(select s.OWNER , i.TABLE_NAME --,s.SEGMENT_TYPE
, round(sum(BYTES)/1024/1024/1024) idxsz
from dba_segments s, dba_indexes i
where --s.OWNER in (' ') and
s.OWNER = i.OWNER
and s.SEGMENT_NAME = i.INDEX_NAME
and s.SEGMENT_TYPE in ('INDEX','INDEX PARTITION')
group by s.OWNER ,i.TABLE_NAME ) idx
where tab.OWNER = idx.OWNER(+)
and tab.SEGMENT_NAME = idx.TABLE_NAME(+)
and tab.OWNER not in ('SYS','SYSTEM','OEM','PERFSTAT','SYSMAN')
order by tab.tabsz + nvl(idx.idxsz,0) desc;
List all users/ roles which have access to a particular table
select * from
(
SELECT table_name, grantee, (select 'Role ' from dba_roles where role= Grantee) ,
MAX(DECODE(privilege,'SELECT','SELECT,')) ||
MAX(DECODE(privilege,'INSERT','INSERT,')) ||
MAX(DECODE(privilege,'UPDATE','UPDATE,')) ||
MAX(DECODE(privilege,'DELETE','DELETE,')) ||
MAX(DECODE(privilege,'EXECUTE','EXECUTE,')) ||
MAX(DECODE(privilege,'DEQUEUE','DEQUEUE,')) ||
MAX(DECODE(privilege,'QUERY REWRITE','QUERY REWRITE,')) ||
MAX(DECODE(privilege,'REFERENCES','REFERENCES,')) ||
MAX(DECODE(privilege,'ON COMMIT REFRESH','ON COMMIT REFRESH,')) ||
MAX(DECODE(privilege,'READ','READ,')) ||
MAX(DECODE(privilege,'ALTER','ALTER,')) ||
MAX(DECODE(privilege,'INDEX','INDEX,')) ||
MAX(DECODE(privilege,'DEBUG','DEBUG,')) ||
MAX(DECODE(privilege,'FLASHBACK','FLASHBACK,')) ||
MAX(DECODE(privilege,'UNDER','UNDER,')) ||
MAX(DECODE(privilege,'WRITE','WRITE,')) "PRIVILEGEs"
FROM dba_tab_privs
WHERE grantee IN (
SELECT role FROM dba_roles
union
select username from dba_users
)
GROUP BY table_name, grantee)
where table_name=upper('&1');
List whether a particular user have any access through role on a given table.
select dtp.table_name,dtp.grantee role,dtp.priv,drp.GRANTEE usr from
(SELECT table_name, grantee, (select 'Role ' from dba_roles where role= Grantee) role,
MAX(DECODE(privilege,'SELECT','SELECT,')) ||
MAX(DECODE(privilege,'INSERT','INSERT,')) ||
MAX(DECODE(privilege,'UPDATE','UPDATE,')) ||
MAX(DECODE(privilege,'DELETE','DELETE,')) ||
MAX(DECODE(privilege,'EXECUTE','EXECUTE,')) ||
MAX(DECODE(privilege,'DEQUEUE','DEQUEUE,')) ||
MAX(DECODE(privilege,'QUERY REWRITE','QUERY REWRITE,')) ||
MAX(DECODE(privilege,'REFERENCES','REFERENCES,')) ||
MAX(DECODE(privilege,'ON COMMIT REFRESH','ON COMMIT REFRESH,')) ||
MAX(DECODE(privilege,'READ','READ,')) ||
MAX(DECODE(privilege,'ALTER','ALTER,')) ||
MAX(DECODE(privilege,'INDEX','INDEX,')) ||
MAX(DECODE(privilege,'DEBUG','DEBUG,')) ||
MAX(DECODE(privilege,'FLASHBACK','FLASHBACK,')) ||
MAX(DECODE(privilege,'UNDER','UNDER,')) ||
MAX(DECODE(privilege,'WRITE','WRITE,')) priv
FROM dba_tab_privs
WHERE grantee IN ( SELECT role FROM dba_roles)
GROUP BY table_name, grantee
) dtp, dba_role_privs drp
where dtp.grantee = drp.GRANTED_ROLE
and table_name=upper('&tabname')
and drp.GRANTEE=upper('&usr');
Index :
select OWNER,INDEX_NAME,TABLE_NAME,TABLE_OWNER,INDEX_TYPE,TABLESPACE_NAME from dba_indexes where TABLE_NAME='&table_name' and TABLE_OWNER='&owner';
select INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME from ALL_IND_COLUMNS where TABLE_NAME='&table_name' and TABLE_OWNER='&owner';
select dbms_metadata.get_ddl('INDEX', '&index_name','&owner') from dual;
Index Size and tablespace size
select OWNER,SEGMENT_NAME,SEGMENT_TYPE,seg.TABLESPACE_NAME , seg.sz, df.sz
from
(select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME, BYTES/1024/1024/1024 sz
from dba_segments ) seg ,
( select TABLESPACE_NAME , sum(BYTES/1024/1024/1024) sz
from dba_data_files group by TABLESPACE_NAME ) df
where seg.TABLESPACE_NAME = df.TABLESPACE_NAME
and seg.SEGMENT_TYPE in ('INDEX','INDEX PARTITION')
and seg.OWNER not in ('SYS','SYSTEM','SYSMAN','OEM','PERFSTAT','OUTLN','DBSNMP')
and df.sz - seg.sz > 1
Indexes not being used :
select index_name from dba_indexes where table_owner='&&owner' --table_name='&tablename'
and index_name not in (select c1 from(
select p.object_name c1, p.operation c2, p.options c3, count(1) c4
from dba_hist_sql_plan p, dba_hist_sqlstat s
where p.object_owner = '&owner' and p.operation like '%INDEX%' and p.sql_id = s.sql_id
group by p.object_name, p.operation, p.options
order by 1,2,3));
Constraints :
select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints where TABLE_NAME='&table_name' and OWNER='&owner';
select * from dba_constraints
where r_constraint_name in (select constraint_name from all_constraints where table_name='&1');
select dbms_metadata.get_ddl('CONSTRAINT','&const_name','&owner') from dual;
select dbms_metadata.get_ddl('REF_CONSTRAINT','&const_name','&owner') from dual;
User :
SET ECHO OFF
SET PAGESIZE 0
SET LINES 3000
SET LONG 200000
SET FEEDBACK OFF
SET HEADING OFF
SET VERIFY OFF
SET SERVEROUTPUT ON SIZE 1000000
COLUMN txt FORMAT a2480 WORD_WRAPPED
set trimspool on
set sqlprompt ""
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
col name noprint new_val nm
select name from v$database;
def username=&1
spool mapuser_&nm\.sql
SELECT dbms_metadata.get_ddl('USER','&&username') txt FROM dual;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&username') txt FROM DUAL;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&username') txt FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&username') txt from dual;
SELECT dbms_metadata.get_granted_ddl ('DEFAULT_ROLE','&username') txt from dual;
SELECT dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&username') txt from dual;
spoo off
!cat mapuser_&nm\.sql |egrep -iv 'spoo|dual' > mapuser_&nm\.lst
List all roles/sys privileges given to a user or role
select * from dba_role_privs where grantee = upper('&user');
select
lpad(' ', 2*level) ||ty || granted_role "User, his roles and privileges"
from
(
select ' ' ty, null grantee,username granted_role from dba_users where username like upper('&enter_username')
union
select '*' ty, null grantee, role granted_role from dba_roles where role = upper('&role')
union
select '*' , grantee, granted_role from dba_role_privs
union
select ' ', grantee, privilege from dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
List object privileges given to a user/role
select ' grant ' || privilege || ' on ' || owner||'.'|| table_name || ' to '|| grantee||';'
from dba_tab_privs where grantee like upper('&GRANTEE_NAME') order by privilege;
Roles :
SELECT dbms_metadata.get_ddl('ROLE','&role_name') FROM DUAL;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&role_name') FROM DUAL;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', '&role_name') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&role_name') from dual;
Objects with size greater than 10GB
select OWNER,SEGMENT_NAME,round(sum(bytes)/1024/1024/1024) from dba_segments
group by OWNER,SEGMENT_NAME
having sum(bytes)/1024/1024/1024 > 50
order by sum(bytes)/1024/1024/1024 desc ;
select OWNER,SEGMENT_NAME,round(sum(bytes)/1024/1024/1024) from dba_segments
group by OWNER,SEGMENT_NAME
having sum(bytes)/1024/1024/1024 between 10 and 50
order by sum(bytes)/1024/1024/1024 desc;
Count of objects in each schema
select OWNER,count(SEGMENT_NAME)
from dba_segments
where OWNER not in ('SYS','SYSMAN','DBSNMP')
group by OWNER
having count(SEGMENT_NAME) > 20
order by count(SEGMENT_NAME) desc;
Schema using maximum space
select OWNER,round(sum(bytes)/1024/1024/1024) from dba_segments
group by OWNER
order by sum(bytes)/1024/1024/1024 desc;
Tables whose indexes are not analyzed
SELECT 'Index '||i.index_name||' not analyzed but table '||
i.table_name||' is.'
FROM dba_tables t, dba_indexes i
WHERE t.table_name = i.table_name
AND t.num_rows IS NOT NULL
AND i.distinct_keys IS NULL
and t.OWNER = '&owner';
Check How Much Table data has Changed Since the Last Analyze :
SELECT *
FROM ( SELECT m.table_owner
, m.table_name
, t.last_analyzed
, m.inserts
, m.updates
, m.deletes
, t.num_rows
, ( m.inserts + m.updates + m.deletes ) / CASE WHEN t.num_rows IS NULL OR t.num_rows = 0 THEN 1 ELSE t.num_rows END "Change Factor"
FROM dba_tab_modifications m
, dba_tables t
WHERE t.owner = m.table_owner
AND t.table_name = m.table_name
AND m.inserts + m.updates + m.deletes > 1
--AND m.table_owner='&Enter_Table_Owner'
--AND m.table_name like '&Enter_Table_Name'
and t.OWNER = '&owner'
ORDER BY "Change Factor" DESC
);
Indexes to rebuild :
. Check the size of index if it's greater than the data size in table.
. Check if there's large waits on sequential reads on index tablespace.
. Larger block sizes for index can help in few conditions.
. Check if there's too many blocks read for an index range scan or an index fast-full-scan.
. Even if deletion is 5% in a big table with millions of records can degrade performance.
. Before the batch jobs , drop the index and rebuils after loading.
. Check if deleted entries are more than 20% of current entries.
. Chckif index depth is more then 4 levels.
select * from index_stats where --btree_space > 8192 and
(height > 3
or pct_used < 75
or (del_lf_rows/(decode(lf_rows,0,1,lf_rows)) *100) > 20);
SELECT OWNER, INDEX_NAME, TABLE_NAME, LAST_ANALYZED, BLEVEL
FROM DBA_INDEXES
WHERE OWNER NOT IN ('SYS', 'SYSTEM')
AND BLEVEL >= 4
ORDER BY BLEVEL DESC;
FROM DBA_INDEXES
WHERE OWNER NOT IN ('SYS', 'SYSTEM')
AND BLEVEL >= 4
ORDER BY BLEVEL DESC;
ALTER INDEX index_name REBUILD tablespace new_tablespace;
If you have 16K data cache , use tablespace index of 16K
http://www.dba-oracle.com/t_plan9i_sql_full_table_scans.htm
Last time the table accessed :
SELECT du.username,
s.sql_text,
MAX(ash.sample_time) AS last_access ,
sp.object_owner ,
sp.object_name ,
sp.object_alias as aliased_as ,
sp.object_type ,
COUNT(*) AS access_count
FROM v$active_session_history ash
JOIN v$sql s ON ash.force_matching_signature = s.force_matching_signature
LEFT JOIN v$sql_plan sp ON s.sql_id = sp.sql_id
JOIN DBA_USERS du ON ash.user_id = du.USER_ID
WHERE ash.session_type = 'FOREGROUND'
AND ash.SQL_ID IS NOT NULL
AND sp.object_name IS NOT NULL
AND ash.user_id <> 0
AND sp.object_owner not in ('SYS')
GROUP BY du.username,
s.sql_text,
sp.object_owner,
sp.object_name,
sp.object_alias,
sp.object_type
ORDER BY 3 DESC;
