SQL Cheat Sheet

Information

Dialects

Postgres

Misc grants

SELECT * FROM information_schema.role_table_grants;
GRANT USAGE ON SCHEMA "<schema>" to "<user>";
GRANT SELECT ON ALL TABLES IN SCHEMA "<schema>" TO "<user>";

Show slow queries per database

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

select datname, state, waiting, count(*)
from pg_stat_activity group by datname, state, waiting
order by datname;

Show slow queries

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

-- 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;

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');

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

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

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

Connect to specific database

sqlplus 'system/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.1.1.1)(Port=1521))(CONNECT_DATA=(SID=THE_SID)))'

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?

connect sys/vegard11 as sysdba
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

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;

Drop existing user

DROP USER R1_SOAINFRA (CASCADE);

Drop existing tablespace

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;

Change passwords

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

--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 :).

Error: ORA-01139

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

Get all table sizes (actual sizes in megabytes)

http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle

vegaasen/sql-cheat-sheet.md