oracle agent shrink - ORACLEAGENT BLOG

Check Installed RDBMS components

col comp_id for a10
col comp_name for a56
col version for a12
col status for a10
set pagesize 200
set lines 200
set long 999
select comp_id,comp_name,version,status from dba_registry;

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

How to Get Alert log location in db

set pagesize 299
set lines 299
col value for a65
select * from v$diag_info where NAME=’Diag Trace’;
(or)
Select value from V$diag_info;

How to Cleanup orphaned datapump jobs

— Find the orphaned Data Pump jobs:

SELECT owner_name, job_name, rtrim(operation) “OPERATION”,
rtrim(job_mode) “JOB_MODE”, state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE ‘BIN$%’ and state=’NOT RUNNING’
ORDER BY 1,2;

— Drop the tables

SELECT ‘drop table ‘ || owner_name || ‘.’ || job_name || ‘;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and job_name NOT LIKE ‘BIN$%’

How to Cleanup orphaned datapump jobs

— Find the orphaned Data Pump jobs:

— Drop the tables

SELECT ‘drop table ‘ || owner_name || ‘.’ || job_name || ‘;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and job_name NOT LIKE ‘BIN$%’

Get installed sqlpatches in database

— From 12c onward

set lines 2000
select patch_id,status,description from dba_registry_sqlpatch;
(or)
SET LINESIZE 400
COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10
SELECT TO_CHAR(action_time, ‘DD-MON-YYYY HH24:MI:SS’) AS action_time,action, status, description,version,patch_id, bundle_series FROM sys.dba_registry_sqlpatch;

— For 11g and below:
set lines 2000
select * from dba_registry_history;

GET INDEX USAGE

—Index monitoring is required, to find whether indexes are really in use or not. Unused can be dropped to avoid overhead.
— First enable monitoring usage for the indexes.

alter index INDEX_NAME.S_ASSET_TEST monitoring usage;

–Below query to find the index usage:

select * from v$object_usage;

Get sql query to html format

— We can spool output of an sql query to html format:

set pages 5000
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON –
HEAD “EMPLOYEE REPORT –

–
–
” –
BODY “TEXT=’#FF00Ff'” –
TABLE “WIDTH=’90%’ BORDER=’5′”
spool report.html
Select * from scott.emp;
spool off
exit

row_count of all the tables of a schema

select table_name,
to_number(extractvalue(dbms_xmlgen.getXMLtype(‘select /+ PARALLEL(8) */ count() cnt from “&&SCHEMA_NAME”.’||table_name),’/ROWSET/ROW/CNT’))
rows_in_table from dba_TABLES
where owner=’&&SCHEMA_NAME’;

Disable or enable all triggers of schema

BEGIN
FOR j IN (SELECT trig_name
FROM user_triggers) LOOP

EXECUTE IMMEDIATE ‘ALTER TRIGGER ‘ || i.trig_name || ‘ DISABLE’;
END LOOP;
END;
/

Get size of the database

col “Database Size” format a20
col “Free space” format a20
col “Used space” format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”
, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Tag Archives: oracle agent shrink