| ManualiOracle.it | ||
|
||
| ||||||||||||||||||||||||||||||||||||||||||||||
|
If it is not specified, the commands must be executed connected as SYS. 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 Oracle version (eg. Oracle 8i is quite different than Oracle 12c!) - it is not warranted to be error-free in all the Oracle environments December - Space Used
Level (*) : 1 Topic (*) : DBA You have to find a query in order to discover the total space (in MBytes) used from all the indexes of the database. Solution
You should read the DBA_SEGMENTS view and use the "WHERE condition" in order to check all the indexes (partitioned and not):
SELECT round(sum(bytes)/1024/1024) mb FROM dba_segments WHERE segment_type like 'INDEX%';
November - Different result sets
Level (*) : 3 Topic (*) : Prog You have the table T1: it is "list partitioned" by the DATE1 column and "list sub-partitioned" by the FT_NO column. Look at this:
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.
Then using the first query seems to exist the FT_NO 306 with DATE1 = 20160912, using the other query no.The sub-partitions on the table T1 are populate thru the EXCHANGE SUBPARTITION command and among the sub-partitions doesn't exists the value 306. How is it possible this situation? How can you fix it?
October - Find out the duplicated rows
Level (*) : 3 Topic (*) : DBA/Progr Looking at the Oracle dictionary and given a table name (the table has no unique constraint and it is under the schema where you are connected), you have to find a query useful in order to find all the duplicated rows on that table. Example: This is the table containing duplicated rows: desc STG_TABLE Name Type --------- ------------------ C1 NUMBER C2 NUMBER C3 VARCHAR2(300)Launching the query reading the dictionary, you must have a dynamic result like this: select C1, C2, C3, count(1) from STG_TABLE group by C1, C2, C3 having count(1) = 1;Then, launching it, you’ll find the duplicated rows.
September - Big Tables
Level (*) : 3 Topic (*) : DBA You have to write a query in order to find the biggest tables (or partitions or subpartitions) under your schema. The size of these objects must be more than 10GB and, in the result, you want to see also their compression information. Result example: 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
August - Sub-Partitioned Table
Level (*) : 3 Topic (*) : DBA You have a composite partitioned table called FACT_TABLE created like this:
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),
...);
After the table has been populated, you want to add a subpartition called S4 containing the value 'SOL4'.This subpartition must be added only in the partitions with DAY >= 20160801. Which is a fast way to do it? Using the Oracle dictionary, how can you check the current subpartition template? And write a script to modify the subpartition template: in it, you want to include the S4 subpartition.
July - Sub-Partitioned Indexes
Level (*) : 2 Topic (*) : DBA You have an index called FACT_IDX1. This index is a local index in the composite-partitioned table called FACT1. Looking at 2 different data dictionary views, you have to find the partitioning key columns and the subpartitioning key columns of FACT_IDX1. Write the 2 queries in order to find out these info. Solution
Connected as SYS or another “super user”:
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';
June - Table DEGREE
Level (*) : 1 Topic (*) : DBA You have to write a query in order to find, in the schemas starting with the letter "L", all the tables with degree of parallelism different than the value 1. For instance, you want a result like this:
DEGREE OWNER TABLE_NAME
------- ------- ------------
5 LORIS FACT1
4 LORIS FACT12
2 LAS DIM4
Solution
Connected as SYS user or another “super user”, you could launch this query on the DBA_TABLES view:
SELECT degree, owner, table_name
FROM dba_tables a
WHERE owner like 'L%' and
degree != 1;
May - Columns Size
Level (*) : 3 Topic (*) : DBA/Progr Loading the tables (also big tables) on your db schema, you often get the error "ORA-12899: value too large for column ...". In order to reduce the probability to have this error, you have to write a PL/SQL code that checks 20.000 rows in all the VARCHAR2 columns of your schema. If they seem little, you have to enlarge them. Example: In your schema you have the TABLE_1 and TABLE_2 tables so launching that program you should get something like: TABLE_1 ---------- 0,15 TABLE_2 ---------- 0,95This means that in the TABLE_2 the column where it is easier to get the error ORA-12899 is a column where there is data long 95% compared to its definition. Example: you have the column COL3 VARCHAR2(100) and in it there is data 95 chars long, so it is better to modify it in something like VARCHAR2(200). Additional Step: As wrote, usually you should check every table using a condition like "WHERE rownum<20000", but if the table has a column called "DAY", in that table you must use a condition like "WHERE day>sysdate-180 and rownum<20000".
April - Table Columns
Level (*) : 2 Topic (*) : DBA You have to write a query in order to find all the tables (and not views) that have a column called ERROR. For instance, you want a result like this: 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 Solution
Usually you should use the view DBA_TAB_COLUMNS alone but in it there is also the columns of the views, so in this case you should launch something like this:
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;
March - Interval Partition
Level (*) : 2 Topic (*) : DBA You have the LASSI.T1 table and you want to know if it's "INTERVAL partitioned" or not. Which query can you do in order to discover this information? Solution
You should look at the DBA_PART_TABLES:
SELECT owner, table_name, partitioning_type, subpartitioning_type, partition_count, interval FROM dba_part_tables WHERE owner = 'LASSI' and table_name = 'T1';E.g.: OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT INTERVAL ------ ----------- --------- --------- --------------- -------------------------- LASSI T1 RANGE NONE 1048575 NUMTOYMINTERVAL(1,'MONTH')In this case you can see that the table T1 is “interval partitioned” because of the PARTITIONING_TYPE column is RANGE and the INTERVAL column is not null.
February - Segments drop problem
Level (*) : 3 Topic (*) : DBA You have a tablespace where there are a lot of partitioned tables without statistics. You try to drop these tables but often the DROP operation is very slow (about 5 minutes each partition) and their sessions have the event “optimizer stats update retry". You check the number of extents of each segment and it is not very high. Which are 2 workarounds of this issue? And which Oracle MOS note talks about it?
January - SQL Plan execution
Level (*) : 2 Topic (*) : DBA/Progr Just now you have launched the query with SQL_ID = '57hfsgdfs876la'. You want to see its SQL execution plan thru this query:
SELECT *
FROM TABLE
(dbms_xplan.display_awr('57hfsgdfs876la', null, null, 'ALL'));
But you don't receive any result.What you could do in order to have a result? And what you could do in a RAC? Solution
To have a result, you need to do an AWR snapshot:
exec DBMS_WORKLOAD_REPOSITORY.create_snapshotIf you have a RAC you should do the same from any instance.
(*) Level: 1: easy solution 2: medium solution 3: hard (or long) solution Topic: DBA: quiz usually suitable for DBAs Progr: quiz usually suitable for PL/SQL or SQL Programmers DBA/Progr: quiz suitable for DBAs and for Programmers
| |||||||||||||||||||||||||||||||||||||||||||||
