| ManualiOracle.it | ||
|
||
| ||||||||||||||||||||||||||||||||||||||||||||||
|
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 December - Postgres Date and Jsonb
Level (*) : 2 Topic (*) : Postgres/Progr You have a table like this:
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 |
You have to find a very simple query in order to get the rows where:- column "ts" is bigger or equal to yesterday - column "data" contains the word "PAYPAL" You don't care about the "performace optimization" because the table T1 has no many rows.
November - Postgres SQL command
Level (*) : 2 Topic (*) : Postgres Look at the describe of a table called T1.
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)
Find out a single SQL command in order to create exactly a table like this (then using only CREATE TABLE ... and not ALTER TABLE ...).
October - Postgres backup thru barman
Level (*) : 2 Topic (*) : Postgres You are connected in a Barman server on Linux and you want to see: - the barman logs - which postgres database are configured for the backup system - the connection status to the postgresql server - the status of the Postgres server How can you do these?
September - Postgres Read-Only database
Level (*) : 2 Topic (*) : Postgres You are connected on a database called db_lor (single database, not database cluster). You want to check if it's in read-only mode and, if not, you want to change it in read-only mode. How can you do these?
August - Postgres db users
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 users in that db cluster. Solution
You can get this information in these ways:
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;
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 - Oracle 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 - Oracle SQL*Plus commands
Level (*) : 1 Topic (*) : DBA/Progr 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; Warning: Procedure created with compilation errors.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: 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.
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 - Oracle 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
| |||||||||||||||||||||||||||||||||||||||||||||
