Databases - youdar/How-to GitHub Wiki

Useful query to fine a field in a data base

select COLUMN_NAME, TABLE_NAME, OWNER
from ALL_TAB_COLUMNS
where lower(COLUMN_NAME) like '%provid%name%';

Vertica

Find open sessions
dbadmin=> select user_name,count(*) as cnt from V_MONITOR.SESSIONS group by user_name order by cnt desc;
Look at queries that created errors

select *
from dc_requests_issued
where
request ilike 'insert%'
and request ilike '%qos.enriched_acme_cdrs%'
order by time desc;

select event_timestamp, error_level, message from error_messages where message ilike '%qos.enriched_acme_cdrs%' ;

Calculate the time a chat bot, that can make any number of parallel chats, is idle
Give data for every chat: chat_start_ts and chat_end_ts

Calculate the number of concurrent chats per minutes of a chat bot, that can make any number of parallel chats
Give data for every chat: chat_start_ts and chat_end_ts

Get Vertica tables size

SELECT
  anchor_table_schema ,
  anchor_table_name ,
  round(SUM (used_bytes) / (1024^3)) AS used_gb
FROM v_monitor.column_storage
GROUP  BY anchor_table_schema , anchor_table_name
ORDER  BY SUM (used_bytes) DESC;

Notes:

Consider use commands like
GREATEST(
      max (engage_start_time) OVER (PARTITION BY agent_name ORDER BY start_time ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING),
      engage_start_time
  ) AS engage_start_time

and 

GREATEST(
      FIRST_VALUE (end_time) OVER (PARTITION BY a.full_name ORDER BY start_time ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING),
      start_time ) AS engage_start_time   

to figure out the time the agent was engage 

Another approach to consider is to do cumulative counting of on going chats,    
n_of_chat += 1 when ever any chat starts    
n_of_chat -= 1 when ever any chat ends

Identifying and closing locked connections

SELECT node_names, object_name, lock_mode, lock_scope 
FROM LOCKS;

SELECT current_statement, last_statement, client_os_user_name, session_id 
FROM sessions
WHERE user_name='<user name>';

SELECT CLOSE_SESSION ( '<session id>' );

Get complete create statement.
SELECT export_objects('', 'database.table_name');.
Connections info:
select * from v_monitor.sessions;

Oracle

Finding information on a table

SELECT *
FROM ALL_OBJECTS 
where lower(object_name) like '%table_name%';

Finding info on a field

select owner, table_name, column_name   
from all_tab_columns   
where lower(column_name) like '%field_name%';

Finding table size

select num_rows from all_tables where lower(table_name) = 'table name';

Sqlite

When running a script from command line:

$ sqlite3 db_name.db ".read 'path/script.txt' "

The script can contain something like:

create table table_name (field_1 text, field_2 text, field_3 text);
.mode tabs
.import data_path table_name
delete from table_name where rowid=1;
create index if not exists idx on table_name(field_1)

Postgres

Install on Linux

Based on: https://www.postgresqltutorial.com/install-postgresql-linux/

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get install wget
sudo apt-get update
sudo apt-get install postgresql

DataGrip remote connection

To setup remote DataGrip connection to postgres on Goolge Compute engine
First in the Google Compute Engine VNC Control, firewall setup, add posrtgres 5432 port, in a similar way to the way port 22 is setup. I followed the instruction in from:BigBinary

Edited: /etc/postgresql/13/main/postgresql.conf
Uncommented #listen_addresses = 'localhost' and changed to listen_addresses = '*'

Added to /etc/postgresql/13/main/pg_hba.conf

host    all             all            0.0.0.0/0                    md5
host    all             all            ::/0                         md5

Restarted postgres: sudo systemctl restart postgresql