Find an Oracle role SQL Code 1select * 2from dba_roles 3where role like '&role' 4/ Sample Oracle Output: 1 2no rows selected 3SQL> Purpose: This code retrieves information about a specific database role, based on a partial or complete role name provided by the user. Breakdown:
select *
:** This clause …
Read MoreRetrieving User Role Privileges in Oracle SQL Code 1select grantee 2, granted_role 3, admin_option 4from dba_role_privs 5where grantee like upper('&username') 6/ Sample Oracle Output: 1 2no rows selected 3SQL> Purpose: This code retrieves the roles granted to a specific user, indicating whether each role …
Read MoreHow large is the Oracle Database? SQL Code 1col "Database Size" format a20 2col "Free space" format a20 3col "Used space" format a20 4select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" 5 ,round(sum(used.bytes) / 1024 / 1024 / 1024 ) - 6 round(free.p / …
Read MoreLock or Unlock an Oracle User in the Database Lock User SQL Code 1alter user <username> account lock; 2/ Unlock User SQL Code 1alter user <username> account unlock; 2/ Sample Oracle Output: 1 2no rows selected 3SQL> Purpose: To lock a specific user account in the Oracle database, preventing any further …
Read MoreShow all Active SQL for Sessions using v$session and v$sqlarea SQL Code` 1set feedback off 2set serveroutput on size 9999 3column username format a20 4column sql_text format a55 word_wrapped 5begin 6 for x in 7 (select username||'('||sid||','||serial#||') ospid = '|| process || 8 ' program = …
Read MoreList all the Oracle Users Account Information for the Oracle Database SQL Code 1set pages 999 lines 100 2col username format a20 3col status format a8 4col tablespace format a20 5col temp_ts format a20 6select username 7, account_status status 8, created 9, default_tablespace tablespace 10, temporary_tablespace temp_ts …
Read MoreDisplay Oracle v$session_longops sessions" SQL Code 1set lines 100 pages 999 2col username format a15 3col message format a40 4col remaining format 9999 5select username 6, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started 7, time_remaining remaining 8, message 9from v$session_longops 10where …
Read MoreDisplay the Oracle database chararter set information SQL Code 1select * from nls_database_parameters 2/ Sample Oracle Output: 1 2SQL> Purpose: To retrieve and display all current National Language Support (NLS) database parameters. These parameters govern how Oracle handles language, territory, character sets, and …
Read MoreShow latent parameter changes Analyzing Modified Oracle Database Parameters for Insights and Optimization Uncover configuration customizations, potential performance bottlenecks, and security considerations by examining modified initialization parameters in Oracle Database. SQL Code 1select name 2, value 3from …
Read MoreShow non-default parameters in the table v$parameter Show non-default parameters in the table v$parameter SQL Code 1set pages 999 lines 100 2col name format a30 3col value format a50 4select name 5, value 6from v$parameter 7where isdefault = 'FALSE' 8and value is not null 9order by name 10/ Sample Oracle …
Read More