TbsName  MB_Tot  MB_Used  %_Used  Autoext  Bl_size  Pct_Inc  Contents  Ext_Mana  Allocat_tp  Segm_sp

TEMP       1096    1096     100     YES      8192      0     TEMPORARY    LOCAL     UNIFORM   MANUAL
EXAMPLE   148,7     149    99,9     YES      8192            PERMANENT    LOCAL     SYSTEM    AUTO
LORIS_T     600     595    99,1     YES     16384      0     PERMANENT    LOCAL     UNIFORM   AUTO
SYSTEM      470     461      98     YES      8192            PERMANENT    LOCAL     SYSTEM    MANUAL
XDB        46,8      44    94,4     YES      8192            PERMANENT    LOCAL     SYSTEM    AUTO

CWMLITE      20      18    87,8     YES      8192            PERMANENT    LOCAL     SYSTEM    AUTO
TOOLS        10       6    60,6     YES      8192            PERMANENT    LOCAL     SYSTEM    AUTO
DRSYS        20      10    48,4     YES      8192            PERMANENT    LOCAL     SYSTEM    AUTO
ODM          20       9    46,6     YES      8192            PERMANENT    LOCAL     SYSTEM    AUTO
UNDOTBS    2770      70     2,5     YES      8192            UNDO         LOCAL     SYSTEM    MANUAL

MONITOR     100       0      ,1     NO       8192            PERMANENT    LOCAL     SYSTEM    AUTO

Create procedure pr_tbs_92 is
  
  v_cont     pls_integer := 0;

begin
  dbms_output.put_line
    (' Tablespace_Name            MB_Tot  MB_Used  %_Used  Autoext  Bl_size  Pct_Incr   Contents' ||
     '   Ext_Managem   Allocat_type  Segm_sp_manag');

  for rec in (
      select rpad(b.tablespace_name, 25) || 
             lpad(Mbytes_alloc,8) || 
             lpad(round(Mbytes_alloc-nvl(Mbytes_free,0)),8) || 
             lpad(round(((Mbytes_alloc-nvl(Mbytes_free,0))/
                                Mbytes_alloc)*100,1),8) || '     ' ||
             rpad(decode(autoext,0,'NO ', 'YES'),4) || 
             lpad(c.block_size,9)  ||
             lpad(nvl(to_char(c.pct_increase),' '),8)  || '      ' ||
             rpad(c.contents,12) || '  ' || 
             rpad(c.extent_management,13)  ||
             rpad(c.allocation_type,13) ||
             c.segment_space_management  all_columns
      from ( select sum(bytes)/1048576 Mbytes_free,
                    tablespace_name
             from  sys.dba_free_space
             group by tablespace_name 
           ) a,
           ( select sum(bytes)/1048576 Mbytes_alloc,
                    sum(decode(autoextensible,'YES',1,0))  autoext,
                    tablespace_name
             from sys.dba_data_files
             group by tablespace_name
             union all
             select sum(bytes)/1048576 Mbytes_alloc,
                    sum(decode(autoextensible,'YES',1,0))  autoext,
                    tablespace_name
             from sys.dba_temp_files
             group by tablespace_name  
           ) b,
           dba_tablespaces c
      where a.tablespace_name (+) = b.tablespace_name and
            c.tablespace_name = b.tablespace_name
      order by (Mbytes_alloc-nvl(Mbytes_free,0))/
                        Mbytes_alloc desc 
          ) loop

	v_cont := v_cont+1;        
	if mod(v_cont,5) = 1 then
	  dbms_output.new_line;
	end if;

	dbms_output.put_line(' ' || rec.all_columns);

  end loop;

end pr_tbs_92;

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?
And if you wanted just rename it in dbfile2.dbf without changing location?

Solution

1. check to have db backups (useful if you have any problems)
2. say to the db users that could not accede to some db information
3. put OFFILINE the tablespace users:
  ALTER TABLESPACE users OFFLINE;
4. move the file through the O.S. commands. In Unix:
  move '/old/dbfile1.dbf' '/new/dbfile1.dbf'
5. Oracle will have to point to the new file:
  ALTER TABLESPACE users RENAME DATAFILE '/old/dbfile1.dbf' TO '/new/dbfile1.dbf';
6. put ONLINE the tablespace users:
  ALTER TABLESPACE users ONLINE;
  
If you wanted rename the file without changing the location, you had to do the same steps.

September - SQL and SYSTEM TABLES

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

We want to do a query that show how many space is 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

Solution

I use the views sys.sm$ts_used and sys.sm$ts_free:

 select b.tablespace_name, round(a.bytes/1048576) "MB Used", 
	round((a.bytes+b.bytes)/1048576) "MB Tot",
	round(100*a.bytes/(a.bytes+b.bytes),1) "Percent Used"
 from sys.sm$ts_used a, sys.sm$ts_free b
 where a.tablespace_name (+) = b.tablespace_name
 order by round(100*a.bytes/(a.bytes+b.bytes),1) desc;

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).

Solution

I use a varray:

 declare
  type sequenzaTab is varray(10) of varchar2(80);
  elencoTab sequenzaTab:= sequenzaTab( 't1'  ,'t2'
                                      ,'t3'  ,'t4'
                                      );
 begin 
  for d in 1..elencoTab.count loop
    for c in 1..elencoTab.count loop
      for b in 1..elencoTab.count loop
        for a in 1..elencoTab.count loop
          if elencoTab(a) != elencoTab(b) and elencoTab(a) != elencoTab(c) and 
             elencoTab(b) != elencoTab(c) and elencoTab(a) != elencoTab(d) and
             elencoTab(b) != elencoTab(d) and elencoTab(c) != elencoTab(d)  then
            dbms_output.put_line ('from ' || elencoTab(d) || ', ' || elencoTab(c) || ', ' || 
				  elencoTab(b) || ', ' || elencoTab(a));
          end if;
        end loop;
      end loop;
    end loop;
  end loop;
 end;

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 (so you could 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 on in the query.

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

Solution

The opened brackets has ascii 40.
The closed brackets has ascii 41.
This procedure is easily suitable to find any character (you just have to know its ascii).


declare
-- !!! IF YOU HAVE ' YOU MUST CHANGE IT IN ''
 v_ascii pls_integer := 0;
 v_aperta pls_integer := 0;
 v_chiusa pls_integer := 0;
 
 a varchar2(2000):= '
select dbms_xmlgen.getxml (
           ctx => dbms_xmlgen.newContext (
               ''select    xmlelement ("Catal", cd_catalog, 
                                     xmlForest (clingua as "Ling", 
                                                pr_item as "Item",
                                                cd_feat as "Feat")
                                     )
                from  a2''),
           tmpclob => ''select   xmlelement ("Catal", cd_catalog, 
                           xmlForest (clingua as "Ling", 
                                      pr_item as "Item",
                                      cd_feat as "Feat")
                                           )
                      from  a2''
from dual
  ';
 
begin
 for i in 1..length(a) loop
  
  v_ascii := ascii(substr(a,i,1));
  
  if v_ascii = 40 then
    v_aperta := v_aperta+1;
  elsif v_ascii = 41 then
    v_chiusa := v_chiusa+1;
  end if;
  
 end loop;
 dbms_output.put_line ('Opened: ' || v_aperta);
 dbms_output.put_line ('Closed: ' || v_chiusa);
 
end;

July - SQL and System Views

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

We are in Oracle9i (then in the db you can have more than 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.

Solution

I use the user_tables and the dba_tablespaces:

select a.table_name, a.num_rows "Number of Rows", 
(a.blocks+empty_blocks) *
 (select block_size/1024 from dba_tablespaces where tablespace_name = a.tablespace_name) KB_Allocated,
(a.blocks) *
 (select block_size/1024 from dba_tablespaces where tablespace_name = a.tablespace_name) "KB Used", 
 a.empty_blocks * 
 (select block_size/1024 from dba_tablespaces where tablespace_name = a.tablespace_name) "KB Empty", 
 avg_row_len "Avg_row_len",
 a.last_analyzed "Last_analyzed"
from user_tables a
where a.partitioned = 'NO';

In Oracle 8i, the subquery inside the query must be this:

 select value/1024  from v$parameter where name = 'db_block_size'

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 through Sql*Plus.

Solution

In Sql*Plus in order to see 2000 characters of the LONG columns you have to launch:

 set long 2000

And this is the query:

 select 'create or replace view ' start_create
        ,a.owner || '.' || a.view_name  complete_name
        ,' as ' congiunzione
        , text
	, ';'
 from dba_views a
 where owner = '&user';

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
-	these files will 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 column names
-	these files must have a name like username__tablename.LST

Solution

set serveroutput on
declare 
  cursor cur is
    select owner, a.table_name
    from DBA_TABLES a
    where a.owner = 'SCOTT'
    order by a.table_name;
begin
  dbms_output.put_line('set lines 200');
  dbms_output.put_line('set pages 1000000');
  dbms_output.put_line('set head off'); -- so you haven’t the columns name
  -- LOOP
  for rec in cur loop
    dbms_output.put_line('SPOOL d:/temp/' || rec.owner || '__' || rec.table_name || '.LST;');
    dbms_output.put_line('SELECT * FROM ' || rec.owner || '.' || rec.table_name || ';');
    dbms_output.put_line('SPOOL off;');
  end loop;
end;

In my db, its result is this:

 set lines 200
 set pages 1000000
 set head off
 SPOOL d:/temp/SCOTT__BONUS.LST;
 SELECT * FROM SCOTT.BONUS;
 SPOOL off;
 SPOOL d:/temp/SCOTT__DEPT.LST;
 SELECT * FROM SCOTT.DEPT;
 SPOOL off;
 SPOOL d:/temp/SCOTT__EMP.LST;
 SELECT * FROM SCOTT.EMP;
 SPOOL off;
 SPOOL d:/temp/SCOTT__SALGRADE.LST;
 SELECT * FROM SCOTT.SALGRADE;
 SPOOL off;

Launching this, you will create 4 files.

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 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;

Solution

In the dba_dependencies you can see which tables are referenced from the views:

select distinct 
       'grant select on ' || b.referenced_owner || '.' || b.referenced_name ||
       ' to SCOTT;' grant_select
from  dba_views a,
      dba_dependencies b
where a.owner = 'LORIS' and
      b.owner = 'LORIS' and
      a.view_name = b.name;

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 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 than 1000
PLAN_TABLE: 0

Tables Analyzed: 4

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

Solution

declare

  cursor cur is
    select*
    from user_tables;
  
  rec cur%rowtype;    
  v_select varchar2(2000);
  v_count pls_integer := 0;
    
begin

  -- HEAD LINES
  dbms_output.put_line ('Date: ' || to_char(sysdate, 'dd/mm/yyyy hh24:mi'));
  dbms_output.put_line ('User Analyzed: ' || user);
  dbms_output.new_line;

  -- WE WANT TO WRITE HOW MANY TABLES WE HAVE ANALYZED, THEN I PREFER DON’T USE THE
  -- “FOR..LOOP” (PERFORMANCE REASONS)
  open cur;
  loop 
    fetch cur into rec;
    exit when cur%notfound;    
  
      v_select := 'select count(*)  from ' || rec.table_name || ' where rownum<=1001';  
      execute immediate v_select into v_count;

      if v_count>1000 then
        dbms_output.put_line (rec.table_name || ': more than 1000');
      else
        dbms_output.put_line (rec.table_name || ': ' || v_count);
      end if;
    
  end loop;
  dbms_output.new_line;
  dbms_output.put_line ('Tables Analyzed: ' || cur%rowcount);
  close cur;
  
end;

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 commands 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

© Copyright 2005-Today Loris ASSI
This website is not connected with Oracle Corporation or the official Oracle website. Some words used here are registered trade marks of their owners.