Groovy DBA

  SELECT TABSCHEMA, TABNAME, SELECTAUTH, INSERTAUTH, UPDATEAUTH, DELETEAUTH FROM SYSCAT.TABAUTH WHERE GRANTEE = 'YOUR_ROLE_NAME' AND GRANTEETYPE = 'R' ; SELECT      TABSCHEMA,      TABNAME,      SELECTAUTH,      INSERTAUTH,      UPDATEAUTH,      DELETEAUTH FROM SYSCAT.TABAUTH WHERE GRANTEE = 'YOUR_ROLE_NAME'    AND GRANTEETYPE = 'R';

  [root@tmwuvdb01 ~]# passwd -S wcadmin wcadmin LK 2011-02-10 0 99999 7 -1 (Password locked.)

  SELECT      TRIGGER_SCHEMA,      TRIGGER_NAME,      EVENT_OBJECT_TABLE AS 'TABLE_NAME',      DEFINER  FROM information_schema.TRIGGERS  WHERE TRIGGER_NAME = 'after_mao_inventory_insert'; SELECT      TRIGGER_SCHEMA,      TRIGGER_NAME,      DEFINER  FROM information_schema.TRIGGERS  WHERE TRIGGER_NAME = 'after_mao_inventory_insert';

 SELECT GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE  FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES  WHERE TABLE_NAME = 'MAO_INVENTORY_TRACKING';

 > net user /domain username   H:\>net user /domain gm15 The request will be processed at a domain controller for domain tmw.com. User name                    GM15 Full Name                    Murugappan, Gillian  (GM15) Comment User's comment Country/region code          000 (System Default) Account active               Yes Account expires              Never Password last set            5/25/2026 7:27:09 PM Password expires             8/23/2026 7:27:09 PM Password changeable          5/26/2026 7:27:09 PM Password required            Yes User may change password     Yes Workstations allowed         All Logon scr...

  select table_schema as database_name,        table_name,        round( (data_length + index_length) / 1024 / 1024, 2)  as total_size,        round( (data_length) / 1024 / 1024, 2)  as data_size,        round( (index_length) / 1024 / 1024, 2)  as index_size from information_schema.tables where table_schema not in ('information_schema', 'mysql',                            'performance_schema' ,'sys')       and table_type = 'BASE TABLE'       -- and table_schema = 'your database name' order by total_size desc limit 10;

  https://oem15db601.tmw.com:7803/em sysman / oms_newLife3

 oracle-tmwoem01:oemprod:/usr/opt/app/oracle/bea/oms11g/opmn>cd $OMS_HOME oracle-tmwoem01:oemprod:/usr/opt/app/oracle/bea/oms11g>cd bin To get a status: >./emctl status oms -details Oracle Enterprise Manager Cloud Control 12c Release 5 Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved. Enter Enterprise Manager Root (SYSMAN) Password : Console Server Host : oem12tstdb601 HTTP Console Port : 7788 HTTPS Console Port : 7802 HTTP Upload Port : 4889 HTTPS Upload Port : 4903 EM Instance Home : /usr/opt/app/oracle/product/gc_inst/em/EMGC_OMS1 OMS Log Directory Location : /usr/opt/app/oracle/product/gc_inst/em/EMGC_OMS1/sysman/log OMS is not configured with SLB or virtual hostname Agent Upload is locked. OMS Console is locked. Active CA ID: 1 Console URL: https://oem12tstdb601:7802/em Upload URL: https://oem12tstdb601:4903/empbs/upload WLS Domain Information Domain Name : GCDomain Admin Server Host : oem12tstdb601 Admin Server HTTPS Port: 7102 Admin Server is RUNNING...

 Go to the PeopleTools Options page:  

  To check multiple variables at once: Sql Copy code WHERE gv.VARIABLE_NAME IN ( 'sql_mode' , 'max_connections' , 'time_zone' ); To search by pattern: Sql Copy code WHERE gv.VARIABLE_NAME LIKE 'innodb%' ;

  here’s a single MySQL query that shows a system parameter’s GLOBAL and SESSION values side-by-side. You just replace 'sql_mode' with the variable you want to check. Sql Copy code SELECT gv.VARIABLE_NAME, gv.VARIABLE_VALUE AS GLOBAL_VALUE, sv.VARIABLE_VALUE AS SESSION_VALUE FROM performance_schema.global_variables gv JOIN performance_schema.session_variables sv ON gv.VARIABLE_NAME = sv.VARIABLE_NAME WHERE gv.VARIABLE_NAME = 'sql_mode' ;

  In MySQL, you can view system parameters (also called system variables ) using the SHOW VARIABLES statement or by querying the performance_schema tables. Here are the main ways: 1. Show all system parameters Sql Copy code SHOW VARIABLES; This lists every system variable and its current value for your session. 2. Show specific parameters by name pattern Sql Copy code SHOW VARIABLES LIKE 'max_connections' ; SHOW VARIABLES LIKE 'innodb%' ; % is a wildcard for matching multiple variables. 3. Show global vs session values Sql Copy code -- Global values (affect all connections) SHOW GLOBAL VARIABLES; -- Session values (specific to your current connection) SHOW SESSION VARIABLES; 4. Using performance_schema Sql Copy code SELECT * FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'max_connections' ; This is useful for more complex filtering or joining with other metadata. ✅ Tip: If you want to see both the current session ...

 SELECT      r.trx_id AS waiting_trx_id,     r.trx_mysql_thread_id AS waiting_thread,     r.trx_query AS waiting_query,     b.trx_id AS blocking_trx_id,     b.trx_mysql_thread_id AS blocking_thread,     b.trx_query AS blocking_query FROM performance_schema.data_lock_waits w JOIN information_schema.innodb_trx b      ON w.blocking_engine_transaction_id = b.trx_id JOIN information_schema.innodb_trx r      ON w.requesting_engine_transaction_id = r.trx_id;                    SELECT      r.trx_id AS waiting_trx_id,     r.trx_mysql_thread_id AS waiting_thread,     r.trx_query AS waiting_query,     b.trx_id AS blocking_trx_id,     b.trx_mysql_thread_id AS blocking_thread,     b.trx_query AS blocking_query,     b.trx_started as blocking_start_time FROM performance_schem...

       

 SELECT     r.rolname AS role_name,     p.proname AS procedure_name,     n.nspname AS schema_name,     pg_get_function_identity_arguments(p.oid) AS arguments FROM     pg_proc p JOIN     pg_namespace n ON n.oid = p.pronamespace JOIN     pg_roles r ON has_function_privilege(r.rolname, p.oid, 'EXECUTE') ORDER BY     procedure_name, role_name;

 -- Replace 'schema_name', 'procedure_name', and argument types as needed SELECT     r.rolname AS role_name,     p.proname AS procedure_name,     n.nspname AS schema_name,     pg_get_function_identity_arguments(p.oid) AS arguments,     has_function_privilege(r.rolname, p.oid, 'EXECUTE') AS can_execute FROM     pg_proc p JOIN     pg_namespace n ON n.oid = p.pronamespace JOIN     pg_roles r ON has_function_privilege(r.rolname, p.oid, 'EXECUTE') WHERE     n.nspname = 'public'  -- schema name     AND p.proname = 'my_procedure'  -- procedure name     -- Optional: match argument types if overloaded     -- AND pg_get_function_identity_arguments(p.oid) = 'integer, text' ORDER BY     role_name; How it works: pg_proc — stores all functions and procedures. pg_namespace — stores schema names. pg_roles — stores all roles (users/groups). has_function_priv...

 Safe SQL to Cleanup SYSAUX Advisor Data SQL> SET SERVEROUTPUT ON DECLARE     CURSOR c_tasks IS         SELECT task_name         FROM   dba_advisor_log  -- Use user_advisor_log if you don't have DBA privileges         WHERE  owner = USER;    -- Restrict to current schema BEGIN     FOR rec IN c_tasks LOOP         BEGIN             DBMS_ADVISOR.DELETE_TASK(rec.task_name);             DBMS_OUTPUT.PUT_LINE('Deleted task: ' || rec.task_name);         EXCEPTION             WHEN OTHERS THEN                 DBMS_OUTPUT.PUT_LINE('Failed to delete task: ' || rec.task_name ||                                      ' - ' || ...

 set linesize 200 COLUMN occupant_name FORMAT A30 COLUMN schema_name FORMAT A20 COLUMN space_usage_mb FORMAT 999,999,999 SELECT occupant_name,        schema_name,        space_usage_kbytes / 1024 AS space_usage_mb,        move_procedure FROM   v$sysaux_occupants ORDER  BY space_usage_kbytes DESC; SM/ADVISOR                     SYS                          12,796

SELECT distinct information_schema . role_table_grants . grantee , information_schema . role_table_grants . privilege_type , information_schema . table_privileges . table_name FROM information_schema . role_table_grants , information_schema . table_privileges WHERE information_schema . role_table_grants . grantee = 'eisuser' and information_schema . role_table_grants . grantee = information_schema . table_privileges . grantee ;

 SHOW VARIABLES LIKE 'wait_timeout'; -- Timeout for interactive connections SHOW VARIABLES LIKE 'interactive_timeout'; -- Timeout for initial connection attempts SHOW VARIABLES LIKE 'connect_timeout'; SHOW VARIABLES LIKE '%timeout%' ; connect_timeout 60 delayed_insert_timeout 300 have_statement_timeout YES innodb_flush_log_at_timeout 1.000000 innodb_lock_wait_timeout 50 innodb_rollback_on_timeout OFF interactive_timeout 28800 lock_wait_timeout 31536000 net_read_timeout 30 net_write_timeout 60 replica_net_timeout 30 rpl_stop_replica_timeout 31536000 rpl_stop_slave_timeout 31536000 slave_net_timeout 30 ssl_session_cache_timeout 300 thread_pool_idle_timeout 60 wait_timeout 28800 The most common timeout-related variables are: wait_timeout  – Timeout for non-interactive connections (in seconds). interactive_timeout  – Timeout for interactive sessions (e.g., MySQL CLI). connect_timeout  – Timeout for initial connection attempts. net_read_timeout  – Timeout f...