sql_text - ORACLEAGENT BLOG

View hidden parameters

Set lines 2000
col NAME for a45
col DESCRIPTION for a100
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ‘_%’ ESCAPE ‘\’
/

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

Get database link details in database

set pagesize 200
set lines 200
col db_link for a19
set long 999
SELECT db_link,
owner_id,
logged_on,
heterogeneous,
open_cursors,
in_transaction,
update_sent
FROM gv$dblink
ORDER BY db_link;

Get DDL of privileges granted to user

set feedback off pages 0 long 900000 lines 20000 pagesize 20000 serveroutput on
accept USERNAME prompt “Enter username :”

execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’SQLTERMINATOR’,true);

SELECT DBMS_METADATA.GET_DDL(‘USER’,username) as script from DBA_USERS where username=’&username’
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,grantee)as script from DBA_SYS_PRIVS where grantee=’&username’ and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,grantee)as script from DBA_ROLE_PRIVS where grantee=’&username’ and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,grantee)as script from DBA_TAB_PRIVS where grantee=’&username’ and rownum=1;

Get timezone information in database

SELECT version FROM v$timezone_file;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;

How to View / Modify AWR retention period

— View current AWR retention period

select retention from dba_hist_wr_control;

— Modify retention period to 7 days and interval to 30 min

select dbms_workload_repository.modify_snapshot_settings (interval => 30, retention => 10080);

NOTE – 7 DAYS = 7243600= 10080 minutes

Check the Characterset info of database

set pagesize 200
set lines 200
select parameter,value from v$nls_parameters where parameter like ‘NLS_%CHAR%’;

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;

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$%’

Tag Archives: sql_text