In the page Prices and Donations, you can find how to have the solutions of the quizzes.			Nella pagina Prezzi Quiz potete vedere come avere le soluzioni dei quiz level 3.

 OWNER   OBJECT_NAME  SUBOBJECT_NAME  OBJECT_TYPE  CREATED
 ------- ------------ --------------- ------------ --------------
 LASSI   DIM_CIN      VIEW                         20141216 14:36
 LASSI   FACT_IDX     INDEX                        20141216 14:09
 LASSI2  FACT         INDEX                        20141216 14:08

 SELECT owner, object_name, subobject_name, object_type, created 
 FROM  dba_objects 
 WHERE created>sysdate-3/24;

 COMMAND
 ------------------------------------
 alter index LASSI.DIMIC_PK rebuild;
 alter index LASSI.LOGMSG_IDX rebuild;

 COMMAND
 -------------------------------------------------------
 alter index LASSI.FSE_IDX rebuild partition P_20141029;
 alter index LASSI.FTR_IDX rebuild partition P_20141030;

 COMMAND
 -----------------------------------------------------------
 alter index LASSI.FS_IDX rebuild subpartition P_20141111_1;
 alter index LASSI.FT_IDX rebuild subpartition P_20141111_2;

 SELECT  'alter index ' || owner || '.' || index_name || ' rebuild;'
 FROM dba_indexes
 WHERE owner = 'LASSI' and status = 'UNUSABLE';

 SELECT 'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' ||
        partition_name || ';'
 FROM dba_ind_partitions
 WHERE index_owner = 'LASSI'  and status = 'UNUSABLE';

 SELECT 'alter index ' || index_owner || '.' || index_name || ' rebuild subpartition ' ||
        subpartition_name || ' parallel 4;'  -- It doesn't change the index degree!
 FROM dba_ind_subpartitions
 WHERE index_owner = 'LASSI' AND STATUS = 'UNUSABLE';

 TABLENAME INDEXNAME   INDEX_TYPE  UNIQUENES  BLEVEL DistinctKeys LAST_ANAL PARTITIONED
 --------- ----------- ----------- --------- ------- ------------ --------- -----------
 LA.FMAP   LA.FMAP_IDX  NORMAL     NONUNIQUE       2     12589039 14-OTT-14  NO
 XXX.FMAP  XXX.FMAP_PK  NORMAL     UNIQUE          2     65640917 13-OTT-14  NO

 TABLENAME INDEXNAME    Column_name   C_Position
 --------- ------------ ------------- ----------
 LA.FMAP   LA.FMAP_IDX  AOD                   1
 LA.FMAP   LA.FMAP_IDX   LE                   2
 LA.FMAP   LA.FMAP_IDX    RIC                 3
 XXX.FMAP  XXX.FMAP_PK  AOD                   1
 XXX.FMAP  XXX.FMAP_PK   LE                   2
 XXX.FMAP  XXX.FMAP_PK    VAR_SCEN            3
 XXX.FMAP  XXX.FMAP_PK     NAME               4

 00 1 * * * /app/ora11/data/STAT/analyze.ksh > /app/ora11/data/STAT/stat.nohup 2>&1

  * * * * * *
  | | | | | | 
  | | | | | +-- Year              (range: 1900-3000)
  | | | | +---- Day of the Week   (range: 1-7, 1 standing for Monday)
  | | | +------ Month of the Year (range: 1-12)
  | | +-------- Day of the Month  (range: 1-31)
  | +---------- Hour              (range: 0-23)
  +------------ Minute            (range: 0-59)

 AOD integer,
 cod varchar2(10),
 cvalue number

  CREATE TABLE t1
  ( aod INTEGER NOT NULL,
    cod VARCHAR2(10) NOT NULL,
    cvalue NUMBER
   )
  PARTITION BY RANGE (aod) 
  INTERVAL(10000) 
  (  
    PARTITION p2013 VALUES LESS THAN (20140101)
   );

 SNAP_ID NODE BEGIN_INTERVAL END_INTERVAL   PlanHashVal EXECUTS  AvgBufGets Avg_DiskRds
 ------- ---- -------------- -------------- ----------- ------- ----------- -----------
 Sql_id       3s0kd398u2pxj
 845        2 20140522 12.00 20140522 12.26  3847028022       1     811,955           0
 851        2 20140522 16.04 20140522 17.00  2247069225       1     515,732      21,028
 852        1 20140522 17.00 20140522 18.00  2247069225       1     525,600           2
 852        2 20140522 17.00 20140522 18.00   617702193       1   1,257,942           0
 852        2 20140522 17.00 20140522 18.00  2247069225       1     523,476           0
 872        1 20140523 13.00 20140523 14.00   530248343       0  93,634,900     195,400

 INSTANCE_NAME  HOST_NAME   STARTUP_TIME
 -------------- ----------- ------------------
 expo_i1        lorsvil61   17-APR-14 18:07:45 
 expo_i2        lorsvil62   30-MAR-14 19:53:48

 SELECT instance_name, host_name, startup_time
 FROM gv$instance;

 TABLE_NAME     PARTITION_NAME
 -------------- ----------------
 U1.TAB_LOR     TAB_LOR_20131201
 U1.TAB_LOR     TAB_LOR_20131202
 ...
 U1.TAB_LOR     TAB_LOR_20140331
 U1.TAB_LOR     TAB_LOR_20140401
 ...
 U1.TAB_LOR     TAB_LOR_PMAX
 U2.TA_PIPPO    PART_20131201
 U2.TA_PIPPO    PART_20131202
 ...
 U2.TA_PIPPO    PART_20140331
 U2.TA_PIPPO    PART_PMAX

 TABLENAME    201308    09    10    11    12   201401    02    03    04
 ---------- -------- ----- ----- ----- ----- -------- ----- ----- -----
 U1.TAB_LOR       31    30    31    30    31       31    28    31    19
 U1.TAB_TEST      31    30    31    30    31       31    28    31    30
 U2.TA_PIPPO       0     0    12    30    31       31    28    31     0
 U2.EXPO           0    30    31    30    31       31    28    31    30

 FILENAME               SIZE    FILE_TIME
 ---------------------- ------- ------------------
 LASSI_VAR_ERR.csv	39	20-FEB-14 15:47:03
 MANUALIORACLE_26.log	8136	04-FEB-14 10:37:28
 LORIS_26932.log	20523	06-FEB-14 16:38:14

  PLATFORM_NAME
  ----------------------------
  Microsoft Windows x86 64-bit

 SELECT platform_name 
 FROM v$database;

  lormachine > ps -ef | grep pmon
  ordblor   3095     1  0 Oct31 ?        00:30:57 ora_pmon_DBLOR

  lormachine > ps -ef | grep pmon
  osdblor   3095     1  0 Oct31 ?        00:30:57 ora_pmon_DBLOR

 1) You see it in the DBA_HIST_WR_CONTROL view

 2) To change the frequence:

   exec dbms_workload_repository.modify_snapshot_settings (interval => 30)

 3) Manual snapshot (you can use it also on the RAC)

   exec DBMS_WORKLOAD_REPOSITORY.create_snapshot;   

 4) If you want AWR going back 10 days, before launching the script awrrpt.sql, you can
    do this:

   define num_days =10

 INDEXNAME               OBJECT_TYPE       TABLENAME            MB START_CREATION
 ----------------------- ----------------- ----------------- ----- --------------
 CRP_OWN.IDX_CA_IDX      INDEX             CRP_OWN.CA_IMPORT    25 20130826 11.36
 SYS.WRH$_SQLSTAT_INDEX  INDEX PARTITION   SYS.WRH$_SQLSTAT      0 20130826 23.20
 SYS.WRH$_WAITSTAT_PK    INDEX PARTITION   SYS.WRH$_WAITSTAT     0 20130826 23.20

 ENVIRONMENT   MAX_START_BCK_PIECE
 ------------- -------------------
 RMAN_DB1
 RMAN_DB2      2006-02-10 06.53
 RMAN_DB3      2006-12-06 14.14
 ...
 RMAN_DB8      2013-07-30 06.10
 RMAN_DB9      2013-07-30 07.00

 GRANT_ON_OBJECT
 ---------------------------------------------------------
 grant SELECT on PESO_CLIENTI to SELECT_ROLE;
 grant ALL on PESO_CLIENTI to ALL_ROLE;

 grant SELECT on VENDITE to SELECT_ROLE;
 grant ALL on VENDITE to ALL_ROLE;

 grant SELECT on ATTIVAZIONI to SELECT_ROLE;
 grant ALL on ATTIVAZIONI to ALL_ROLE;
 ...

 GRANT_ON_OBJECT
 ---------------------------------------------------------------
 grant SELECT on LASSI.PESO_CLIENTI to SELECT_ROLE;
 grant ALL on LASSI.PESO_CLIENTI to ALL_ROLE;

 grant SELECT on LASSI.VENDITE to SELECT_ROLE;
 grant ALL on LASSI.VENDITE to ALL_ROLE;

 grant SELECT on LASSI.ATTIVAZIONI to SELECT_ROLE;
 grant ALL on LASSI.ATTIVAZIONI to ALL_ROLE;
 ...

 ROLE          CREATION_TIME
 ------------- -------------------
 CONNECT       18/09/2012 11:36:12
 RESOURCE      18/09/2012 11:36:12
 DBA           18/09/2012 11:36:12
 ...

 UPDATE_ECOS   23/04/2013 10:41:16
 SELECT_EFF    23/04/2013 11:44:44

  SELECT  r.role
         ,to_char(ctime,'dd/mm/yyyy hh24:mi:ss') creation_time
  FROM	dba_roles r,
	user$ u
  WHERE  r.ROLE = u.name
  ORDER BY ctime;

  EMGC_GCDomain/instance1/ohs1 Target Down 

Instance: ORCL             Status: OPEN         Startup: 20120912 22.24

 NAME                       DATABASE_ROLE        OPEN_MODE               PROTECTION_MODE      
 -------------------------- -------------------- ----------------------- -------------------- 
 SWITCHOVER_STATUS  STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS     FS_FAILOVER_OBSERVER_HOST
 ------------------ -------------------------- ---------------------- -------------------------
 Database: ORCL             PRIMARY              READ WRITE              MAXIMUM PERFORMANCE  
 TO STANDBY                           10468375 TARGET UNDER LAG LIMIT HOST_075

 Instance: ORCL             Status: OPEN         Startup: 20120912 22.24

 NAME                       DATABASE_ROLE        OPEN_MODE               PROTECTION_MODE      
 -------------------------- -------------------- ----------------------- -------------------- 
 SWITCHOVER_STATUS  STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS     FS_FAILOVER_OBSERVER_HOST
 ------------------ -------------------------- ---------------------- -------------------------
 Database: ORCL             PHYSICAL STANDBY     MOUNTED                 MAXIMUM PERFORMANCE  
 SWITCHOVER PENDING                          0 TARGET UNDER LAG LIMIT HOST_075

 Instance: STDBY            Status: MOUNTED      Startup: 20120912 22.42

 SELECT  'Database: ' || name name, database_role, 
   open_mode, protection_mode, switchover_over, standby_became_primary_scn, 
   fs_failover_status, fs_failover_observer_host
 FROM v$database
   union all
 SELECT 
  'Instance: ' || instance_name, 'Status: ' || status, 
  'Startup: ' || to_char(STARTUP_TIME,'yyyymmdd hh24.mi'), null, null, null, 
  null, null
 FROM v$instance;

 NAME                            VALUE        DEFAULT   TYPE
 ------------------------------- ------------ --------- --------
 __shared_pool_size              1241513984   FALSE     6
 _dm_max_shared_pool_pct         1            TRUE      number
 _enable_shared_pool_durations   TRUE         TRUE      boolean
 _io_shared_pool_size            4194304      TRUE      6
 _shared_pool_max_size           0            TRUE      6
 _shared_pool_minsize_on         FALSE        TRUE      boolean
 _shared_pool_reserved_min_alloc 4400         TRUE      6
 _shared_pool_reserved_pct       5            TRUE      number

 select 	a.ksppinm name,
   b.ksppstvl value,
   b.ksppstdf default,
   decode
   (a.ksppity, 1,
   'boolean', 2,
   'string', 3,
   'number', 4,
   'file', a.ksppity) type
 from
  sys.x$ksppi a,
  sys.x$ksppcv b
 where
   a.indx = b.indx 
   and a.ksppinm like '\_%' escape '\'
   and lower(a.ksppinm) like '%shared_p%'
 order by 1;

   and lower(a.ksppinm) like '%shared_p%'

  MERGE INTO table_1
	USING ( SELECT  a.col_1, 
			b.col_2
		  FROM  tab_2 a,
			tab_3 b,
			tab_4 c
....
....

 ERROR at line 2:
 ORA-00600: internal error code, arguments: [kkmupsViewDestFro_4], [0], [85883], [], []

- The UNDO tablespace or the undo retention are small: in these cases, the DBA have to get
  bigger the UNDO tablespace or the undo retention.
  If, using the db, they receive often the ORA-01555 probably this is the problem.

- They have a big transaction: in this case, the developers should use more “commit” or
  “rollback” in the code

- In the pr_problem there is a big query so you should check its explain plan and if you
  think the explain plan is not good, the developers (or you) should do the performance
  tuning of the query

The summary of its indexes:

 TABLENAME        INDEXNAME          STATUS  INDEX_TYPE            TABLESP_NAME UNIQUENES
 ---------------- ------------------ ------- --------------------- ------------ ---------
 BLEVEL DistinctKeys LAST_ANAL SAMPLE_SIZE  INI_TRANS PARTITIONED
 ------ ------------ --------- ----------- ---------- -----------
 LASSI.ORDERS     LASSI.IDX_OR       N/A     NORMAL                             NONUNIQUE
      0	1782052	19-SET-12      136113            YES

 LASSI.ORDERS     LASSI.IDX_PROVA    VALID   NORMAL                TBS_INDEX    NONUNIQUE
      2 1863974 19-SET-12       41188         30 NO

 LASSI.ORDERS     LASSI.PK_ORDER     VALID   NORMAL                TBS_INDEX    UNIQUE
      2 1778278 19-SET-12      134368         12 NO

 LASSI.ORDERS     LASSI.CLIENT_NAME  VALID   FUNCTION-BASED NORMAL TBS_INDEX    NONUNIQUE
      2 423444 19-SET-12      264726         12 NO


Its indexed columns:

 TABLENAME                INDEXNAME              Column_name        C_Position   C_length
 ------------------------ ---------------------- ------------------ ---------- ----------
 LASSI.ORDERS             LASSI.IDX_OR           OM_SITE_ID                1         22
 LASSI.ORDERS             LASSI.IDX_PROVA        ORDER_ID                  1         22
 LASSI.ORDERS             LASSI.IDX_PROVA         ACCOUNT_NUMBER           2         60
 LASSI.ORDERS             LASSI.IDX_PROVA          TECHNOLOGY              3         22
 LASSI.ORDERS             LASSI.IDX_PROVA           STATUS                 4         22
 LASSI.ORDERS             LASSI.PK_ORDER         ORDER_B                   1         22
 LASSI.ORDERS             LASSI.CLIENT_NAME      SYS_NC00036$              1         60


The expression of its "function-based" indexes:

 TABLENAME          INDEXNAME              COLUMN_EXPRESSION            COLUMN_POSITION
 ------------------ ---------------------- ---------------------------- ---------------
 LASSI.ORDERS       LASSI.CLIENT_NAME      UPPER("CLIENT_LAST_NAME")                  1


The details of its partitioned indexes:

 INDEXNAME    PARTITION_NAM HIGH_VALUE         PARTITION_POSIT TABLESP_NAM STATUS IniTrans
 ------------ ------------- ------------------ --------------- ----------- ------ --------
 LASSI.IDX_OR P136419656_0  13641965, MAXVALUE               1 TBS_INDEX   USABLE        2
 LASSI.IDX_OR P370794502_0  37079450, MAXVALUE               2 TBS_INDEX   USABLE        2
 LASSI.IDX_OR P_MX          MAXVALUE, MAXVALUE               3 TBS_INDEX   USABLE        2


The metadata of its indexes: here is one of them:

 CREATE UNIQUE INDEX "LASSI"."PK_ORDER" ON "LASSI"."ORDERS" ("ORDER_B")
 PCTFREE 10 INITRANS 12 MAXTRANS 255 COMPUTE STATISTICS
 STORAGE(INITIAL 9437184 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "TBS_INDEX"

UDI-31623: operation generated ORACLE error 31623
 ORA-31623: a job is not attached to this session via the specified handle
 ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3263
 ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4488
 ORA-06512: at line 1

alter system set streams_pool_size=40M;

  select LASSI_SEQ.nextval 
  from dual;

  NEXTVAL
  -------
    10089

- Do an ALTER on the sequence:

   alter sequence LASSI_SEQ 
   increment by -10089 minvalue 0;

- Check the nextval value:

   select LASSI_SEQ.nextval 
   from dual;

    NEXTVAL
   --------
          0

- Do another ALTER on the sequence:

   alter sequence LASSI_SEQ 
   increment by 1;

 exec DBMS_STATS.GATHER_TABLE_STATS ('LASSI', 'PT'); 
 *
 ERROR at line 1:
 ORA-20005: object statistics are locked (stattype = ALL)
 ORA-06512: at "SYS.DBMS_STATS", line 10640
 ORA-06512: at "SYS.DBMS_STATS", line 10664
 ORA-06512: at line 1

 SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'PT';

 STATT
 -----
 ALL
 ALL

 2 rows.

 exec DBMS_STATS.unlock_table_STATS ('LASSI', 'PT')

 STATT
 -----


 2 rows.

 exec DBMS_STATS.gather_table_stats ('LASSI', 'PT')

 exec DBMS_STATS.lock_table_STATS ('LASSI', 'PT');

 SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'PT';

 STATT
 -----
 ALL
 ALL

  ORA-28007: the password cannot be reused

 alter user LASSI profile DEFAULT;
 alter user LASSI identified by lassi1; 
 alter user LASSI profile PROF_1;

You have the tables T1 and T2. Those tables have many columns.
You have to find a query that checks their structures in order to see if these are identical:
COLUMN_NAME, DATA_TYPE, DATA_LENGTH, COLUMN_ID.

E.g.:

 create table LASSI.t1
  (c1 char,
  c2 number,
  c3 date);

 create table LASSI.t2
  (c1 varchar2(10),
  c2 number);

 create table LASSI.t3
  (c1 char,
  c2 number,
  c3 date);

If you launch that query between t1 and t2, you receive:

 COLUMN_NAME        DATA_TYPE       DATA_LENGTH  COLUMN_ID     CNT_T1     CNT_T2
 ------------------ --------------- ----------- ---------- ---------- ----------
 C1                 CHAR                      1          1          1          0
 C1                 VARCHAR2                 10          1          0          1
 C3                 DATE                      7          3          1          0

and if you launch the query between t1 and t3, you receive:

 no rows selected

March 2012 - Oracle Password

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

You are in Oracle 11g, you want to know the hashed password (someone call it "encrypted password") of the user LASSI.
In the previous Oracle versions, you could check the column PASSWORD on the DBA_USERS. But in 11g, this value is always null.
So, what you can do in order to see that information?

Solution

In Oracle 11g, you can find this information on SYS.USER$:

  SELECT name, password, ctime created
  FROM sys.user$;

February 2012 - Moving Segments

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

You want to move all segments contained in the tablespace TBS_SOURCE to the tablespaces TBS_TARGET_IDX and TBS_TARGET_DT.
You don't want to use exp/imp, expdp/impdp or online table Redefinition.

In TBS_SOURCE there are these objects types:
- INDEX --> you want them to TBS_TARGET_IDX
- LOBINDEX --> you want them to TBS_TARGET_DT
- LOBSEGMENT --> you want them to TBS_TARGET_DT
- TABLE --> you want them to TBS_TARGET_DT

You have to find a query (from dictionary) that create the correct DDL to move those objects.
For example, when you launch it, you have to see a result like this:

 SEGMENT_TYPE   DDL
 -------------- -------------------------------------------------------------------------
 INDEX          alter index LA.SYS_C0015774 rebuild tablespace TBS_TARGET_IDX;
 INDEX          alter index LA.IDX_LOG_ID_FOUR rebuild tablespace TBS_TARGET_IDX;
 LOBINDEX
 LOBINDEX
 LOBSEGMENT     alter table CT.LOG_B move lob (XML) store as (tablespace TBS_TARGET_DT);
 LOBSEGMENT     alter table LA.LOG_M move lob (XML) store as (tablespace TBS_TARGET_DT);
 TABLE          alter table LA.LOG_EVENTS_BK move tablespace TBS_TARGET_DT;
 TABLE          alter table LA.LOG_POLICY move tablespace TBS_TARGET_DT

And you have to explain why you don't need a DDL to move the LOBINDEXes?

January 2012 - resource busy error

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

You are in Oracle 10g and you launch this command:

 alter table EMP initrans 5; 

 ERROR at line 1:
 ORA-00054: resource busy and acquire with NOWAIT specified
 ORA-06512: at line 3

You have to write an anonymous block that tries to do this operation 10 times (waiting for 0,2 seconds between every attempt).
For example, you launch it and, at the third attempt, the command was successful, so on the screen you see:

 Success: No
 Success: No
 Success: YES

Otherwise, which Oracle feature can you use if you are in Oracle 11g?

December 2011 - Db Analysis

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

You have to write a PL/SQL program that do a short "db analysis".
When you launch it, you must receive a result like this:

************************************************************************
************************************************************************
   Analysis Date: 07/12/2011 16:33:42
************************************************************************
************************************************************************
 
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
 
Database: 
 Dbid: 324368697       Name: DBLOR
 Created: 18-AGO-11    Log_mode: ARCHIVELOG

Instance: 
 Instance Number: 1     Name: DBLOR
 Parallel: NO           Host name: mi088lor
 Archiver: STARTED      Startup Time: 05/10/2011 16:08
 
SGA Statistics:
 Buffer Cache Hit Ratio:   97.384 %
 Library Cache Miss Ratio:   0.912 %
 Dictionary Cache Miss Ratio:   0.984 %

November 2011 - Contention and "Metadata query"

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

One your colleague, says that the EMP table and its indexes have contention.
Of course this is an "application issue" but, as a DBA, you could reduce this problem (for the "future rows") by changing the INITRANS setting.
So you have to find a "metadata query" that:
- return the commands you could launch in order to apply this changing
- return the current INITRANS

Result Example:

  COMMAND                                 INI_TRANS
  -------------------------------------- ----------
  alter index SCOTT.EMP_IDX2 initrans 6; 	2
  alter index SCOTT.EMP_IDX1 initrans 6; 	2
  alter table SCOTT.EMP initrans 5;		1

You have to find it without knowing the indexes names.

October 2011 - Initialization Parameters

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

You are in Oracle 10.2. and you want to avoid errors like :

 ORA-00018: maximum number of sessions exceed
 ORA-00020: maximum number of processes (%s) exceeded

To avoid them, you want to set correctly the parameters "processes" and "sessions".
To do this analyze, you have to find a query that has result like this:

 RESOURCE_NAME  CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
 -------------- ------------------- --------------- -----------
 processes                      130             133         150
 sessions                       135             159         170

Solution

You should use the view v$resource_limit : it displays information regarding some of the system resources:

  SELECT
    resource_name, 
    current_utilization, 
    max_utilization, 
    limit_value
  FROM
    v$resource_limit
  WHERE
    resource_name in ('sessions', 'processes');

September 2011 - DDL generation problem

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

You are in Oracle 10.2.
You have an existing DBMS_SCHEDULER job called FIND_JOB inside the schema SCOTT.
You are using SQL*Plus and you want to generate the DDL of this job.
How can you do it?

Solution

You can use the function dbms_metadata.get_ddl.
Its output is a CLOB so in order to see the result in SQL*Plus, you have to launch:

  set long 2000

After this, you can use this query (the type of this object is PROCOBJ):

  SELECT dbms_metadata.get_ddl('PROCOBJ', 'FIND_JOB', 'SCOTT') 
  FROM dual;

August 2011 - Export Problem

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

You are in Oracle 11.2.0.1.
You use the "original" export (no datapump) in order to export the schema called LASSI and you receive this error:

  EXP-11 Table Does Not Exist or Table is Ignored

Why?
Which query can you launch before the export in order to see if you could receive this error?
How can you avoid it?
Which metalink note is useful to read?

July 2011 - Invalid Views

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

You have to find a query that shows the invalid views in your db.
This query must be executed in SQL*Plus and you want a result like this:

 INVALID_OBJECT		TEXT
 ----------------------- ---------------------------------------
 LASSI.VW_S_ORDER_ITEM   select row_id,
                         PAR_ROW_ID,
                         S_ORDER_ITEM_X.ATTRIB_26,
                         S_ORDER_ITEM_X.ATTRIB_27
                         from pippo.S_ORDER_ITEM_X

 LASSI.S_EXTRACT_NODE    SELECT
                         T1.RULE_ID     RULE_ID,
                         T1.NODE_ID     NODE_ID,
                         T1.GROUP_CD    GROUP_CD,
                         T2.NAME        RULE_NAME
                         FROM S_EXTDATA_N T1, S_EXTDATA_R T2
                         WHERE T1.RULE_ID = T2.ROW_ID

Solution

You have to set up the SQL*Plus environment. Example:

  set lines 155 pages 66
  col invalid_object for a33
  set long 2000

And, after this, you can launch the query:

  select o.owner || '.' || o.object_name invalid_object, v.text
  from dba_objects o,
       dba_views v
  where status != 'VALID'
        AND object_type = 'VIEW'
        AND o.owner = v.owner
        AND o.object_name = v.view_name;

June 2011 - Table Access

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

In the database, you have many "physical reads" on a small table called TAB_1.
Then you can try to improve the queries that read it and you can do others 2 things in order to improve the access to it.
Find these 2 other solutions.

May 2011 - SQL and Files

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

You have to find a query that shows the datafiles and tempfiles created in the last X days, where X is a dynamic variable defined like this: &days.
For example, I want to know the files created in the last 90 days then my result is:

 TYPE   TABLESPACE_NAME    FILE_NAME                                   MB   CREATION
 ------ ------------------ ------------------------------------- -------- ----------
 DATAF  TS_TSOLC_TAB       /oracle/dblor/data0/TS_TAB_d02.dbf       10240  22-FEB-11
 DATAF  TS_TSOLC_TAB       /oracle/dblor/data0/TS_TAB_d03.dbf       10240  22-APR-11
 DATAF  USERS              /oracle/dblor/data0/users02.dbf          16384  14-FEB-11
 TEMPF. TS_ACN_UNB_TEMP    /oracle/dblor/temp0/TS_ACN_TEMP.dbf       5120  14-FEB-11

April 2011 - Archiving

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

You have to find a query that shows the amount of redo logs generated every 10 minutes in the last 2 hours.
Then you want a result like this:

  DATE                   N.Archive     Tot.MB
  ------------------- ------------ ----------
  From 2011-01-25 17.20          3        708
  From 2011-01-25 17.30          3        702
  From 2011-01-25 17.40          1        234
  From 2011-01-25 17.50          2        469
  From 2011-01-25 18.00          1        234
  From 2011-01-25 18.10          2        471
  From 2011-01-25 18.20          2        469
  From 2011-01-25 18.30          2        468
  From 2011-01-25 18.40          1        234
  From 2011-01-25 18.50          2        468
  From 2011-01-25 19.00          2        480
  From 2011-01-25 19.10          1        144
  From 2011-01-25 19.20          1          0

Solution

You can see the view v$archived_log:

 select 'From '|| substr(to_char(al.completion_time,'YYYY-MM-DD hh24.mi'),1,15) || 0 date,
      count(al.recid) "N.Archive",
      round(sum ((al.blocks+1)*al.block_size)/1048576) "Tot.MB" 
 from v$archived_log al 
 where al.completion_time >=sysdate-1/12
 group by substr(to_char(al.completion_time,'YYYY-MM-DD hh24.mi'),1,15) 
 order by 1;

March 2011 - Auditing

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

You have the auditing activated in Oracle9i.
You upgrade the db to the 10.2.0.5 Oracle version.
After that, you see that Oracle writes in the sys.aud$ but the result of one query is strange:

 SELECT count(1)
 FROM sys.aud$
 WHERE TIMESTAMP# IS NOT NULL;

 COUNT(1)
 --------
       0

You used this query in Oracle9i and now you expected to receive a result different than 0.
What is the reason?
Which metalink notes can you refer to?

Solution

In the Oracle db version 10gR1 and above, the TIMESTAMP# column is obsoleted in favour of the new NTIMESTAMP# column.

Since datatype of TIMESTAMP# (date) is coarse and can not capture fractional seconds and timezone, NTIMESTAMP# column is introduced of datatype timestamp(6).
The old column is deprecated.

Metalink ID 427296.1

February 2011 - Alert.log

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

Your Oracle server is crashed.
After it works again, you check the db.
It seems to work fine but some people say that they can't connect to the db and in the alert.log you found this:

 Tue Jan 11 12:10:28 2011
 Errors in file /oracle/db_lor/admin/bdump/db_lor_qmn0_5525654.trc:
 ORA-00600: internal error code, arguments: [4194], [73], [77], [], [], [], [], []
 Doing block recovery for fno: 2 blk: 43
   block recovery range from rba 17687.5432.0 to scn 1972.2718780554 

Which metalink notes can you refer to?
How can you fix the problem?

January (2) 2011 - Listener Problem

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

You want to see the status of a listener and you receive this error:

  ordblor # lsnrctl status LS_DBLOR_1521

  LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production on 9-JAN-11 11:17

  Copyright (c) 1991, 2006, Oracle Corporation.  All rights reserved.

  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mi011)(PORT=1521)))
  TNS-01169: The listener has not recognized the password

Why have you received this error?
How can you avoid it?

Solution

You have received this error because you have implemented the password in the listener.
The password in the listener was useful especially in Oracle 9i for security reasons.

To avoid the error here described, you have to look at inside the file listener.ora .
At the end of this file, you find something this:

  PASSWORDS_ls_dblor_1521 = B5C1F55A82F9E241

Then you have to launch these commands:

  1. lsnrctl
  2. set current_listener LS_DBLOR_1521
  3. set password B5C1F55A82F9E241
  4. status

January 2011 - Oracle 11g Installation

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

Using OUI, you are installing Oracle 11.2.0.2 server on AIX machines (AIX 5.3 and AIX 6.1).
At Step 10 ("Perform Prerequisite Checks"), you receive errors like these:

 1. free space: server88:/tmp : Expected Value 1GB,  Actual Value 100MB
 2. Checking for rsct.basic.rte(0.0); Not found
 3. Checking for rsct.compat.clients.rte(0.0); Not found
 4. APARs IZ42940, IZ49516, IZ52331 are missing
 5. APARs IZ41855, IZ51456, IZ52319 are missing

Can you ignore them? In which metalink notes can you find these problems?

December 2010 - Statspack

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

You want to delete all the data inside the tables in the STATSPACK schema that are older than 1 year.
What is a sample way to do it?

Solution

The stats$snapshot is the main table in the STATSPACK.
This table contains the snapshot ID for all of the subordinate tables and the SNAP_TIME indicates when the snapshot was taken.

The stats$snapshot table can be deleted in order to delete rows from all of the subordinate tables, so in this case you can launch:

 Delete stats$snapshot 
 where SNAP_TIME>sysdate-365;

November 2010 - Secret Code

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

You are in Oracle 10g and you have a PL/SQL procedure called pr_trunc.
This procedure can be used but you want that nobody (neither the schema owner) can see its source.
How can you do it?

October 2010 - Index Use

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

You have the index LASSI.idx_1.

You want to discover whether it is used, or not:
- how can you enable this check?
- which view you have to examine if you are connected as LASSI? and if you are connected as SYS?
- how can you stop this check?

September (2) 2010 - Package Compilation

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

You are trying to compile a package but you receive the error

  ORA-04021: timeout occurred while waiting to lock object 

You want to know who is using it. How can you do it?
You want a result like this:

  (103) - LASSI
  (211) - USER2

where 103 and 211 are the SIDs and LASSI and USER2 are the users that are using the package.

Solution

You have to create the procedure who_is_using in the SYS schema.
You find it in metalink.
Then you launch that procedure. Example where PKG_LASSI is the package that you want to compile:

 set serveroutput on

 exec who_is_using ('PKG_LASSI')
  (103) - LASSI
  (211) - USER2

September 2010 - Hidden Parameters

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

You have to write a query that find out your hidden parameters and some its characteristics.
Example of its result:

  NAME                                  VALUE        DEFLT     TYPE
  ------------------------------------- ------------ --------- ----------------
  _abort_recovery_on_join               FALSE        TRUE      boolean
  _active_standby_fast_reconfiguration  TRUE         TRUE      boolean
  _adaptive_direct_read                 TRUE         TRUE      boolean
  _adaptive_fetch_enabled               TRUE         TRUE      boolean
  _adjust_literal_replacement           FALSE        TRUE      boolean
  _affinity_on                          TRUE         TRUE      boolean
  _aiowait_timeouts                     100          TRUE      number
  ...

Solution

This is a good query :

 select a.ksppinm name,
	b.ksppstvl value,
	b.ksppstdf deflt,
	decode
	(a.ksppity, 1,
	'boolean', 2,
	'string', 3,
	'number', 4,
	'file', a.ksppity) type
	--,a.ksppdesc description
 from
	sys.x$ksppi a,
	sys.x$ksppcv b
 where
	a.indx = b.indx  AND
	a.ksppinm like '\_%' escape '\'
 order by 1;

August (2) 2010 - Move Files

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

You are in Unix and inside the directory /oracle/db_lor/dbms102/rdbms/audit
you have thousands of files having the name starting with "ora_1".

You want to move them inside the directory
/oracle/db_lor_audit_ora_1

You try it normally, but you receive one error:

  mv ora_1* /oracle/db_lor_audit_ora_1/
  bash: /bin/mv: Argument list too long

How can you do this?

August 2010 - SQL Query

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

You want to find the tablespaces that have the datafiles only in /oracled02.
Find out the query that do this.

Solution

Launch this:

 select distinct tablespace_name  
 from dba_data_files 
 where file_name  like '/oracled02%'
   minus
 select tablespace_name  
 from dba_data_files 
 where file_name not like  '/oracled02%';

As I wrote, after the “minus”, you don’t need the “distinct”.

July 2010 - Parameter Problem

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

You have created a single db instance in Oracle 10.2.
When you start the instance, you receive the error ORA-32004 :

 SQL> startup

 ORA-32004: obsolete and/or deprecated parameter(s) specified
 ORACLE instance started.

 Total System Global Area  981467136 bytes
 Fixed Size                  2088800 bytes
 Variable Size             436207776 bytes
 Database Buffers          536870912 bytes
 Redo Buffers                6299648 bytes

 Database mounted.
 Database opened.

How can you find which is the "wrong" parameter ?

You find that the "wrong" parameter is max_commit_propagation_delay.
You are using a modified SPFILE copied from a RAC db.
How can you adjust that parameter?

Solution

You will find that parameter inside the alert.log :

 ...
  pga_aggregate_target     = 536870912
  _optimizer_connect_by_cost_based= FALSE
  _right_outer_hash_enable = FALSE
 Deprecated system parameters with specified values:
  max_commit_propagation_delay
 End of deprecated system parameter listing
 Wed Apr  7 16:55:36 2010
 Oracle instance running with ODM: Veritas 5.0 ODM Library, Version 1.1
 PMON started with pid=2, OS id=1298548
 PSP0 started with pid=3, OS id=1290286
 ...

You can change that parameter using this command:

 alter system reset max_commit_propagation_delay
 scope=spfile
 sid='*';

After that, you have to do the bounce of the database.

June 2010 - Disable Db links

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

You are in Oracle 10g and you want to disable one database link without dropping it.
Actually, in Oracle the Database links cannot be altered.
But there is a workaround.
Which is it?

Solution

A simple workaround of this problem is: put comments inside the entry in tnsnames.ora.
For example:
the db link uses this TNS entry:

db_lor =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.122.144 )(PORT = 1521)))
    (CONNECT_DATA =
      (SERVICE_NAME = db_lor ))
  )

It should became like this:

# db_lor =
#   (DESCRIPTION =
#     (ADDRESS_LIST =
#       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.122.144 )(PORT = 1521)))
#     (CONNECT_DATA =
#       (SERVICE_NAME = db_lor ))
#   )

Of course, you can do this only if this connection is used only by that database link!

May 2010 - Number of Sessions

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

You want to do a stored procedure in order to check every 30 seconds how many sessions (active and inactive) are on your db:
When you launch this check, the procedure must do 10 snapshots and you must see graphically the result.
Example of the result:

 Time                Active Ses    Inact Ses        Active | Inactive
 20091119 16.33.01       13           229             **** | ***************
 20091119 16.33.31       14           227             **** | ***************
 20091119 16.34.01       16           225             **** | ***************
 20091119 16.34.31       16           226             **** | ***************
 20091119 16.35.01       13           229             **** | ***************
 20091119 16.35.31       13           229             **** | ***************
 20091119 16.36.01       16           223             **** | ***************
 20091119 16.36.31       22           210            ***** | **************
 20091119 16.37.03       22           220            ***** | ***************
 20091119 16.37.33       15           245             **** | ****************

Solution

You have to use the view v$session and the functions LPAD, RPAD and dbms_lock.sleep:

set serveroutput on size 1000000;

declare

 v_sessions	varchar2(2000) := null; -- USEFUL FOR THE CURSOR RESULT
 v_snapshots	pls_integer := 10;	-- NUMBER OF SNAPSHOTS
 v_wait		pls_integer := 30;	-- NUMBER OF SECONDS BETWEEN THE SNAPSHOT

BEGIN

 dbms_output.put_line (' Time                Active Ses    Inact Ses        Active | Inactive');

 for i in 1..v_snapshots loop

	select  to_char(sysdate,'yyyymmdd hh24.mi.ss') || 
 		LPAD(sum(decode(status,'ACTIVE',count(1))),9,' ')  ||
 	 	LPAD(sum(decode(status,'INACTIVE',count(1))),14,' ') || '  ' ||
		LPAD(LPAD('*',round(sqrt(sum(decode(status,'ACTIVE',count(1))))) ,'*'),15,' ')
		|| ' | ' || 
		RPAD('*',round(sqrt(sum(decode(status,'INACTIVE',count(1))))),'*')
	into v_sessions
	from v$session
	group by to_char(sysdate,'yyyymmdd hh24.mi.ss'),
		 status;

	dbms_output.put_line (v_sessions);

	if i != v_snapshots then 
		dbms_lock.sleep(seconds => v_wait);
	end if;

 end loop;

end;

April 2010 - Tablespace Problem

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

Your db is in Oracle 9.2.0.6.0 in a Sun Microsystems machine.
The datafiles are in the File System (FS) /oracle/dblor/data.
The tempfile is in the FS /oracle/dblor/temp.

The FS /oracle/dblor/temp is totally full then you ask the UNIX group to extend it.
But they say that in order to do this the FS can't be totally full.

In it there is only one file and it's the file of the temporary tablespace TEMP.
Its size is 300 MB and it is in AUTOEXTEND till 400 MB.

Which is the 2 ways to check if the TEMP is offline?
How you can bring it again online?

After that you bring it online, you could make that file a little bit smaller (295M) but you want to be sure that, till the FS will not be larger, new sessions will not use it.
What are the last steps in order to solve your problem?
And why could be useful the view database_properties?

March 2010 - Tablespaces Changed

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

You want to know the names of the tablespaces where at least one datafile has been added today.
You don't have access to the file system then you have to find them using a SQL query.
Which is this query?

Solution

You could launch this join:

 select  distinct tbs.name
 from  v$datafile dbf,
       v$tablespace tbs
 where trunc(dbf.CREATION_TIME)=trunc(sysdate) and
       dbf.ts# = tbs.ts#;

February 2010 - DB Link

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

In the machine SERVER_1, you have the database DB1 and in the TNSNAMES.ORA you have this entry:

 db2.world =
   (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER_2)(PORT = 1521))
    )
    (CONNECT_DATA =
     (SERVICE_NAME = DB2000)
    )
   )

Then you create a database link:

  CREATE DATABASE LINK DB2000
  CONNECT TO user1 IDENTIFIED BY pwd1
  USING 'db2.world';

From the SERVER_1, you try this database link and it works:

  select * from dual@DB2000;

  D
  -
  X

Then, you try the same query from a client connected to the DB1 and you receive this error:

  "ORA-12154: TNS:could not resolve the connect identifier specified"

How you could create the database link in order to avoid this error?

Solution

In the database link creation, you could use the TNSNAMES.ORA entry:

  CREATE DATABASE LINK DB2000
  CONNECT TO user1 IDENTIFIED BY pwd1
  USING '(DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER_2)(PORT = 1521))
    )
    (CONNECT_DATA =
     (SERVICE_NAME = DB2000)
    )
   )';

January 2010 - SQL tuning

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

You want to do a SQL tuning of this:

  SELECT COUNT(*) 
  FROM tab_1 , tab_2
  WHERE tab_1.IDDOC = tab_2.IDDOC 
	AND tab_1.LASTVERSION = tab_2.VERSION
	AND tab_2.DIR = :b1;

Then you decide to use an Oracle event:
Which undocumented Oracle event is useful in this situation?
And which level you have to use in this event if you want to have also the wait event (as the example below)?

 Elapsed times include waiting on following events:
   Event waited on                 Times   Max. Wait  Total Waited
   -----------------------------  Waited  ----------  ------------
   SQL*Net message to client           3        0.00          0.00
   SQL*Net message from client         3       16.10         16.14
   db file sequential read            32        0.00          0.02

And how you can stop the Oracle event?

Solution

You have to use the event 10046 and, after, use the TKPROF to interpret the trace file.
In order to activate this event :

  alter session set events '10046 trace name context forever, level num'; 

Where num is either 1,4,8 or 12, with:
- 1 being the same as a normal set sql_trace=true,
- 4 including values of bind variables,
- 8 including wait events,
- 12 including both bind variables and wait events.
Then in this example, you have to use the level 12:

  alter session set events '10046 trace name context forever, level 12'; 

In order to stop it:

  alter session set events '10046 trace name context off';

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