What is the difference between dba_services and v$services?

Why do we see differences in output between dba_services and v$services view ?

SQL>select name from v$services;

NAME
------------------------------
SYS$BACKGROUND
SYS$USERS
ebs_accnt
accnt
cdbXDB
cdb
accnt_ebs_patch

7 rows selected.



SQL>  select name from dba_services;

NAME
------------------------------
SYS$BACKGROUND
SYS$USERS
accntXDB
cdb
cdbXDB
cdb.skead.no
ebs_PATCH

7 rows selected.

New services were added and few services were stopped recently.

Answer:

The difference is due to the following:

* DBA_SERVICES - This view lists all the services that are in the database. This includes both started and stopped services.
* V$SERVICES - This view lists only the services that were started some time since the instance startup. When you stop a running service, that service will still be listed in this view until the next instance restart.

Oracle states that "The difference between these outputs does not pose any issue."

Source: Differences in outputs between DBA_SERVICES and V$SERVICES view (Doc ID 1496074.1) found at support.oracle.com

What is the catcon.pl script used in a Multitenant Database installation?

What is the catcton.pl script?

From Doc ID 1932340.1:

Oracle has provided script catcon.pl to execute scripts at Container and Pluggable database at once. In a CDB, the catcon.pl script is the best way to run SQL scripts and SQL statements. It can run them in the root and in specified PDBs in the correct order, and it generates log files.

It can be found in the $ORACLE_HOME/rdbms/admin folder.

Example of usage

To execute preupgrd.sql at CDB and all PDBs, copy preupgrd.sql and utlppkf.sql from the software version you want to upgrade to, temporary location (say /u01/oracle) PDB should be in open state before executing script. Its status can be checked using

SYS@cdb> connect / as sysdba
SYS@cdb>SQL>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SALES                          READ WRITE NO

At OS prompt, execute:

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catcon.pl -d /u01/oracle -l /home/oracle/preupgrd_logs -b preupgrade_log_base_name preupgrd.sql

The arguments used are:

-d = directory containing the file to be run (Location of preupgrade script)
-l = directory to use for spool log files
-b = base name for log and spool file names

Not that if neither the -c nor the -C parameter is specified, then catcon.pl runs the script in all containers by default:

-c - container(s) in which to run sqlplus scripts for example, -c 'PDB1 PDB2'
-C - container(s) in which NOT to run sqlplus scripts, i.e. skip all

Sources:
"How to execute sql scripts in Multitenant environment (catcon.pl) (Doc ID 1932340.1)"
"How to recompile invalid objects in all PDBs at the same time (Doc ID 2880643.1)"

Both from Oracle Support.

What is the missing privilege when receiving ORA-01031: insufficient privileges when switching container?

You need to grant the SET CONTAINER system privilege to a common user, in order for the user to be able to switch container.

Eksample: you have a common user used for auditing, C##AUDITADMIN:

CREATE USER c##auditadmin IDENTIFIED BY  CONTAINER=ALL;
GRANT AUDIT_ADMIN TO c##auditadmin CONTAINER=ALL;
GRANT SELECT ANY TABLE TO c##auditadmin CONTAINER=ALL;
GRANT CREATE SESSION TO c##auditadmin CONTAINER=ALL;

You log in with your user to the root container:

sqlplus c##auditadmin@cdb
Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

C##AUDITADMIN@cdb SQL> alter session set container=pdb1;
ERROR:
ORA-01031: insufficient privileges

To grant the required privilege, login as sysdba:

sqlplus / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

Grant the SET CONTAINER privilege:

SYS@cdb SQL> grant set container to c##auditadmin container=all;

Grant succeeded.

Connect with C##AUDITADMIN again, and switch to the PDB1 container within your session:

sqlplus c##auditadmin@cdb

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

C##AUDITADMIN@cdb SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
C##AUDITADMIN@cdb SQL> alter session set container=pdb1;

Session altered.

C##AUDITADMIN@cdb SQL> show con_name

CON_NAME
------------------------------
PDB1

Rename table in postgreSQL: syntax

Rename a table:

alter table myschema.mytable rename to mytable_old;

Change ownership for a table:

alter table myschema.mytable owner to anotherschema;

How to generate insert statements for a table in PostgreSQL

Thanks to Carlos Becker for pointing out the following solution on how to generate insert statments for a table in PostgreSQL

pg_dump mydb -n 'myschema' -a -Fp  -t myschema.mytable --column-inserts > /data1/insert_statements.sql| gzip

How to find the oid for a table in PostgreSQL

Connected to the relevant database using the connection tool of your choice, execute the following to find the oid for a table:

mydb # select 'myschema.mytable'::regclass::oid;
   oid
----------
 19561436
(1 row)

Query to find schema size in PostgreSQL

SELECT pg_size_pretty(sum(pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::bigint) FROM pg_tables 
WHERE schemaname = 'yourschema';