| 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 November - Postgres dictionary
Level (*) : 2 Topic (*) : Postgres In your database cluster (postgres version 13), you have a database called DB1 with owner USER_1. You want to duplicate it in the same database cluster so you launch this command: CREATE DATABASE db2 TEMPLATE db1 OWNER user_1;After this operation you see that in the DB1 there is a search_path and in DB2 there isn't. You have to write a SQL query (from Postgresql dictionary) in order to get a result like this: ALTER DATABASE db1 SET search_path TO user_1;You'll modify it (and you'll launch it) in this way: ALTER DATABASE db2 SET search_path TO user_1;
October - Oracle DUAL table
Level (*) : 2 Topic (*) : Oracle Using the DUAL table you see some strange behavior. So you see this (these SQL statements are launched from SQL*Plus): SQL > create table prova as select * from dual; Table created. SQL > select * from prova; D - X y SQL > select * from dual; D - XOur Dual has 1 or 2 rows? How is it possible to get this situation? How can you solve it?
September - Postgres boolean values
Level (*) : 2 Topic (*) : Postgres You have a postgres table with a boolean column data type. You want to insert some boolean-ish values on it. What is its behavior when you want to insert values such as: 0, 1, 2, 99, t, 't', true ?
August - Postgres physical backups checks
Level (*) : 2 Topic (*) : Postgres You have a postgres database cluster and you do the physical backups of it by using the pgbackrest tool. You want to check that its WAL files are being archived in the correct way. So you want 2 outputs like these: Service : WAL_ARCHIVES Returns : 0 (OK) Message : 7 WAL archived Message : latest archived since 6m8s Long message : latest_archive_age=6m8s Long message : num_archives=7 Long message : archives_dir=/opt/postgres/pgbackrest_repo/archive/sta/13-1 Long message : min_wal=000000010000000000000015 Long message : max_wal=00000001000000000000001B Long message : latest_archive=00000001000000000000001B Long message : latest_bck_archive_start=00000001000000000000001B Long message : latest_bck_type=full Long message : oldest_archive=000000010000000000000015 Long message : oldest_bck_archive_start=000000010000000000000015 Long message : oldest_bck_type=full WAL_ARCHIVES OK - 7 WAL archived, latest archived since 6m8s | latest_archive_age=368s ... The first output is useful for "human" reading. The second output (here shortened) can be used by Nagios tool. Which tool can you use to have these outputs? And which commands can provide the above outputs?
July - Oracle SQL*Plus commands
Level (*) : 1 Topic (*) : Oracle You are connected to the database thru the SQL*Plus tool. You want to see the username you are using and the linesize of your connection, so for example you want results like these: USER is "SYS" linesize 220 Solution
To get this information you can use the SHOW (or SHO) command like this: SQL > sho user USER is "SYS" SQL > sho lin linesize 220And SHOW ALL lists the settings of all SHOW options, except ERRORS and SGA.
June - Oracle X$ tables
Level (*) : 2 Topic (*) : Oracle Sometimes, objects such as DBA_TABLES are called system tables. But actually the DBA_TABLES is a system view based on system tables such as sys.user$, sys.ts$ and other objects such as x$ksppcv, x$ksppi. In the DBA_OBJECTS, DBA_TABLES, DBA_INDEXES you can't find objects like x$ksppcv and x$ksppi. Why? Where you can find the list of the objects like them?
May - Oracle CDB or non-CDB
Level (*) : 3 Topic (*) : Oracle You have a lot of databases, some of them are CDB, some are non-CDB. You have to write a query in order to have these 3 possible results (it is important last row, here called "ENV"): NAME VAL ------------ ------------------------------------- Conn Name CDB$ROOT CDB Name ORCDB ENV DB multitenant. Connected to CDB$root NAME VAL ------------ ------------------------------------- Conn Name ORCLPDB1 CDB Name ORCDB ENV DB multitenant. Connected to a PDB NAME VAL ------------ ------------------------------------- Conn Name ORCLCDB CDB Name ENV DB non multitenantThis query can be launched from ANY user (so you don't need to be a "DBA"). The first 3 rows are just examples and they are directly extracted from the database, last row ("ENV") is calculated using a DECODE (or a CASE).
April - Postgresql connections
Level (*) : 2 Topic (*) : Postgres You have a database cluster that contains many databases. Regarding its database called db01, how can you check the maximum number of concurrent connections that can be made to it? And how can you set this value to 50 concurrent connections?
March - Oracle users
Level (*) : 2 Topic (*) : DBA You are connected in a non-CDB database and in it there are 2 application users called U1 and U2. Looking at the DBA_USERS you get this information: USERNAME ORACLE_MAINTAINED ------------ ----------------- U1 N U2 YBut both users weren't created by Oracle-supplied scripts. The U2 user has wrongly ORACLE_MAINTAINED = Y. How was possible to create an Oracle user like this ? Is there a way to change that user in order to have ORACLE_MAINTAINED = N ?
February (2) - Index issue using Oracle datapump
Level (*) : 3 Topic (*) : DBA In Oracle, you do an export (expdp) of one schema and the impdp of it in another database. The export and the import logs are without errors but you want to check if all the objects have actually been copied. Looking at the DBA_OBJECTS, you find out that you have all the constraints but the target database has not some indexes existing in the source database. This can be not a real problem. BTW You have to find: 1. Which could be the reason? And which MOS Oracle note write about that issue? 2. How can you find out all the indexes could have this issue (without checking all the index names)? 3. Say 2 methods to avoid it next time you'll do an impdp? And which MOS Oracle note write about these methods?
February - LOB issue using Oracle datapump
Level (*) : 2 Topic (*) : DBA In Oracle, you do an export (expdp) of one schema and the impdp of it in another database. After these operations, you check if all the objects have been copied. Looking at the DBA_OBJECTS, you find out that the target database has not some LOBs existing in the source database. Probably the most common reason of that is existing from Oracle 11.2.0.3. You have to find: 1. Which could be this reason? and why looking at the DBA_OBJECTS this issue happens from 11.2.0.3 and not before? 2. Looking at only the source database, how can you find out the LOBs could have this issue? 3. How can you avoid it next time you'll do an expdp? We are not in the case described in the following MOS Oracle note: Orphaned Lobs after marking LOB column unused (Doc ID 461651.1)
January - Oracle SQL hints
Level (*) : 2 Topic (*) : DBA / Progr In Oracle, you have this table and indexes:
CREATE TABLE emp (
empno INTEGER,
ename VARCHAR(10),
job VARCHAR(9),
mgr INTEGER,
hiredate TIMESTAMP,
sal NUMERIC,
comm NUMERIC,
deptno INTEGER,
constraint emp_pk primary key (empno)
);
CREATE INDEX emp_idx
ON emp (ename);
You want to launch this SQL statement:
SELECT * FROM emp e WHERE ename like '%po%' and empno in (1,2,3);And you see that its execution plan is using the EMP_PK index but you don't like it. So, you have to re-write 2 times the above SELECT using hints: - one query must use one hint in order to use the EMP_IDX index - the other one must use another hint in order to have a Table Access Full on the EMP table And, in SQL*Plus, how can you check these 3 execution plan?
(*) 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 Oracle DBAs and for Programmers Postgres: regarding the Postgresql database
| |||||||||||||||||||||||||||||||||||||||||||||
