In the page Prices and Donations, you can find how to have the solutions of the quizzes.			Nella pagina Prezzi Quiz potete vedere come avere le soluzioni dei quiz.

                     indexdef                      
 -------------------------------------------------------------------------
 CREATE INDEX t1_idx ON public.t1 USING btree (c1);
 CREATE INDEX model_gin ON public.dbelement USING gin (data jsonb_path_ops);

  SELECT col_1, count(*)
  FROM jou
  WHERE to_char(dt_insert,'yyyymm') >= '201910'
  GROUP BY col_1;

  SELECT col_1, count(*)
  FROM jou
  WHERE dt_insert >= (date_trunc('month', current_date))
  GROUP BY col_1;

  $ ps -ef |grep "bin/postg"
  postgres  1003   1  0 14:42 ?   00:00:00 /usr/lib/postgresql/11/bin/postgres ...
  postgres  3866   1  0 15:34 ?   00:00:00 /usr/lib/postgresql/10/bin/postgres ...

 ls -ld /var/lib/postgresql/10/main/base/13014/19441
 ls -ld /var/lib/postgresql/10/main/base/13014/19805
 ls -ld /var/lib/postgresql/10/main/base/13014/19797
 ls -ld /var/lib/postgresql/10/main/base/13014/19800

  datname   | usename  | count 
 -----------+----------+-------
 db_lassi   | lassi    |    10
 db_lassi   | loris    |     4
 db_pippo   | lassi    |     1
            | postgres |     1

  postgres=# SELECT * FROM pg_tablespace;
    spcname   | spcowner | spcacl | spcoptions 
  ------------+----------+--------+------------
   pg_default |       10 |        | 
   pg_global  |       10 |        | 

postgres=# show data_directory;
	       data_directory
	-----------------------------
	 /var/lib/postgresql/10/main

Then in this case, the data files will be inside the directories in:

	/var/lib/postgresql/10/main/base/

 postgres=# \l db_test_def
         List of databases
    Name     |     Owner     | ...
 ----------------------------+ ...
 db_test_def | user_test_def | ...

 1. You want to upgrade it to the latest version (2.6).
    How can you do it?

 2. You have a new postgres cluster database, version 11, to be backupped (until now, you
    have backupped only postgresql 10 databases).

    You are not able to backup the new cluster database and in the barman logs there is:
     2019-03-07 09:51:01,825 [30468] barman.server ERROR: ArchiverFailure:pg_receivexlog
     not present in $PATH

    Which can be the problem and How can you backup it?

 CREATE TABLE t_order
 (id VARCHAR(6),
  description VARCHAR(100));

 INSERT INTO t_order VALUES ('1','product 1');
 INSERT INTO t_order VALUES ('11','product 11');
 INSERT INTO t_order VALUES ('2','product 2');

  id | description 
 ----+-------------
  1  | product 1
  2  | product 2
  11 | product 11

 SELECT * 
 FROM t_order
 ORDER BY id::INT;

 # \dt
             List of relations
  Schema |   Name     | Type  |  Owner   
 --------+------------+-------+----------
  public | tb         | table | postgres
  public | tb_2018_4q | table | postgres
  public | tb_2019_1q | table | postgres

 # \d+ tb
                                                           Table "public.tb"
  Column |            Type             | ... | Nullable |             Default             
 --------+-----------------------------+-----+----------+---------------------------------
  id     | integer                     |     | not null | nextval('tb_id_seq'::regclass)
  dt_ins | timestamp without time zone |     | not null |                                 
  code   | character varying(255)      |     | not null |                                 
  detail | character varying(255)      |     |          |                                 
 Partition key: RANGE (dt_ins)
 Partitions: tb_2018_4q FOR VALUES FROM ('2018-10-01 00:00:00') TO ('2019-01-01 00:00:00'),
             tb_2019_1q FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-04-01 00:00:00')

                    Table "public.t1"
  Column |            Type             | Collation | Nullable | Default 
 --------+-----------------------------+-----------+----------+---------
  id     | character varying(64)       |           | not null | 
  data   | jsonb                       |           | not null | 
  ts     | timestamp without time zone |           | not null |

  postgres=# \d t1
                                        Table "public.t1"
     Column    |         Type          | Collation | Nullable |            Default             
  -------------+-----------------------+-----------+----------+--------------------------------
   id          | integer               |           | not null | nextval('t1_id_seq'::regclass)
   description | character varying(10) |           |          | 
  Indexes:
      "t1_pkey" PRIMARY KEY, btree (id)

  Using the psql comand \du

  Example:

   postgres=# \du
		        List of roles
           Role name |   Attributes   | Member of 
	-------------+----------------+-----------
	 aitan       |                | {}
	 backup      | Replication    | {}
	 boiae       |                | {}
	 lassi       | Superuser      | {}

Or using this SELECT on a system table called PG_USER (it is shared across all databases of
a cluster):

  Example:

   postgres=# select usename from pg_user;
    datname  
  -----------
   aitan
   backup
   boiae
   lassi

   If you want more information regarding these users, use this query:

     select * from pg_user;

  Using the psql comand \list or \l

  Example:

   postgres=# \l
                                    List of databases
     Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
  -----------+----------+----------+-------------+-------------+-----------------------
   db_loris  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
   postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
   template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
   template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
   (4 rows)

Or using this SELECT on a system table called PG_DATABASE (it is shared across all databases
of a cluster):

  Example:

   postgres=# select datname from pg_database;
    datname  
  -----------
   db_loris
   postgres
   template0
   template1
   (4 rows)

   If you want more information regarding these databases, launch this:

     select * from pg_database;

  "customerinfo": {
    "cod": "XWZLUX18S20D510K",

                 Table "public.t_2"
  Column |            Type             | Collation | Nullable | Default 
 --------+-----------------------------+-----------+----------+---------
  id     | character varying(64)       |           | not null | 
  data   | jsonb                       |           | not null | 
  time   | timestamp without time zone |           | not null | now()

  SQL > 
   1  CREATE OR REPLACE PROCEDURE pr_error
   2  BEGIN
   3   null;
   4* END;

  Warning: Procedure created with compilation errors.

  SQL > show error
  Errori in PROCEDURE PR_ERROR:

  LINE/COL ERROR
  -------- -----------------------------------------------------------------
  2/1      PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
           ( ; is
           with authid as cluster compress order using compiled wrapped
           external deterministic parallel_enable pipelined result_cache
           The symbol "is" was substituted for "BEGIN" to continue.

  select *
  from dba_tables
  where table_name = 't1';

  select *
  from dba_tables
  where table_name = 'T1';

  DAY         00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17
  ----------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --
  11/25, SAT  0   0   0   1   0   0   0   1   2   0   0   0   5   1   1   0   1   1
  11/26, SUN  0   0   0   15  0   0   0   0   4   1   6   5   5   2   0   1   6   3
  11/27, MON  1   0   0   20  0   0   0   0   1   4   6   1   3   9   0   5   5   1
  11/28, TUE  1   0   0   30  0   0   0   0   2   1   2   0   7   4   0   1   1   0
  11/29, WED  1   0   0   12  0   0   0   0   0   0   0   0   0   1   0   0   4   0
  11/30, THU  1   0   0   10  0   0   0   0   0   0   0   0   0   0   0   0   2   0
  12/01, FRI  0   0   0   3   0   0   0   0   0   1   9   4   1   0   1   20  10  0
  12/02, SAT  0   0   0   2   2   0   0   2   1   0   5   1   1   7   9   1   0   0

  ALTER INDEX loris.pk_emp
  MONITORING USAGE;

 USERNAME    ACCOUNT_STATUS    CREATED
 ----------- ----------------- ---------
 SYS         OPEN              30-MAR-10
 SCOTT       OPEN              30-MAR-10
 LORIS       OPEN              04-AUG-14
 SYSTEM      EXPIRED(GRACE)    30-MAR-10
 OUTLN       EXPIRED & LOCKED  30-MAR-10
 ...

  SELECT username, account_status, created
  FROM dba_users;

 SELECT owner, table_name
 FROM dba_tables
 WHERE table_name LIKE 'ICOLD%'
    OR table_name LIKE '%ROLE_DATA%';

  SELECT owner, table_name
  FROM dba_tables
  WHERE REGEXP_LIKE (table_name, '^ICOLD|ROLE_DATA');

  SELECT table_name, last_analyzed
  FROM dba_tables
  WHERE table_name = 'T1' AND owner = 'LASSI';

  exec dbms_stats.gather_table_stats ( 'LASSI', 'T1')

  SQL > ALTER DATABASE DROP LOGFILE MEMBER 'C:\ORA\ADMIN\ORADATA\ORCL\REDO01.LOG';
  ALTER DATABASE DROP LOGFILE MEMBER 'C:\ORA\ADMIN\ORADATA\ORCL\REDO01.LOG'
  *
  ERROR at line 1:
  ORA-00361: cannot remove last log member C:\ORA\ADMIN\ORADATA\ORCL\REDO1.LOG for group 1.

  18:14:19 ORCL > ALTER DATABASE DROP LOGFILE GROUP 1;
  ALTER DATABASE DROP LOGFILE GROUP 1
  *
  ERROR at line 1:
  ORA-01623: log 1 is current log for instance orcl (thread 1) - cannot drop
  ORA-00312: online log 1 thread 1: 'C:\ORA\ADMIN\ORADATA\ORCL\REDO01.LOG'

  ALTER SYSTEM SWITCH LOGFILE;

  SELECT *
  FROM dba_role_privs 
  WHERE granted_role = 'DBA';

  KERNEL_VERSION                  MAKE_MODEL                                           N cells N Instances
  ------------------------------- ---------------------------------------------------- ------- -----------
  2.6.39-400.248.3.el6uek.x86_64  Oracle Corporation SUN SERVER X4-2L High Performance       3           2

  CON_NAME
  -------------
  CDB$ROOT

  NAME      OPEN_MODE
  --------- ----------------
  OR2017    READ WRITE

  INSTANCE_NAME    VERSION        STATUS
  ---------------- -------------- -------
  or2017           12.1.0.2.0     OPEN

  NAME        OPEN_MODE
  ----------- ----------
  PDB$SEED    READ ONLY
  PDB2017     MOUNTED

  show con_name

  select name, open_mode from v$database;

  select instance_name, version, status from v$instance;

  select name, open_mode from v$pdbs;

 CREATE TABLE data 
  (aod DATE);
 
  insert into data values (to_date(20150331,'yyyymmdd'));
  insert into data values (to_date(20150630,'yyyymmdd'));
  insert into data values (to_date(20150930,'yyyymmdd'));
  insert into data values (to_date(20151231,'yyyymmdd'));
 
  insert into data values (to_date(20160331,'yyyymmdd'));
  insert into data values (to_date(20160630,'yyyymmdd'));
  insert into data values (to_date(20160930,'yyyymmdd'));
  insert into data values (to_date(20161231,'yyyymmdd'));

  AOD         Q-1         EOY-1
  ----------- ----------- -----------
  31-MAR-2015 31-DEC-2014 31-DEC-2014
  30-JUN-2015 31-MAR-2015 31-DEC-2014
  30-SEP-2015 30-JUN-2015 31-DEC-2014
  31-DEC-2015 30-SEP-2015 31-DEC-2014
  31-MAR-2016 31-DEC-2015 31-DEC-2015
  30-JUN-2016 31-MAR-2016 31-DEC-2015
  30-SEP-2016 30-JUN-2016 31-DEC-2015
  31-DEC-2016 30-SEP-2016 31-DEC-2015

  SELECT aod,
         add_months(aod,-3) "Q-1",
         trunc(aod,'year')-1 "EOY-1"
  FROM data;

  SELECT *
  FROM dba_ind_columns
  WHERE index_owner = 'LASSI' and 
        index_name = 'FACT1_IDX';

  SELECT round(sum(bytes)/1024/1024) mb
  FROM dba_segments
  WHERE segment_type like 'INDEX%';

 SELECT distinct ft_no
 FROM t1
 WHERE date1 = 20160912 AND
       le = 'LOR';

 FT_NO
 -----
   306

 SELECT *
 FROM t1
 WHERE date1 = 20160912 AND
       ft_no = 306;

 No rows.
 

 desc STG_TABLE

 Name      Type
 --------- ------------------
 C1        NUMBER
 C2        NUMBER
 C3        VARCHAR2(300)

 select C1, C2, C3, count(1)
 from STG_TABLE
 group by C1, C2, C3
 having count(1) = 1;

 SEGMENT PARTITION_NAME SEGMENT_TYPE    COMPRESS COMPRESS_FOR COMPRESS COMPRESS_FOR    MB
 ------- -------------- --------------- -------- ------------ -------- ------------ -----
 D_ST                   TABLE                                                       19200
 F_DATA  TP_20160609_W  TABLE SUBPARTIT                       DISABLED              14406
 F_DATA  TP_20160728_D  TABLE SUBPARTIT                       DISABLED              12209
 F_VAR   F_20150219     TABLE PARTITION ENABLED  QUERY LOW                          10500

  CREATE TABLE FACT_TABLE
   ( DAY NUMBER,
     DESCR VARCHAR2(10),
	...
    )
  PARTITION BY LIST (DAY)
  SUBPARTITION BY LIST (DESCR)
  SUBPARTITION TEMPLATE (
    SUBPARTITION S1 VALUES ( 'SOL1' ),
    SUBPARTITION S2 VALUES ( 'SOL2' ),
    SUBPARTITION S3 VALUES ( 'SOL3' ) )
 ( PARTITION TP_20160101  VALUES (20160101),
   PARTITION TP_20160102  VALUES (20160102),
  ...);

 1. To get the partitioning key columns:

  SELECT *
  FROM dba_part_key_columns
  WHERE name = 'FACT_IDX1';

 2. To get the subpartitioning key columns:

  SELECT *
  FROM dba_subpart_key_columns
  WHERE name = 'FACT_IDX1';

 DEGREE OWNER   TABLE_NAME
------- ------- ------------
      5 LORIS   FACT1
      4 LORIS   FACT12
      2 LAS     DIM4

  SELECT degree, owner, table_name
  FROM dba_tables a
  WHERE owner like 'L%' and 
        degree != 1;

 TABLE_1
 ----------
 0,15

 TABLE_2
 ----------
 0,95
 

 OWNER   TABLE_NAME   COLUMN_NAME  DATA_TYPE  DATA_LENGTH
 ------- ------------ ------------ ---------- -----------
 LASSI   ST_ERROR     ERROR        VARCHAR2   200
 LORIS2  DIM_ERRORS   ERROR        VARCHAR2   600
 LORIS2  FACT_ERRORS  ERROR        VARCHAR2   2000

  SELECT tc.owner, tc.table_name, tc.column_name, tc.data_type, tc.data_length
  FROM     	dba_tab_columns tc,
        	dba_tables t
  WHERE tc.table_name = t.table_name and tc.owner = t.owner 
        and column_name in UPPER( '&col_name' ) 
  ORDER BY 1,2;

 SELECT owner, table_name, partitioning_type, subpartitioning_type, partition_count, interval
 FROM dba_part_tables
 WHERE owner = 'LASSI' and table_name = 'T1';

 OWNER  TABLE_NAME  PARTITION SUBPARTIT PARTITION_COUNT INTERVAL
 ------ ----------- --------- --------- --------------- --------------------------
 LASSI  T1          RANGE     NONE              1048575 NUMTOYMINTERVAL(1,'MONTH')

 SELECT *
 FROM TABLE
  (dbms_xplan.display_awr('57hfsgdfs876la', null, null, 'ALL'));

  exec DBMS_WORKLOAD_REPOSITORY.create_snapshot

You could use the function SYS_CONTEXT :

 SELECT
    SYS_CONTEXT('USERENV', 'INSTANCE_NAME') instance,
    SYS_CONTEXT('USERENV', 'ISDBA') amidba
 FROM dual;

 ID OPERATION                  OBJECT CARDINALITY   KB TEMP  COST TIME PStart PStop QBLOCK
--- -------------------------- ------ ----------- ---- ---- ----- ---- ------ ----- ------
  0 SELECT - Plan 635035975                                  5668
  1  PX COORDINATOR                                                                 SEL$1
  2   PX SEND QC (RANDOM)      :TQ107       20063  467       5668    3
  3    HASH GROUP BY                        20063  467   76  5668    3
  4     PX RECEIVE                          20063  467       5667    3
  5      PX SEND HASH          :TQ106       20063  467       5667    3
  6       BUFFER SORT                       20063  467       5668    3
  7        NESTED LOOPS                     20063  467       5667    3
  8         HASH JOIN                       20063  369       5667    3
etc...
 15      PARTITION LIST SINGLE               2857              12    1 3       3
 16       INDEX RANGE SCAN     FACT1         2857              12    1 KEY     KEY  SEL$1

 NAME                  VALUE_I1                             VALUE_I2
 --------------------- -------------------------------- --------------------------------
 background_dump_dest  /opt/ora/diag/rdbms/db/db1/trace /opt/ora/diag/rdbms/db/db2/trace
 cluster_interconnects 192.168.10.1:192.168.10.2        192.168.10.3:192.168.10.4
 core_dump_dest        /opt/ora/diag/rdbms/db/db1/cdump /opt/ora/diag/rdbms/db/db2/cdump
 instance_name         db1                              db2
 instance_number       1                                2

Reading the GV$SYSTEM_PARAMETER views, you could use a query like this:

 select	i1.name,
	i1.value value_i1,
	i2.value value_i2
 from GV$SYSTEM_PARAMETER  i1,
      GV$SYSTEM_PARAMETER  i2
 where i1.inst_id = (select min(inst_id) from GV$SYSTEM_PARAMETER)  and
       i2.inst_id = (select max(inst_id) from GV$SYSTEM_PARAMETER)  and
       i1.num = i2.num and
       i1.value != i2.value
 order by 1;

Usually in a RAC, two instance have at least 8-10 parameters with different values.

 DAY       TSNAME  SIZE_GB    MAXSIZE_GB    USEDSIZE_GB
 --------- ------- ---------- ------------- -----------
 09-JUL-15 TB1     3543          3700           3438
 10-JUL-15 TB1     3543          3700           3438
 11-JUL-15 TB1     3543          3700           3431
 12-JUL-15 TB1     3543          3700           3453
 13-JUL-15 TB1     3548          3860           3458
 14-JUL-15 TB1     3548          3860           3476
 15-JUL-15 TB1     3548          3860           3480
 16-JUL-15 TB1     3548          3860           3417
 17-JUL-15 TB1     3548          3860           3410
 18-JUL-15 TB1     3548          3860           3410
 19-JUL-15 TB1     3548          3860           3410
 20-JUL-15 TB1     3548          3860           3439
 21-JUL-15 TB1     3726          3860           3584
 22-JUL-15 TB1     3726          3860           3589
 23-JUL-15 TB1     3726          3860           3554

You could use a query like this (in bold font what you should change according to your
tablespace and period to analyze) :

 select trunc(begin_interval_time) day,tsname, 
    round(max(tablespace_size*8192)/1024/1024/1024) size_gb, 
    round(max(tablespace_maxsize*8192)/1024/1024/1024) maxsize_gb,
    round(max(tablespace_usedsize*8192)/1024/1024/1024) usedsize_gb
 from (
    select
         s.snap_id, s.dbid, s.instance_number, s.startup_time, s.begin_interval_time, 
         s.end_interval_time, tablespace_size, tablespace_maxsize, tablespace_usedsize,
         tsname, contents, status, segment_space_management, extent_management, is_backup
    from  dba_hist_tbspc_space_usage u,
          dba_hist_snapshot s ,
          dba_hist_tablespace_stat t
    where u.snap_id=s.snap_id and u.dbid=s.dbid and u.snap_id=t.snap_id and t.dbid=s.dbid
      and u.tablespace_id=t.ts# and s.instance_number=t.instance_number
      and tsname = 'TB1'
      and begin_interval_time > sysdate-14 
    order by s.snap_id desc
    ) 
 group by trunc(begin_interval_time), tsname 
 order by trunc(begin_interval_time), tsname desc;

  SQL > 

 18:03:23 dblor >

 set lin 180 pages 120
 alter session set nls_language='AMERICAN';

 SQL> select sysdate from dual;

 SYSDATE
 ---------
 02-GIU-15

1. You could modify the query using the function TO_CHAR

    SQL> SELECT to_char(sysdate,'yyyymmdd hh24.mi') date_time 
         FROM dual;

         DATE_TIME
    --------------
    20150602 21.37

2. You could modify the NLS_DATE_FORMAT on the session where you are connected using the
   ALTER SESSION command:

    SQL> alter session set nls_date_format = 'yyyymmdd hh24.mi';
    Session modified.

    SQL> SELECT sysdate 
         FROM dual;

           SYSDATE
    --------------
    20150602 21.37

 INST_ID  SID Plan_Hash_V SQL_ID        CHILD_ADDRESS    NAME  LAST_CAPTURED  Value
 ------- ---- ----------- ------------- ---------------- ----- -------------- -----
       2  285  2170058777 0kkhhb2w93cx1 0000000692F75980 :1    23/04 14.00    1
       2  285  2170058777 0kkhhb2w93cx1 0000000692F75980 :2    23/04 14.00    2
       2  285  2170058777 0kkhhb2w93cx1 0000000692F75980 :3    23/04 14.00    44506

  create table a (aa char);
  insert into a values ('Z');
  commit;

  SQL > select aa from dual;
  select aa from dual
         *
  ERROR at line 1:
  ORA-00904: "AA": invalid identifier

  SQL > select * from a where aa in (select aa from dual);

  A
  -
  Z

 Instance_name : you could use the sys_context function :

	select sys_context ('USERENV', 'INSTANCE_NAME') 
	from dual;

 Database Global name : you could use the view global_name :

	select *
	from global_name;

 ALTER TABLE t1
 EXCHANGE SUBPARTITION sp_20141218_mi
 WITH TABLE t2
 INCLUDING INDEXES WITHOUT VALIDATION;
 Report errors:
 Error SQL: ORA-14278: column type or size mismatch in EXCHANGE SUBPARTITION
 14278. 00000 -  "column type or size mismatch in EXCHANGE SUBPARTITION"
 *Cause:    The corresponding columns in the tables specified in the
            ALTER TABLE EXCHANGE SUBPARTITION are of different type or size
 *Action:   Ensure that the two tables have the same number of columns
            with the same type and size.