Cheatsheet - Oracle and PostgresSQL
Differences in daily work with Oracle and PostgreSQL databases.
Task | Oracle | PostgreSQL |
---|---|---|
WARNING: | 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'; |