Oracle vs Amazon Redshift – The basic commands

Here are the most often used command on Oracle and their equivalent in Redshift

1. Show Schema

Oracle Way:

1 2 3 4

SELECT username FROM all_users;

Redshift Way:

1 2	SELECT * FROM pg_namespace;

2. Describe a table and see the field names, types, encoding etc.

Oracle Way:

1 2

Describe SchemName.TableName

Redshift Way:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

SELECT DISTINCT n.nspname AS schemaname ,c.relname AS tablename ,a.attname AS COLUMN ,a.attnum AS column_position ,pg_catalog.format_type(a.atttypid, a.atttypmod) AS TYPE ,pg_catalog.format_encoding(a.attencodingtype) AS encoding ,a.attisdistkey AS distkey ,a.attsortkeyord AS sortkey ,a.attnotnull AS notnull ,a.attencodingtype AS compression ,con.conkey AS primary_key_column_ids ,con.contype AS con_type FROM pg_catalog.pg_namespace n ,pg_catalog.pg_class c ,pg_catalog.pg_attribute a ,pg_constraint con ,pg_catalog.pg_stats stats WHERE n.oid = c.relnamespace AND c.oid = a.attrelid AND a.attnum > 0 AND c.relname NOT LIKE '%pkey' AND lower(c.relname) = '' AND n.nspname = '' AND c.oid = con.conrelid(+) ORDER BY A.ATTNUM ;

3. Find Disk Usage Per Table 

Oracle Way:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

SELECT owner, table_name, TRUNC(SUM(bytes)/1024/1024) Meg FROM ( SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type = 'TABLE' UNION ALL SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner        = i.owner AND s.segment_type = 'INDEX' UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name AND s.owner        = l.owner AND s.segment_type = 'LOBSEGMENT' UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name AND s.owner        = l.owner AND s.segment_type = 'LOBINDEX' ) WHERE owner IN UPPER('BIC_DDL') -- PUT YOUR SCHEMANAME HERE GROUP BY table_name, owner ORDER BY SUM(bytes) DESC

Redshift Way :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

SELECT DISTINCT n.nspname AS schemaname  ,c.relname AS tablename  ,a.attname AS COLUMN  ,a.attnum AS column_position  ,pg_catalog.format_type(a.atttypid, a.atttypmod) AS TYPE  ,pg_catalog.format_encoding(a.attencodingtype) AS encoding   ,a.attisdistkey AS distkey  ,a.attsortkeyord AS sortkey  ,a.attnotnull AS notnull  ,a.attencodingtype AS compression  ,con.conkey AS primary_key_column_ids  ,con.contype AS con_type FROM pg_catalog.pg_namespace n  ,pg_catalog.pg_class c  ,pg_catalog.pg_attribute a  ,pg_constraint con  ,pg_catalog.pg_stats stats WHERE n.oid = c.relnamespace  AND c.oid = a.attrelid  AND a.attnum > 0  AND c.relname NOT LIKE '%pkey'  AND lower(c.relname) = ''  AND n.nspname = ''  AND c.oid = con.conrelid(+) ORDER BY A.ATTNUM ;