BLOG DBA TIPS AND TRICKS

Some standard scripts used for day to day activities.

NB: You’ll need to replace some characters like ’ or ” with correct apostrophes or quotation marks

DETERMINE OBJECT SIZE

select ‘grant select, insert, delete, update on TARGET_USER.’||object_name||’ to TESTUSER;’
from dba_objects
where owner = ‘TESTUSER’ and object_type like ‘%TABLE%’;

GENERATE GRANT ACCESS ON OBJECTS

select ‘grant select, insert, delete, update on SOURCE_USER.’||object_name||’ to TARGET_USER;’ from dba_objects where owner = ‘SOURCE_USER’ and object_type like ‘%TABLE%’;

CHECK REDO LOG SWITCH FREQUENCY

SELECT TO_CHAR(first_time, ‘YYYY-MM-DD HH24’) AS hour,
COUNT(*) AS log_switches
FROM v$log_history
WHERE first_time >= SYSDATE – 3
GROUP BY TO_CHAR(first_time, ‘YYYY-MM-DD HH24’)

CHECK QUERY PLAN FOR QUERY IN AWR

SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_AWR(‘aa5dxctxupj3c’ ));

Run awr report

SQL>@?/rdbms/admin/awrrpt
SQL>@?/rdbms/admin/awrgrpt (for all nodes in RAC)

TO ANALYZE A PLAN WITH ESTIMATED AND ACTUAL ROWS

SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
‘781vagczrcknf’,
NULL,
‘ALLSTATS LAST’
));

FIND TABLES USING LOGBESGMENTS

SELECT
l.owner, l.table_name, l.column_name, l.segment_name,
ROUND(s.bytes / 1024 / 1024/1024, 2) AS size_gb
FROM dba_lobs l JOIN dba_segments s
ON l.owner = s.owner AND l.segment_name = s.segment_name
WHERE l.segment_name in ( ‘SYS_LOB0000075807C00005$$’, ‘SYS_LOB0000075936C00002$$’,’SYS_LOB0000172191C00003$$’);

Move all tables from OLD_TBS to NEW_TBS tablespace

select ‘alter table ‘||owner||’.’||segment_name||’ move tablespace NEW_TBS;’
from dba_segments
where tablespace_name=’OLD_TBS’ and segment_type=’TABLE’;

Move all table partitions to NEW_TBS

SELECT ‘alter table ‘||owner||’.’
|| segment_name
|| ‘ move partition ‘
|| partition_name
|| ‘ tablespace NEW_TBS COMPRESS;’
|| ‘ ;’ –, trunc(bytes/1024/1024,2) as MB
FROM dba_segments a
WHERE — owner = ‘BI’ AND
segment_type = ‘TABLE PARTITION’
AND tablespace_name = ‘OLD_TBS’
AND ( partition_name LIKE ‘%201101%’
OR partition_name LIKE ‘%201102%’);

SCRIPT CREATE USER

CREATE USER NEW_USER IDENTIFIED BY “***” PROFILE SECURITY;
GRANT CONNECT, RESOURCE, CREATE SESSION TO NEW_USER;
ALTER USER NEW_USER PASSWORD EXPIRE;

Script to run a bunch of commands in parallel from OS to SQL

rm -rf sql_*.sql
rm -rf *.out

split -1 t.sql sql_
ls sql_* |awk ‘{print “mv ” $1 ” ” $1 “.sql” }’ |sh
ls sql_* |awk ‘{print “echo \” \” >> ” $1 }’ |sh

ls sql_* |awk ‘{print “nohup sqlplus / as sysdba <” $1 ” > ” $1 “.out & ” }’ |sh