Oracle views (dba_objects, dba_segments, ...)

[PAscal L]

Hi,

Maybe some of you are also coming from Oracle and will try to find equivalent informations in PostgreSQL ...

On my side as a beginer I have noted:
- V$PARAMETERS --> pg_settings
- V$SESSION join V$SQL (to monitor current SQL) --> pg_stat_activity
- V$SQL --> pg_stat_statements (I would have expected something equivalent to FIRST_LOAD_TIME but didn't found it)
- DBA_OBJECTS (One view for all objects ...) --> not found so I try with

create or replace view oracle.dba_objects as
SELECT
    pg_namespace.nspname as schema,
    pg_class.relname object_name,
              CASE WHEN pg_class.relkind = 'r' THEN CAST('TABLE' AS VARCHAR(18))
                WHEN pg_class.relkind = 'i' THEN CAST('INDEX' AS VARCHAR(18))
                WHEN pg_class.relkind = 'f' THEN CAST('FOREIGN TABLE' AS VARCHAR(18))
                WHEN pg_class.relkind = 'S' THEN CAST('SEQUENCE' AS VARCHAR(18))
                WHEN pg_class.relkind = 's' THEN CAST('SPECIAL' AS VARCHAR(18))
                WHEN pg_class.relkind = 't' THEN CAST('TOAST TABLE' AS VARCHAR(18))
                WHEN pg_class.relkind = 'v' THEN CAST('VIEW' AS VARCHAR(18))
                    ELSE CAST(pg_class.relkind AS VARCHAR(18))
          END AS object_type,
    pg_class.oid oid
FROM
    pg_class 
    INNER JOIN pg_namespace
     ON pg_class.relnamespace = pg_namespace.oid
UNION ALL
SELECT
    pg_namespace.nspname,
    pg_proc.proname,
      CAST('FUNCTION' AS VARCHAR(18)),
    pg_proc.oid
FROM
    pg_proc 
    INNER JOIN pg_namespace 
     ON pg_proc.pronamespace = pg_namespace.oid
UNION ALL
SELECT
    pg_namespace.nspname AS owner,
    pg_trigger.tgname,
    CAST( 'TRIGGER' AS VARCHAR( 18 ) ),
    pg_trigger.oid
FROM
    pg_class
    INNER JOIN pg_namespace
     ON pg_class.relnamespace = pg_namespace.oid
    INNER JOIN pg_trigger
     ON pg_class.oid = pg_trigger.tgrelid
WHERE
    pg_trigger.tgconstraint = 0
;


- DBA_SEGMENTS --> not found so I try with

create or replace view oracle.dba_segments
as
SELECT
    pg_namespace.nspname AS schema,
    pg_class.relname AS segment_name,
    CASE
        WHEN pg_class.relkind = 'r' THEN CAST( 'TABLE' AS VARCHAR( 18 ) )
        WHEN pg_class.relkind = 'i' THEN CAST( 'INDEX' AS VARCHAR( 18 ) )
        WHEN pg_class.relkind = 'f' THEN CAST( 'FOREIGN TABLE' AS VARCHAR( 18 ) )
        WHEN pg_class.relkind = 'S' THEN CAST( 'SEQUENCE' AS VARCHAR( 18 ) )
        WHEN pg_class.relkind = 's' THEN CAST( 'SPECIAL' AS VARCHAR( 18 ) )
        WHEN pg_class.relkind = 't' THEN CAST( 'TOAST TABLE' AS VARCHAR( 18 ) )
        WHEN pg_class.relkind = 'v' THEN CAST( 'VIEW' AS VARCHAR( 18 ) )
        ELSE CAST( pg_class.relkind AS VARCHAR( 18 ) )
    END AS segment_type,
    pg_relation_size( pg_class.oid ) BYTES,
--    pg_relation_size( pg_class.oid ,'main') AS main_BYTES,
--    pg_relation_size( pg_class.oid ,'fsm') AS fsm_BYTES,
--    pg_relation_size( pg_class.oid ,'vm') AS vm_BYTES,
--    pg_relation_size( pg_class.oid ,'init') AS init_BYTES,
    pg_size_pretty( pg_relation_size( pg_class.oid ) ) AS "pretty_size",
    pg_tablespace.spcname AS tablespace_name,
    pg_class.oid AS oid
FROM
    pg_class
    INNER JOIN pg_namespace
     ON pg_class.relnamespace = pg_namespace.oid
    LEFT OUTER JOIN pg_tablespace 
     ON pg_class.reltablespace = pg_tablespace.oid
where
    pg_class.relkind not in ('f','S','v')
;

That's all for today, I hope it will help other Oracle dba

Regards
PAscal

[PAscal L]

All_objets bas been added un la test release: thanks !

Wouldn't dba_segments be added too ?

Regards
PAscal

[Pavel Stehule]

2017-10-07 13:19 GMT+02:00 PAscal L <legrand...@hotmail.com>:

All_objets bas been added un la test release: thanks !

Wouldn't dba_segments be added too ?

if you send me the query, then I can push it there

Regards
PAscal

--
You received this message because you are subscribed to the Google Groups "Better Oracle functions support" group.
To post to this group, send email to orafce-general@googlegroups.com.
Visit this group at https://groups.google.com/group/orafce-general.

[PAscal L]

the query is in the initial post

[Pavel Stehule]

Hi

I did it - new version contains the dba_segments view

Regards

Pavel

2017-10-07 14:59 GMT+02:00 PAscal L <legrand...@hotmail.com>:

the query is in the initial post

[PAscal L]

Thanks