ManualiOracle.it
  
 
> Quizzes > Quizzes 2018 -- Oracle / Postgresql -- Best Pratices / How to
 
 
 
 
 


In the page Prices and Donations, you can find how to have the solutions of the quizzes.

Nella pagina Prezzi e Contatti potete vedere come avere le soluzioni dei quiz.


If it is not specified, the commands must be executed connected as a database "superuser".
About the solutions:
- could be more than one. Here is one solution that often takes into account of the performance tuning
- they could change because of the database version
- it is not warranted to be error-free in all the database environments


July - Postgres db list

Level (*) : 1
Topic (*) : Postgres


You are connected, thru the psql tool, on a Postgres database cluster.
You must say two methods to have the list of the databases in that db cluster.

Solution

You can get this information in these ways:
  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;




June - Postgres Index Creation

Level (*) : 2
Topic (*) : Postgres


In postgres you have a table called T1 with a column called "data". Its type is JSONB.
Inside it, you have:
- a top level key called "customerinfo"
- a second level key called "cod"

Example taken from "data" column:
  "customerinfo": {
    "cod": "XWZLUX18S20D510K",
You have to create an index called T1_IDX on "cod" inside "customerinfo".
This must be done "online": while the people are using the database.
Write the SQL index creation.



May - SQL cursor problem

Level (*) : 2
Topic (*) : DBA


You are connected in an Oracle database 11.2.0.4.

Looking at the Oracle dynamic system views, you have to find 2 queries:
- one query in order to find the SELECTs with many version_count
- one query in order to find the reason why the SQL cursor of a known sql_id (i.e. a5w5hm8nn657b) is not shared



April - Postgres Streaming Replication

Level (*) : 2
Topic (*) : Postgres


You have 2 postgres database servers (postgresql version 10.3 in Linux machines) in Streaming Replication.
You are connected in both servers and you want to discover which is the master / primary database and which is the slave / secondary database (this accepts read-only transactions).

To be sure, you can get this information using many methods. Find some of these methods:
- one method using a Recovery Information Function
- one using a database system parameter
- one looking at the O.S. processes in the machines

And find which Recovery Information Functions can you use in order to check if now there is a lag between the data on the 2 databases.



March - Postgres Dblink

Level (*) : 3
Topic (*) : Postgres


You are working on a Postgresql cluster database, version 9.5.
Connected as USER_1 user on the DB_1 database, you must create a view called V_1 reading the T_2 table (owner USER_2, database DB_2, schema public).
DB_1 and DB_2 are in the same cluster database.

The describe of T_2 table is:
                 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()
You decide to create a dblink and use it.
The USER_1 is not a "super user" and you want to avoid to write the USER_2 password.
Then inside the query of the view, you decide to use the dblink_connect_u.

Write that query and all the needed steps.



February - SQL*Plus commands

Level (*) : 1
Topic (*) : DBA/Prog


You are connected on the db thru the SQL*Plus tool.
You try to write an Oracle procedure and you get an error:
  SQL > 
   1  CREATE OR REPLACE PROCEDURE pr_error
   2  BEGIN
   3   null;
   4* END;

  Avvertimento: procedura creata con errori di compilazione.
How can you see the details of this error?
And which SQL*Plus command can you use in order to modify easily the procedure?

Solution

To see the error you must write SHOW ERROR:
  SQL > show error
  Errori in PROCEDURE PR_ERROR:

  LINE/COL ERROR
  -------- -----------------------------------------------------------------
  2/1      PLS-00103: Trovato il simbolo "BEGIN" anzich uno dei seguenti:
           ( ; is
           with authid as cluster compress order using compiled wrapped
           external deterministic parallel_enable pipelined result_cache
           Il simbolo "is"  stato sostituito per permettere a "BEGIN" di
           continuare.
To modify the procedure, you could use the SQL*Plus command called EDIT: it will open a text edit where you can easily work.



January - AWR report

Level (*) : 2
Topic (*) : DBA


You are connected on the db thru an Oracle client using a TNS connection.
You want to do an AWR report and, because of you are not in the server, you can't launch the awrrpt.sql script.
Which queries can you do in order to do the AWR report?



(*)
Level:
1: easy solution
2: medium solution
3: hard (or long) solution

Topic:
DBA:
quiz usually suitable for Oracle DBAs
Progr: quiz usually suitable for PL/SQL or SQL Programmers
DBA/Progr: quiz suitable for DBAs and for Programmers
Postgres: regarding the Postgresql database

   

This website is not authorized by Oracle Corporation and it is not connected to the official Oracle website.
Some words used here are registered trade marks of their owners.