redsfyre

select index_name, column_name from user_ind_columns where table_name='&tablename';
-- Enter table name after running this query

-- dba_ind_columns : This is to used if login with user having DBA role
-- all_ind_columns : This is to used if login with user having normal role
-- user_ind_columns : This is to used if login with user having normal role

-- OK nvm, this one is better;

set pagesize 50000 verify off echo off

redsfyre

-- Gets tables with their auto generated sequences
select table_name,data_default from user_tab_cols where identity_column='YES';

-- Gets table and its ID column details, like sequence name if its auto generated sequence
COLUMN table_name FORMAT A50
COLUMN column_name FORMAT A15
COLUMN sequence_name format A15          
COLUMN generation_type FORMAT A10          
COLUMN identity_options FORMAT A75         
                                           

redsfyre

#!/bin/bash
# Copyright (C) 2015, Wazuh Inc.
# Created by Wazuh, Inc. <[email protected]>.
# This program is free software; you can redistribute it and/or modify it under the terms of GPLv2

WPYTHON_BIN="framework/python/bin/python3"

SCRIPT_PATH_NAME="$0"

DIR_NAME="$(cd $(dirname ${SCRIPT_PATH_NAME}); pwd -P)"

redsfyre

SELECT
    pt.tablename AS TableName
    ,t.indexname AS IndexName
    ,to_char(pc.reltuples, '999,999,999,999') AS TotalRows
    ,pg_size_pretty(pg_relation_size(quote_ident(pt.tablename)::text)) AS TableSize
    ,pg_size_pretty(pg_relation_size(quote_ident(t.indexrelname)::text)) AS IndexSize
    ,to_char(t.idx_scan, '999,999,999,999') AS TotalNumberOfScan
    ,to_char(t.idx_tup_read, '999,999,999,999') AS TotalTupleRead
    ,to_char(t.idx_tup_fetch, '999,999,999,999') AS TotalTupleFetched
FROM pg_tables AS pt

redsfyre

CREATE OR REPLACE VIEW public.active_locks AS 
 SELECT t.schemaname,
    t.relname,
    l.locktype,
    l.page,
    l.virtualtransaction,
    l.pid,
    l.mode,
    l.granted
   FROM pg_locks l

redsfyre

-- Get long queries
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  state,
  datname,
  query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 seconds' and state != 'idle' order by duration desc;