KevinAtLeap · August 19, 2023 08:51 · Sep 22, 2022 · Jul 6, 2022 · Jul 6, 2022 · Mar 17, 2022
diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -7,6 +7,16 @@ SQL Cheat Sheet
 
 ## Postgres
 
+### Show slow queries per database
+
+```sql
+SELECT d.datname, qs.* 
+FROM query_store.qs_view qs 
+join pg_database d on d.oid = qs.db_id 
+where qs.start_time > '2022-09-21' 
+order by qs.max_time desc;
+```
+
 ### Show connections
 
 ```sql

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -7,6 +7,14 @@ SQL Cheat Sheet
 
 ## Postgres
 
+### Show connections
+
+```sql
+select datname, state, waiting, count(*)
+from pg_stat_activity group by datname, state, waiting
+order by datname;
+```
+
 ### Show slow queries
 
 ```sql

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -7,6 +7,14 @@ SQL Cheat Sheet
 
 ## Postgres
 
+### Show slow queries
+
+```sql
+SELECT *, now() - pg_stat_activity.query_start AS duration
+FROM pg_stat_activity
+WHERE (now() - pg_stat_activity.query_start) > interval '5 seconds';
+```
+
 ### Cancel or terminate long running jobs
 
 ```

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -7,6 +7,19 @@ SQL Cheat Sheet
 
 ## Postgres
 
+### Cancel or terminate long running jobs
+
+```
+-- get the ones hangin'
+SELECT * FROM pg_stat_activity WHERE state = 'active';
+
+-- attempt cancel
+SELECT pg_cancel_backend(<pid>)
+
+--attemt terminate
+SELECT pg_terminate_backend(<pid>)
+```
+
 ### Remove all tables in a database*
 
 `drop owned by the_user;`

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -11,10 +11,11 @@ SQL Cheat Sheet
 
 `drop owned by the_user;`
 
-### Get size of particular table
+### Get size of particular table/index
 
 ```
 select pg_size_pretty(pg_relation_size('push_messages'));
+select pg_size_pretty(pg_indexes_size('index-navnet'));
 -- or in bytes
 select pg_relation_size('push_messages');
 ```

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -11,6 +11,14 @@ SQL Cheat Sheet
 
 `drop owned by the_user;`
 
+### Get size of particular table
+
+```
+select pg_size_pretty(pg_relation_size('push_messages'));
+-- or in bytes
+select pg_relation_size('push_messages');
+```
+
 ### Get size of databases
 
 ```

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -7,10 +7,20 @@ SQL Cheat Sheet
 
 ## Postgres
 
-*Remove all tables in a database*
+### Remove all tables in a database*
 
 `drop owned by the_user;`
 
+### Get size of databases
+
+```
+SELECT schema_name, pg_size_pretty(sum(table_size)::bigint) FROM (
+  SELECT pg_catalog.pg_namespace.nspname as schema_name, pg_relation_size(pg_catalog.pg_class.oid) as table_size FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
+) t
+GROUP BY schema_name
+ORDER BY schema_name
+```
+
 ## Oracle
 
 ### Restore database

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -5,6 +5,12 @@ SQL Cheat Sheet
 
 # Dialects
 
+## Postgres
+
+*Remove all tables in a database*
+
+`drop owned by the_user;`
+
 ## Oracle
 
 ### Restore database

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -129,4 +129,8 @@ Might throw the following exception:
 
 This is due to the RESTLOGS option is not valid for execution. Run the following command instead:
 
-    alter database open
+    alter database open
+
+# Get all table sizes (actual sizes in megabytes)
+
+http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle
diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -96,6 +96,12 @@ You're now done, and you can use the database as intended.
 
     ALTER USER <username> IDENTIFIED BY "<password>";
 
+*Alter some configurations*
+
+    	alter system set open_cursors = 1000
+    	alter system set sessions = 300 scope = spfile
+    	alter system set processes = 500 scope = spfile (this might need to be added to the init.ora-configuration file [e.g C:\_usr\oracle\odb\product\11.2.0\dbhome_1\dbs])
+
 ### Errors
 
 *Error: ORA-23515*

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -92,6 +92,10 @@ You're now done, and you can use the database as intended.
 
     SELECT view_name, owner FROM sys.all_views where VIEW_NAME LIKE 'SCHEMA_%' ORDER BY owner, view_name;
 
+*Change passwords*
+
+    ALTER USER <username> IDENTIFIED BY "<password>";
+
 ### Errors
 
 *Error: ORA-23515*

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -103,7 +103,7 @@ You're now done, and you can use the database as intended.
 
 Rerun the drop :).
 
-*Error: *
+*Error: ORA-01139*
 
 Executing:
 

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -102,3 +102,21 @@ You're now done, and you can use the database as intended.
     select 'drop materialized view '||owner||'.'||name||' PRESERVE TABLE;' from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name = 'DEV_SOAINFRA');
 
 Rerun the drop :).
+
+*Error: *
+
+Executing:
+
+    alter database open resetlogs;
+
+Might throw the following exception:
+
+    RMAN-00571: ===========================================================
+    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+    RMAN-00571: ===========================================================
+    RMAN-03002: failure of alter db command at 02/18/2016 09:44:18
+    ORA-01139: RESETLOGS option only valid after an incomplete database recovery
+
+This is due to the RESTLOGS option is not valid for execution. Run the following command instead:
+
+    alter database open
diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -76,6 +76,10 @@ You're now done, and you can use the database as intended.
 
     ALTER USER TEST_MDS IDENTIFIED BY oracle;
 
+*Drop existing user*
+
+    DROP USER R1_SOAINFRA (CASCADE);
+
 *Drop existing tablespace*
 
     DROP TABLESPACE R1_SOAINFRA INCLUDING CONTENTS ([AND|KEEP] DATAFILES) CASCADE CONSTRAINTS;

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -80,6 +80,14 @@ You're now done, and you can use the database as intended.
 
     DROP TABLESPACE R1_SOAINFRA INCLUDING CONTENTS ([AND|KEEP] DATAFILES) CASCADE CONSTRAINTS;
 
+*Find all active locks for tables within session*
+
+    select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;
+
+*Show views in the database*
+
+    SELECT view_name, owner FROM sys.all_views where VIEW_NAME LIKE 'SCHEMA_%' ORDER BY owner, view_name;
+
 ### Errors
 
 *Error: ORA-23515*

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -76,6 +76,10 @@ You're now done, and you can use the database as intended.
 
     ALTER USER TEST_MDS IDENTIFIED BY oracle;
 
+*Drop existing tablespace*
+
+    DROP TABLESPACE R1_SOAINFRA INCLUDING CONTENTS ([AND|KEEP] DATAFILES) CASCADE CONSTRAINTS;
+
 ### Errors
 
 *Error: ORA-23515*

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -7,6 +7,33 @@ SQL Cheat Sheet
 
 ## Oracle
 
+### Restore database
+
+Choose the database
+
+    [oracle ~]$ . oraenv
+    ORACLE_SID = [ oracle]? YOURDB
+
+
+Start the Recovery Manager and then run the various commandoes mention below: 
+
+    [oracle ~]$  rman target /
+    Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jun 22 09:16:50 2015
+    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
+    connected to target database: YOURDB (DBID=1234567890)  ----------- Ensure that its the correct database
+
+    RMAN > shutdown immediate
+    RMAN > startup mount
+    RMAN > run {
+    RMAN > set until time "to_date('2015 JUN 17 07:00','YYYY MON DD HH24:MI')"; ------ Change the date
+    RMAN > restore database ;
+    RMAN > recover database ;
+    RMAN >  }
+    RMAN > alter database open resetlogs;
+    RMAN> EXIT
+
+You're now done, and you can use the database as intended.
+
 ### System specifics
 
 *Database uptime*

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -9,6 +9,10 @@ SQL Cheat Sheet
 
 ### System specifics
 
+*Database uptime*
+
+    SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.v_$instance;
+
 *Show service-name*
 
     show parameter service_name

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -9,6 +9,10 @@ SQL Cheat Sheet
 
 ### System specifics
 
+*Show service-name*
+
+    show parameter service_name
+
 *Connect to specific database*
 
     sqlplus 'system/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.1.1.1)(Port=1521))(CONNECT_DATA=(SID=THE_SID)))'

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -36,6 +36,10 @@ SQL Cheat Sheet
 *Show all foreign keys*
 
     select * from all_constraints where r_constraint_name in (select constraint_name from all_constraints where table_name='RECON_EVENTS');
+
+*Change user/schema password*
+
+    ALTER USER TEST_MDS IDENTIFIED BY oracle;
 
 ### Errors
 

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -7,9 +7,7 @@ SQL Cheat Sheet
 
 ## Oracle
 
-*Show all foreign keys*
-
-    select * from all_constraints where r_constraint_name in (select constraint_name from all_constraints where table_name='RECON_EVENTS');
+### System specifics
 
 *Connect to specific database*
 
@@ -18,6 +16,12 @@ SQL Cheat Sheet
 *Show all databases*
 
     SELECT TABLESPACE_NAME FROM USER_TABLESPACES;
+
+*Show all tablespaces*
+
+    SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
+    FROM dba_data_files
+    ORDER BY 1;
 
 *Accidentaly deleted all databases?*
 
@@ -28,6 +32,12 @@ SQL Cheat Sheet
 *Display whatever version is in use*
 
     SELECT * FROM V$VERSION
+
+*Show all foreign keys*
+
+    select * from all_constraints where r_constraint_name in (select constraint_name from all_constraints where table_name='RECON_EVENTS');
+
+### Errors
 
 *Error: ORA-23515*
 

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -25,11 +25,15 @@ SQL Cheat Sheet
     startup mount
     alter database datafile 'C:\_USR\ORACLE\ODB\ORADATA\ORCL\<db-filename>.DBF' offline drop;
 
+*Display whatever version is in use*
+
+    SELECT * FROM V$VERSION
+
 *Error: ORA-23515*
 
     --This provides the entitled error: 
     drop tablespace DEV_SOAINFRA INCLUDING CONTENTS;
     --Fix this by doing this:
     select 'drop materialized view '||owner||'.'||name||' PRESERVE TABLE;' from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name = 'DEV_SOAINFRA');
 
-Rerun the drop :).
+Rerun the drop :).
diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -7,6 +7,10 @@ SQL Cheat Sheet
 
 ## Oracle
 
+*Show all foreign keys*
+
+    select * from all_constraints where r_constraint_name in (select constraint_name from all_constraints where table_name='RECON_EVENTS');
+
 *Connect to specific database*
 
     sqlplus 'system/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.1.1.1)(Port=1521))(CONNECT_DATA=(SID=THE_SID)))'

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -21,7 +21,7 @@ SQL Cheat Sheet
     startup mount
     alter database datafile 'C:\_USR\ORACLE\ODB\ORADATA\ORCL\<db-filename>.DBF' offline drop;
 
-*Error: *
+*Error: ORA-23515*
 
     --This provides the entitled error: 
     drop tablespace DEV_SOAINFRA INCLUDING CONTENTS;

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -21,3 +21,11 @@ SQL Cheat Sheet
     startup mount
     alter database datafile 'C:\_USR\ORACLE\ODB\ORADATA\ORCL\<db-filename>.DBF' offline drop;
 
+*Error: *
+
+    --This provides the entitled error: 
+    drop tablespace DEV_SOAINFRA INCLUDING CONTENTS;
+    --Fix this by doing this:
+    select 'drop materialized view '||owner||'.'||name||' PRESERVE TABLE;' from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name = 'DEV_SOAINFRA');
+
+Rerun the drop :).
diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -17,5 +17,7 @@ SQL Cheat Sheet
 
 *Accidentaly deleted all databases?*
 
+    connect sys/vegard11 as sysdba
+    startup mount
     alter database datafile 'C:\_USR\ORACLE\ODB\ORADATA\ORCL\<db-filename>.DBF' offline drop;
 
diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -13,4 +13,9 @@ SQL Cheat Sheet
 
 *Show all databases*
 
-    SELECT TABLESPACE_NAME FROM USER_TABLESPACES;
+    SELECT TABLESPACE_NAME FROM USER_TABLESPACES;
+
+*Accidentaly deleted all databases?*
+
+    alter database datafile 'C:\_USR\ORACLE\ODB\ORADATA\ORCL\<db-filename>.DBF' offline drop;
+
diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -9,7 +9,7 @@ SQL Cheat Sheet
 
 *Connect to specific database*
 
-    sqlplus 'system/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.1.1.1)(Port=1521))(CONNECT_DATA=(SID=IDMDB3)))'
+    sqlplus 'system/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.1.1.1)(Port=1521))(CONNECT_DATA=(SID=THE_SID)))'
 
 *Show all databases*
 

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -9,7 +9,7 @@ SQL Cheat Sheet
 
 *Connect to specific database*
 
-    sqlplus 'system/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.150.1.60)(Port=1521))(CONNECT_DATA=(SID=IDMDB3)))'
+    sqlplus 'system/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.1.1.1)(Port=1521))(CONNECT_DATA=(SID=IDMDB3)))'
 
 *Show all databases*
 

diff --git a/sql-cheat-sheet.md b/sql-cheat-sheet.md
@@ -7,6 +7,10 @@ SQL Cheat Sheet
 
 ## Oracle
 
-Show all databases
+*Connect to specific database*
+
+    sqlplus 'system/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.150.1.60)(Port=1521))(CONNECT_DATA=(SID=IDMDB3)))'
+
+*Show all databases*
 
     SELECT TABLESPACE_NAME FROM USER_TABLESPACES;