Cheatsheet - Oracle and PostgresSQL

WARNING:
commit differences

by default: SQL> show autocommit autocommit OFF SQL> show exitcommit exitcommit ON WARNING: DDL causes implicit commit

by default: postgres=# \echo :AUTOCOMMIT on corpus=> \set AUTOCOMMIT off ... or start using transactions

Execute SQL commands from a file. (run a script)

bash$ sqlplus corpus/corpus @file.sql ... or ... SQL>@file.sql

bash$ psql corpus -f 1.sql ... or ... psql>\i file.sql

connect to the database

bash$ sqlplus corpus/password ... or ... bash$ sqlplus corpus/password@IP:PORT/corpus.censhare

bash$ psql corpus corpus # db user ... or ... bash$ psql -h IP -p PORT -U corpus -d corpus

show information about the current connection

SQL> show user USER is "CORPUS"

corpus=> \c You are now connected to database "corpus" as user "corpus".

list all tables in the schema

SQL> select table_name from user_tables oder by 1;

corpus=> \dt

list size of the objects

SQL> select sum(bytes)/1024/1024/1024 GB from dba_segments where segment_name='ASSET'; Or you can also

For more information, see the section List DB segments by size in Oracle database performance checks.

corpus=> SELECT pg_size_pretty(pg_relation_size('asset')); corpus=> SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(C.oid) DESC LIMIT 20; ... or see this article

For more information, see the Wikipedia article Disk usage.

show non-default parameters

set pages 999 lines 100 col name format a30 col value format a50 SELECT name, value FROM v$parameter WHERE isdefault = 'FALSE' AND value is not null ORDER by name ;

postgres@PG-host$ egrep -v "^$|^[[:space:]]|^#" $PGDATA/postgresql.conf

list currently connected users

SQL> select distinct username,machine from v$session;

postgres=# select * from pg_stat_activity;

Generate a list for killion user sessions

SELECT 'alter system KILL session '''||sid||','||serial#||'''immediate -- '|| username ||'@'| machine||';'FROM gv$session WHERE  username is not null and type != 'BACKGROUND'  and username <> 'SYS';