Manuali Oracle - Quizzes 2006

1° case:
We are in Oracle 9i.
During the SGA tuning, from the dynamic views we calculate the Library Cache Hit Ratio:
the result is -1,5%.
Why the hit ratio is negative??

2° case:
We are in Oracle 8i and we have the db in ARCHIVELOG mode.
On the db these are constantly about 100 redo log switches a day.
The last log sequence number is 80000.
In about 200 days time, which strange thing could happen to the archived log?

November - PL/SQL and System Tables

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

We are in Oracle 9i.
We want to do a PLSQL procedure. When you execute that procedure, you receive (through the dbms_output) an analysis of all the tablespaces like that one on the following txt attachment:

tbs92.txt

October - SQL and UNIX

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

We are in Oracle 9i on a Sun Solaris machine.
We want to move a datafile (belonging to the USERS tablespace) without doing an istance shutdown.
The name and the path of the datafile is '/old/dbfile1.bdf'.
And we want to move it in '/new/dbfile1.bdf'.

Which steps you have to do?
If if you wanted just rename it in dbfile2.dbf without changing location?

September - SQL and SYSTEM TABLES

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

We want to do a query that show how many space are used in the tablespaces that we have in our instance.
This query must be suitable in Oracle 8, 8i, 9i and 10g.
For instance, the result must be like this:

TABLESPACE_NAME	MB Used	MB Tot	Percent Used
SYSTEM	473	480	98,5
SYSAUX	231	240	96,2
EXAMPLE	77	100	77,4
UNDOTBS1	20	30	66,4
USERS	23	50	46,1

August - PL/SQL

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

We are in Oracle 8i and the optimizer is RBO.
We have a query on 4 tables (t1, t2, t3, t4) and, because of the RBO optimizer, the tables order on the "FROM clause" of the query can change the explain plan.

We want to do a little plsql program where in input you put the 4 tables name and in output (through the dbms_output) you have:

from t1, t2, t3, t4
from t1, t2, t4, t3
from t1, t3, t2, t4
from t1, t3, t4, t2
from t1, t4, t2, t3
from t1, t4, t3, t2
from t2, t1, t3, t4
from t2, t1, t4, t3
from t2, t3, t1, t4
from t2, t3, t4, t1
from t2, t4, t1, t3
from t2, t4, t3, t1
from t3, t1, t2, t4
from t3, t1, t4, t2
from t3, t2, t1, t4
from t3, t2, t4, t1
from t3, t4, t1, t2
from t3, t4, t2, t1
from t4, t1, t2, t3
from t4, t1, t3, t2
from t4, t2, t1, t3
from t4, t2, t3, t1
from t4, t3, t1, t2
from t4, t3, t2, t1

Through this output, you can prepare 24 queries and you can see which of these queries has the best explain plan (or you can see which of these queries is faster).

July (2) - Number of brackets

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

Sometimes you do a big query and you don't know anymore if the number of the opened brackets and the number of the closed braket are the same (otherwise surely you'll receive an error from the query during its parsing).

You must do a little plsql program that, having in input the query to check, calculate the number of opened brackets and the number of the closed brackets present in the query.

For example, we want a result (through the dbms_output) such as this:
Opened: 6
Closed: 5

July - SQL and System Views

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

We are in Oracle9i (then in the db you can have more then one block size).
All the tables in your schema has the own statistics.

You must do a query in order to find the following information of those tables (not partitioned):
- table name
- number of rows
- space allocated (in KB)
- space used (in KB)
- empty space (that is space allocated = space used + empty space)
- how big is the average row (in bytes)
- the date of the last analysis

And you have to find a query with the same result but on Oracle 8i.

June - SQL and System Views

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

We are in Oracle8i (then we don't have the package dbms_metadata).

You have to find a SQL query that create scripts useful to re-create the views belonging to a schema.
For instance, launching this query about the schema SYS, a part of the result is this:

create or replace view
SYS.ALL_APPLY_CONFLICT_COLUMNS as
select c.object_owner, c.object_name, c.method_name,
	 c.resolution_column, c.column_name, c.apply_database_link
  from dba_apply_conflict_columns c, all_tables o
 where c.object_owner = o.owner
   and c.object_name = o.table_name
;

You must see the result thought Sql*Plus.

May - SQL and System Views

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

We are in Oracle9i and we are connected as SYSTEM.

You have to do a PL/SQL script that create some commands (through the dbms_output).

The execution of these commands must:

-	for any user (e.g., SCOTT) create a spool file for any table that he have
-	all of these spool files must contain all the rows that these tables have (every table has just few columns and few rows)
-	in the file you must put just the tables data without the columns name
-	all of these files must have a name like username__tablename.LST

April - SQL and System Views

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

We are in Oracle9i and we are connected as SYSTEM.
On the schema SCOTT we have just created the same views that are in schema LORIS.
All the LORIS and SCOTT's views read from the LORIS' tables.

You must do a query that create the SQL that SYSTEM must launch in order to give to SCOTT the privileges to read all the LORIS tables that are referenced from the views.

For instance, LORIS have the SALES view and this view reference the tables CUSTOMER, ITEM, ORD and PRODUCT; then we want that the query give this result:

grant select on LORIS.CUSTOMER to SCOTT;
grant select on LORIS.ITEM to SCOTT;
grant select on LORIS.ORD to SCOTT;
grant select on LORIS.PRODUCT to SCOTT;

March (2) - Increase the use of a narrow column

Level (*) : 3
Topic (*) : Prog

We are in Oracle9i. We have the table TAB_NUM with a numeric column that contain maximum the value 30.000.000
We know that this maximum value is not going to increase really much.

All of the values of this column must be inserted in a varchar2(5) column in the table TAB_CHAR.
This column can't be enlarged because of some applications that use it.

You must do an Oracle procedure that convert a number (maximum value: 50-60 million) in a string with 5 characters.

For example, my solution return these results:

fz(1) -> '00001'
fz(60111000) -> 'ZSDY0'

March - PL/SQL and System Views

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

We are in Oracle9i without statistics on the tables.
You have to do an anonymous block that calculate how many rows are in all the tables of the user that launch this block.
For instance, we want (through the dbms_output) a result like this:

Date: 05/03/2006 16:48
User Analyzed: LASSI

ITEM: 64
ORD: 21
PIPPO: More then 1000
PLAN_TABLE: 0

Tables Analyzed: 4

As you can see, the tables with more then 1000 rows must be marked with the sentence "More then 1000". You have to solve the problem reading the system views and using the execute .

February - SQL and System Views

Level (*) : 3
Topic (*) : Prog

We have a table like this:

create table A
( STATEMENT_ID VARCHAR2(30), PLAN_ID NUMBER, TIMESTAMP DATE, REMARKS VARCHAR2(4000), 
OPERATION VARCHAR2(30), OPTIONS VARCHAR2(255), 
OBJECT_NODE VARCHAR2(128), OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30), 
OBJECT_ALIAS VARCHAR2(65), OBJECT_INSTANCE INTEGER, 
OBJECT_TYPE VARCHAR2(30), OPTIMIZER VARCHAR2(255), SEARCH_COLUMNS NUMBER, ID INTEGER, 
PARENT_ID INTEGER, DEPTH INTEGER, POSITION INTEGER, 
COST INTEGER, CARDINALITY INTEGER, BYTES INTEGER, OTHER_TAG VARCHAR2(255), 
PARTITION_START VARCHAR2(255), PARTITION_STOP VARCHAR2(255), 
PARTITION_ID INTEGER, OTHER LONG, DISTRIBUTION VARCHAR2(30), CPU_COST INTEGER );

We are doing a procedure that insert records in that table.
We do it through a SQL that begin like this:

insert into A

	( STATEMENT_ID	,PLAN_ID
	,TIMESTAMP	,REMARKS
	,OPERATION	,OPTIONS
	,OBJECT_NODE	,OBJECT_OWNER
	,OBJECT_NAME	,OBJECT_ALIAS
	,OBJECT_INSTANCE	,OBJECT_TYPE
	,OPTIMIZER	,SEARCH_COLUMNS
	,ID	,PARENT_ID
	,DEPTH	,POSITION
	,COST	,CARDINALITY
	,BYTES	,OTHER_TAG
	,PARTITION_START	,PARTITION_STOP
	,PARTITION_ID	,OTHER
	,DISTRIBUTION	,CPU_COST
	)

values (

An SQL wrote like this is useful because make the code easily readable.
But in order to write an SQL long like this, you spend a lot of time and you could forget to write some columns.
Then you must write a template that, having in input the tablename that you want to fill up, create an SQL INSERT as that one above (included the rows "insert..." and "values (".
That template must be done in SQL.

January - Duplicate the users

Level (*) : 3
Topic (*) : DBA

We have the instance dblor and from it, we want to take information about some users in order to recreate them in one another instance.
In order to do it, we use a PL/SQL anonymous block that will write, through dbms_output, all the SQL command that after you can launch.

For example, launching the block in my db, I obtain:

-- *************************************************************
-- Purpose : CREATE THE SCRIPTS OF SOME EXISTING USERS IN THE INSTANCE
-- Date : 28/01/2006 13:28
-- Instance : dblor
--
*************************************************************

-- USERS INVOLVED --------------------------------------------
These are the users that meet the condition put in the main cursor:
1 LORIS
2 LORIS2

-- SCRIPTS FOR THE USER CREATION -----------------------------
create user LORIS identified by values '2570FE42941FF4FF'
default tablespace USERS temporary tablespace TEMP
profile DEFAULT
;
create user LORIS2 identified by values
'F894844C34402B67'
default tablespace SYSTEM temporary tablespace TEMP
profile DEFAULT
;

-- SYSTEM PRIVILEGES --------------------------------------------
These are the system privileges directly assigned to the users:
grant UNLIMITED TABLESPACE to LORIS;
grant UNLIMITED TABLESPACE to LORIS2;

-- ASSIGN ROLE PRIVILEGES ---------------------------------------
Attention: I don't display the object privileges not assigned through roles!
grant DBA to LORIS;
grant LORIS_USERS to LORIS;
grant DBA to LORIS2;
grant LORIS_USERS to LORIS2;
grant AQ_USER_ROLE to LORIS2;

-- ASSIGN DEFAULT ROLE ------------------------------------------
Here I get disabled some roles assigned previously:
alter user LORIS2 default role all except
DBA
,AQ_USER_ROLE
;

Details:
In the program you have to declare:
- the username if you want to get information about one user; or
- the role belonging to many users if u want to get information about the role

In the example above, I have the role LORIS_USERS that have associated the users LORIS and LORIS2.
The procedure is suitable also in old Oracle releases such as Oracle 8.0.
The new users that you'll create must have the same password of the old users (even if you don't know that password).

In the example below, I launch the procedure only for the user LORIS:

-- *************************************************************
-- Purpose : CREATE THE SCRIPTS OF SOME EXISTING USERS IN THE INSTANCE
-- Date : 28/01/2006 14:12
-- Instance : dblor
-- *************************************************************

-- USERS INVOLVED --------------------------------------------
These are the users that meet the condition put in the main cursor:
1 LORIS

-- SCRIPTS FOR THE USER CREATION -----------------------------
create user LORIS identified by values '2570FE42941FF4FF'
default tablespace USERS temporary tablespace TEMP
profile DEFAULT
;

-- SYSTEM PRIVILEGES --------------------------------------------
These are the system privileges assigned directly to the users:

grant UNLIMITED TABLESPACE to LORIS;

-- ASSIGN ROLE PRIVILEGES ---------------------------------------
Attention: I don't display the object privileges not assigned through roles!

grant DBA to LORIS;
grant LORIS_USERS to LORIS;

-- ASSIGN DEFAULT ROLE ------------------------------------------
Here I get disabled some roles assigned previously:

Note: The part where the roles are get disabled is probably the most difficult.
Especially if the roles to get disabled are more than one.

(*)
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


webmaster	© Copyright 2005-2010 Loris ASSI
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.

	ManualiOracle.it