tag:blogger.com,1999:blog-289354782009-12-18T20:47:32.724ZOracle tips, tricks and information.ddfhttp://www.blogger.com/profile/01396093972232442857noreply@blogger.comBlogger55125tag:blogger.com,1999:blog-28935478.post-76864424307634109082009-12-10T14:52:00.005Z2009-12-18T16:35:59.071Z2009-12-18T16:35:59.071ZIt's entirely possible that a query execution plan includes the following peculiar entry: <pre><span style="color:#3333ff;"> COLLECTION ITERATOR (PICKLER FETCH) </span></pre> Intriguing, at the very least. What, exactly, IS a pickler fetch? To answer that we must answer another question: <pre><span style="color:#3333ff;"> What is pickled data? </span></pre> so let's start there and see where that leads us.<br /><br /> Packed object data cannot be serialized directly, it requires some manipulation. This manipulation results in a more 'palatable' data stream presented to the receiving process or interface. As an example let's return the output from the dbms_xplan.display function without any additional 'magic': <pre><span style="color:#3333ff;">SQL> select dbms_XPLAN.DISPLAY FROM DUAL; DISPLAY(PLAN_TABLE_OUTPUT) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DBMS_XPLAN_TYPE_TABLE(DBMS_XPLAN_TYPE(' '), DBMS_XPLAN_TYPE('-------------------------------------------------------------------------------'), DBMS_XPLAN_TYPE('| Id | Operation | Name | Rows | Bytes | Cost |'), DBMS_XPLAN_TYPE('-------------------------------------------------------------------------------'), DBMS_XPLAN_TYPE('| 0 | SELECT STATEMENT | | 8169 | 1412K| 102 |'), DBMS_XPLAN_TYPE('| 1 | VIEW | DBA_OBJECTS | 8169 | 1412K| 102 |'), DBMS_XPLAN_TYPE('| 2 | UNION-ALL | | | | |'), DBMS_XPLAN_TYPE('|* 3 | FILTER | | | | |'), DBMS_XPLAN_TYPE('| 4 | NESTED LOOPS | | 1 | 235 | 18 |'), DBMS_XPLAN_TYPE('|* 5 | TABLE ACCESS FULL | OBJ$ | 1 | 205 | 17 |'), DBMS_XPLAN_TYPE('| 6 | TABLE ACCESS CLUSTER | USER$ | 1 | 30 | 1 |'), DBMS_XPLAN_TYPE('|* 7 | INDEX UNIQUE SCAN | I_USER# | 1 | | |'), DBMS_XPLAN_TYPE('|* 8 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 26 | 2 |'), DBMS_XPLAN_TYPE('|* 9 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 |'), DBMS_XPLAN_TYPE('| 10 | NESTED LOOPS | | 8168 | 941K| 84 |'), DBMS_XPLAN_TYPE('| 11 | TABLE ACCESS FULL | LINK$ | 82 | 7216 | 2 |'), DBMS_XPLAN_TYPE('| 12 | TABLE ACCESS CLUSTER | USER$ | 100 | 3000 | 1 |'), DBMS_XPLAN_TYPE('|* 13 | INDEX UNIQUE SCAN | I_USER# | 1 | | |'), DBMS_XPLAN_TYPE('--------------------------------------- ----------------------------------------'), DBMS_XPLAN_TYPE(' '), DBMS_XPLAN_TYPE('Predicate Information (identified by operation id):'), DBMS_XPLAN_TYPE('--------------------------------------------- ------'), DBMS_XPLAN_TYPE(' '), DBMS_XPLAN_TYPE(' 3 - filter("SYS_ALIAS_1"."TYPE#"<>1 AND "SYS_ALIAS_1"."TYPE#"<>10 OR '), DBMS_XPLAN_TYPE(' "SYS_ALIAS_1"."TYPE#"=1 AND (SELECT /*+ */ 1 FROM "SYS"."IND$" "I" WHERE '), DBMS_XPLAN_TYPE(' "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR '), DBMS_XPLAN_TYPE(' "I"."TYPE#"=4 OR "I"."TYPE#"= 6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)'), DBMS_XPLAN_TYPE(' 5 - filter("SYS_ALIAS_1"."LINKNAME" IS NULL AND '), DBMS_XPLAN_TYPE(' "SYS_ALIAS_1"."NAME"<>''_NEXT_OBJECT'' AND '), DBMS_X PLAN_TYPE(' "SYS_ALIAS_1"."NAME"<>''_default_auditing_options_'')'), DBMS_XPLAN_TYPE(' 7 - access("SYS_ALIAS_1"."OWNER#"="U"."USER#")'), DBMS_XPLAN_TYPE(' 8 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 '), DBMS_XPLAN_TYPE(' OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)'), DBMS_XPLAN_TYPE(' 9 - access("I"."OBJ#"=:B1)'), DBMS_XPLAN_T YPE(' 13 - access("L"."OWNER#"="U"."USER#")'), DBMS_XPLAN_TYPE(' '), DBMS_XPLAN_TYPE('Note: cpu costing is off')) SQL> </span></pre> We can read the result, but it doesn't look much like the usually formatted PLAN_TABLE output. Using the TABLE() function provides something more recognizable: <pre><span style="color:#3333ff;">SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8169 | 1412K| 102 | | 1 | VIEW | DBA_OBJECTS | 8169 | 1412K| 102 | | 2 | UNION-ALL | | | | | |* 3 | FILTER | | | | | | 4 | NESTED LOOPS | | 1 | 235 | 18 | |* 5 | TABLE ACCESS FULL | OBJ$ | 1 | 205 | 17 | | 6 | TABLE ACCESS CLUSTER | USER$ | 1 | 30 | 1 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ |* 7 | INDEX UNIQUE SCAN | I_USER# | 1 | | | |* 8 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 26 | 2 | |* 9 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 | | 10 | NESTED LOOPS | | 8168 | 941K| 84 | | 11 | TABLE ACCESS FULL | LINK$ | 82 | 7216 | 2 | | 12 | TABLE ACCESS CLUSTER | USER$ | 100 | 3000 | 1 | |* 13 | INDEX UNIQUE SCAN | I_USER# | 1 | | | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ 3 - filter("SYS_ALIAS_1"."TYPE#"<>1 AND "SYS_ALIAS_1"."TYPE#"<>10 OR "SYS_ALIAS_1"."TYPE#"=1 AND (SELECT /*+ */ 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) 5 - filter("SYS_ALIAS_1"."LINKNAME" IS NULL AND "SYS_ALIAS_1"."NAME"<>'_NEXT_OBJECT' AND "SYS_ALIAS_1"."NAME"<>'_default_auditing_options_') 7 - access("SYS_ALIAS_1"."OWNER#"="U"."USER#") 8 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ 9 - access("I"."OBJ#"=:B1) 13 - access("L"."OWNER#"="U"."USER#") Note: cpu costing is off 37 rows selected. SQL> </span></pre> The TABLE() function "pickles", or converts, the object data to a byte stream and formats the output. Using the TABLE() function introduces the "COLLECTION ITERATOR (PICKLER FETCH)" step in the execution plan (the second execution plan is for the "select * from table(dbms_xplan.display)" query): <pre><span style="color:#3333ff;">SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8169 | 1412K| 102 | | 1 | VIEW | DBA_OBJECTS | 8169 | 1412K| 102 | | 2 | UNION-ALL | | | | | |* 3 | FILTER | | | | | | 4 | NESTED LOOPS | | 1 | 235 | 18 | |* 5 | TABLE ACCESS FULL | OBJ$ | 1 | 205 | 17 | | 6 | TABLE ACCESS CLUSTER | USER$ | 1 | 30 | 1 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------- |* 7 | INDEX UNIQUE SCAN | I_USER# | 1 | | | |* 8 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 26 | 2 | |* 9 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 | | 10 | NESTED LOOPS | | 8168 | 941K| 84 | | 11 | TABLE ACCESS FULL | LINK$ | 82 | 7216 | 2 | | 12 | TABLE ACCESS CLUSTER | USER$ | 100 | 3000 | 1 | |* 13 | INDEX UNIQUE SCAN | I_USER# | 1 | | | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------- 3 - filter("SYS_ALIAS_1"."TYPE#"<>1 AND "SYS_ALIAS_1"."TYPE#"<>10 OR "SYS_ALIAS_1"."TYPE#"=1 AND (SELECT /*+ */ 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) 5 - filter("SYS_ALIAS_1"."LINKNAME" IS NULL AND "SYS_ALIAS_1"."NAME"<>'_NEXT_OBJECT' AND "SYS_ALIAS_1"."NAME"<>'_default_auditing_options_') 7 - access("SYS_ALIAS_1"."OWNER#"="U"."USER#") 8 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------- 9 - access("I"."OBJ#"=:B1) 13 - access("L"."OWNER#"="U"."USER#") Note: cpu costing is off 37 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=17 Card=8168 Bytes=16336) 1 0 <span style="color:#009900;">COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'</span> Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 51 consistent gets 0 physical reads 0 redo size 3384 bytes sent via SQL*Net to client 678 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 15 sorts (memory) 0 sorts (disk) 37 rows processed SQL> SQL></span></pre> Selecting from a function usually involves a picker fetch to format the output into a 'user-friendly' form, and, usually, it doesn't introduce massive amounts of overhead with respect to query processing. I have rarely seen such a step add significantly to the response time (both queries shown above ran in about the same time). Yes, there may be situations where each added step degrades the response time but if that happens investigate the situation fully for you may find your assumptions in error.<br /><br /> It's not so bad to be 'in a pickle' when it comes to queries.<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-7686442430763410908?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-62622760165786631522009-04-23T16:21:00.003+01:002009-04-23T17:46:52.892+01:002009-04-23T17:46:52.892+01:00Views are interesting constructs in Oracle. They can be built on a single table, they can contain aggregate data, they can be built on two or more tables joined together, they can even be built on other views. Regardless of all of that, sometimes users may want to update data through a view and, depending upon how the view is built those updates may or may not be allowed. Short of making the attempt and receiving the following dismal message:<br /><br /> <pre><span style="color:#009900;">ERROR at line 2: ORA-01779: cannot modify a column which maps to a non key-preserved table </span></pre> how do you know if you can update a particular view? Ask Oracle, of course.<br /><br /> Oracle, in its infinite wisdom, provides three views to reveal which views can be updated, and even which columns in those views have modifiable data. These views are: <pre><span style="color:#009900;">USER_UPDATABLE_COLUMNS ALL_UPDATABLE_COLUMNS DBA_UPDATABLE_COLUMNS </span></pre> The level of access in the database determines which view will provide the desired information; the USER-named view reports on all tables/views owned by the connected user, the ALL-named view reports on all tables and views the connected user can access regardless of ownership, and the DBA-named view reports on all tables and views in the database. Usually the USER_UPDATABLE_COLUMNS view should be used; a sample query is shown below: <pre><span style="color:#009900;">SQL> select owner, table_name, column_name, updatable 2 from user_updatable_columns; OWNER TABLE_NAME COLUMN_NAME UPD ------------------------------ ------------------------------ ------------------------------ --- BING AA CUCD1 YES BING AA CVRA YES BING AA CONV_RESULT YES BING BONUS ENAME YES BING BONUS JOB YES BING BONUS SAL YES BING BONUS COMM YES BING BONUS DNAME YES BING CPU_APPLY_VERSION INSTANCE_NAME YES BING CPU_APPLY_VERSION HOST_NAME YES BING CPU_APPLY_VERSION VERSION YES BING CPU_APPLY_VERSION COMMENTS YES BING CPU_APPLY_VERSION ACTION_TIME YES BING CPU_APPLY_VERSION_HOLD INSTANCE_NAME YES BING CPU_APPLY_VERSION_HOLD HOST_NAME YES BING CPU_APPLY_VERSION_HOLD VERSION YES BING DBAOBJS OWNER NO BING DBAOBJS OBJECT_NAME NO BING DBAOBJS SUBOBJECT_NAME NO BING DBAOBJS OBJECT_ID NO BING DBAOBJS DATA_OBJECT_ID NO BING DBAOBJS OBJECT_TYPE NO BING DBAOBJS CREATED NO BING DBAOBJS LAST_DDL_TIME NO BING DBAOBJS TIMESTAMP NO BING DBAOBJS STATUS NO BING DBAOBJS TEMPORARY NO BING DBAOBJS GENERATED NO BING DBAOBJS SECONDARY NO BING DBAOBJS NAMESPACE NO BING DBAOBJS EDITION_NAME NO ... </span></pre> This, of course, returns rows for tables and views; restricting this to just the views is a simple task: <pre><span style="color:#009900;">SQL> select owner, table_name, column_name, updatable 2 from user_updatable_columns 3 where table_name in (select view_name from user_views); OWNER TABLE_NAME COLUMN_NAME UPD ------------------------------ ------------------------------ ------------------------------ --- BING EMP_VIEW EMPNO YES BING EMP_VIEW ENAME YES BING EMP_VIEW JOB YES BING EMP_VIEW MGR YES BING EMP_VIEW HIREDATE YES BING EMP_VIEW SAL YES BING EMP_VIEW COMM YES BING EMP_VIEW DEPTNO YES BING V_BASE OBJECT_NAME NO BING V_BASE SUBOBJECT_NAME NO BING V_BASE OBJECT_ID NO BING V_BASE DATA_OBJECT_ID NO BING V_BASE OBJECT_TYPE NO BING V_BASE CREATED NO BING V_BASE LAST_DDL_TIME NO BING V_BASE TIMESTAMP NO BING V_BASE STATUS NO BING V_BASE TEMPORARY NO BING V_BASE GENERATED NO BING V_BASE SECONDARY NO BING V_BASE NAMESPACE NO BING V_BASE EDITION_NAME NO BING YINGYONG SNERM NO BING YINGYONG FLANG NO BING YINGYONG GLERBIT NO BING YINGYONG DRONK NO 26 rows selected. SQL></span></pre> In this example it's possible to reduce the output considerably since all columns in a given view report the same value for UPDATABLE: <pre><span style="color:#009900;">SQL> select distinct owner, table_name, updatable 2 from user_updatable_columns 3 where table_name in (select view_name from user_views); OWNER TABLE_NAME UPD ------------------------------ ------------------------------ --- BING EMP_VIEW YES BING V_BASE NO BING YINGYONG NO SQL></span></pre> Now we know that only the EMP_VIEW is updatable in this schema. Updatable views across schemas can also be found using the following query: <pre><span style="color:#009900;">select distinct owner, table_name, updatable from all_updatable_columns where table_name in (select view_name from all_views) and updatable = 'YES'; </span></pre> The list generated by that query may be quite large.<br /><br /> There is another way around the problem of updating data in an otherwise non-updatable view, and that is the INSTEAD OF trigger, which fires instead of the coded action (insert, update, delete) and bypasses the view by performing the requested action on the base tables. And that is a subject for another post.<br /><br /> So, it's easy to find the views a user can update because Oracle knows which views those are. Neat.<br /><br /> They call it Oracle for a reason.<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-6262276016578663152?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-82615988102354382022009-03-19T14:48:00.004Z2009-04-21T17:06:06.805+01:002009-04-21T17:06:06.805+01:00There are still databases in existence using dictionary-managed tablespaces, which means that the SYSTEM tablespaces in these databases are also dictionary-managed (which presents other issues besides the extent management, such as using the SYSTEM tablespace as a temporary tablespace and having a TEMP tablespace utilizing permanent files). Converting these tablespaces 'in-place' <em><span style="color:#3333ff;">is</span></em> possible with a packaged procedure provided by Oracle, but this method doesn't work very well in terms of making the dictionary-managed tablespace a true locally managed one. Let's look at the results of a conversion and see where this method falls short of the mark.<br /><br /> DBA_TABLESPACES contains information on the extent management, extent sizing, block_size, status and other various aspects of all of the tablespaces in a given database. The current description is: <pre><span style="color:#6600cc;">SQL> desc dba_tablespaces Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) BLOCK_SIZE NOT NULL NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NOT NULL NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER MIN_EXTLEN NUMBER STATUS VARCHAR2(9) CONTENTS VARCHAR2(9) LOGGING VARCHAR2(9) FORCE_LOGGING VARCHAR2(3) EXTENT_MANAGEMENT VARCHAR2(10) ALLOCATION_TYPE VARCHAR2(9) PLUGGED_IN VARCHAR2(3) SEGMENT_SPACE_MANAGEMENT VARCHAR2(6) DEF_TAB_COMPRESSION VARCHAR2(8) RETENTION VARCHAR2(11) BIGFILE VARCHAR2(3) SQL></span></pre> Let's look at some information about tablespaces created as locally managed: <pre><span style="color:#6600cc;">TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATIO MIN_EXTLEN ------------------------------ -------------- ----------- ---------- --------- ---------- UNDOTBS1 65536 LOCAL SYSTEM 65536 SYSAUX 65536 LOCAL SYSTEM 65536 </span></pre> Notice that the ALLOCATION_TYPE is listed as SYSTEM, meaning these are created AUTOALLOCATE, with extents starting at 64K and systematically increasing in size. Notice also that the next extent is NULL, and that the minimum extent length is 64 K. Let's now look at tablespaces which were dictionary-managed that were converted with the <a href="http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_spadmn.htm#i997300">DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL</a> procedure: <pre><span style="color:#3333ff;">TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATIO MIN_EXTLEN ------------------------------ -------------- ----------- ---------- --------- ---------- TOOLS 32768 32768 LOCAL USER 32768 USERS 131072 131072 LOCAL USER 131072 </span></pre> Here we see a different picture: the ALLOCATION_TYPE is still set to USER, the INITIAL_EXTENT and NEXT_EXTENT fields are populated, not necessarily with matching values and (although not shown here) the PCT_INCREASE values are retained (a true locally managed tablespace has a PCTINCREASE of NULL). Such omissions can create problems later, as free space in these 'mongrel' tablespaces may not coalesce, even by brute force methods and next extent sizing can grow since the PCTINCREASE is not ignored. The initial and next extents can also provide problems as they may not be multiples of 64K (as are the extent sizes of a locally managed, autoallocate tablespace) or they may not be uniform in size (due to the pctincrease). Remember, too, that any objects in these tablespaces won't be rebuilt with the 'standard' extent sizes provided by true locally managed tablespaces, and as the free space coalescing may not be reliable in a converted tablespace free space fragmentation can be a serious issue. In true locally managed tablespaces free space fragmentation isn't a problem because either the extents are all multiples of 64K or all of the extents are uniformly sized. In either case the freed extents can be reused by any new object which needs them. Not so with a converted tablespace, as extent sizes can vary and can be, well, 'interesting' sizes so that fragmentation can be an issue, especially when the coalesce functionality fails.</br ><br /> So, how best to convert dictionary-managed tablespaces to locally managed? The most reliable method is to create a new tablespace, locally managed, and move the objects from the old dictionary-managed tablespace to the new, locally managed one and then drop the old dictionary-managed tablespace. OF course you can't do this if it's the SYSTEM tablespace; you're then stuck using the DBMS_SPACE_ADMIN package to migrate this tablespace, and all other dictionary-managed tablespace, in place (if that's possible as there are restrictions for using DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL). <em><span style="color:#3333ff;">No dictionary-managed tablespaces can exist in read/write mode in a database where the SYSTEM tablespace is locally managed, so any and all dictionary-managed tablespaces other than SYSTEM need to be migrated BEFORE the SYSTEM tablespace is migrated.</span></em> The only other way to do this is to create a new database with a locally managed SYSTEM tablespace and, using a full export, relocate the objects from the source database.<br /><br /> So hanging with the locals is possible, but taking shortcuts usually isn't the best way to get the job done because you may find yourself in worse shape than if you did nothing at all.<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-8261598810235438202?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-65859756169287469132009-03-11T14:46:00.007Z2009-03-13T16:15:07.929Z2009-03-13T16:15:07.929ZSome of the most interesting questions pass through www.google.com, like this one:<br /><br /> "how to know how much table space is allocated for a user in oracle"<br /><br /> How DO we know how much space a given user account can consume in a tablespace? Two views can produce that report, DBA_TS_QUOTAS and DBA_DATA_FILES. Let's see how that can be done.<br /><br /> DBA_TS_QUOTAS provides information on which tablespaces a given user can use and how much space they can consume: <pre><span style="color:#009900;">SQL> desc dba_ts_quotas Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) USERNAME NOT NULL VARCHAR2(30) BYTES NUMBER MAX_BYTES NUMBER BLOCKS NUMBER MAX_BLOCKS NUMBER DROPPED VARCHAR2(3) (available in 11g) SQL></span></pre> This view lists the tablespaces for which a user has an assigned quota, so not all of the tablespaces in the database will be listed for non-DBA users. MAX_BYTES reports the actual size of the granted quota and if that quota is UNLIMITED the value of MAX_BYTES is -1. Likewise for MAX_BLOCKS, which translates the MAX_BYTES column into blocks based upon the db_block_size parameter set at database creation.<br /><br /> Onward and upward to DBA_DATA_FILES: <pre><span style="color:#009900;">SQL> desc dba_data_files Name Null? Type ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2(7) SQL></span></pre> The columns of interest in this view are TABLESPACE_NAME, BYTES and BLOCKS since we'll sum the last two columns, by tablespace, to obtain the total configured space available.<br /><br /> Now it's time to put this all together and return our report. We'll use subquery factoring to assist in this query (available in 9i and later releases of Oracle): <pre><span style="color:#6600CC;">SQL> with ttlbytes as ( 2 select tablespace_name, sum(bytes) ttl_bytes, sum(blocks) ttl_blocks 3 from dba_data_files 4 group by tablespace_name 5 ), 6 userquotas as( 7 select 8 TABLESPACE_NAME, 9 USERNAME, 10 BYTES, 11 max_bytes, 12 blocks, 13 MAX_BLOCKS 14 from dba_ts_quotas 15 where username = upper('&&1') 16 ) 17 select 18 s.tablespace_name, 19 nvl(q.username, upper('&&1')) username, 20 nvl(q.bytes,0) bytes, 21 case when q.MAX_BYTES = -1 then s.ttl_bytes else nvl(q.max_bytes,0) end max_bytes, 22 nvl(q.BLOCKS,0) blocks, 23 case when q.MAX_BLOCKS = -1 then s.ttl_blocks else nvl(q.max_blocks,0) end max_blocks 24 from userquotas q full outer join ttlbytes s 25 on (q.tablespace_name = s.tablespace_name) 26 order by q.username, s.tablespace_name; old 15: where username = upper('&&1') new 15: where username = upper('bing') old 19: nvl(q.username, upper('&&1')) username, new 19: nvl(q.username, upper('bing')) username, TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS ------------------------------ ---------- ---------- ----------- ---------- ----------- TOOLS BING 0 10485760 0 1280 USERS BING 17235968 2899247104 2104 353912 EXAMPLE BING 0 0 0 0 STATDATA BING 0 0 0 0 SYSAUX BING 0 0 0 0 SYSTEM BING 0 0 0 0 UNDOTBS1 BING 0 0 0 0 7 rows selected. SQL></span></pre> Notice the report returns all of the tablespaces in the database, with user information for the tablespaces the given user can utilize.<br /><br /> Can this be done for a database release earlier than 9i? You bet (but, really, you should upgrade to a supported release of Oracle): <pre><span style="color:#6600CC;">SQL> select 2 s.tablespace_name, 3 nvl(q.username, upper('&&1')) username, 4 nvl(q.bytes,0) bytes, 5 decode(q.MAX_BYTES, -1, s.ttl_bytes, nvl(q.max_bytes,0)) max_bytes, 6 nvl(q.BLOCKS,0) blocks, 7 decode(q.MAX_BLOCKS, -1, s.ttl_blocks, nvl(q.max_blocks,0)) max_blocks 8 from ( 9 select 10 TABLESPACE_NAME, 11 USERNAME, 12 BYTES, 13 max_bytes, 14 blocks, 15 MAX_BLOCKS 16 from dba_ts_quotas 17 where username = upper('&&1') 18 ) q, ( 19 select tablespace_name, sum(bytes) ttl_bytes, sum(blocks) ttl_blocks 20 from dba_data_files 21 group by tablespace_name 22 ) s 23 where q.tablespace_name (+)= s.tablespace_name 24 order by q.username, s.tablespace_name; old 3: nvl(q.username, upper('&&1')) username, new 3: nvl(q.username, upper('bing')) username, old 17: where username = upper('&&1') new 17: where username = upper('bing') TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS ------------------------------ ------------- ---------- ----------- ---------- ----------- TOOLS BING 0 10485760 0 1280 USERS BING 17235968 2899247104 2104 353912 EXAMPLE BING 0 0 0 0 STATDATA BING 0 0 0 0 SYSAUX BING 0 0 0 0 SYSTEM BING 0 0 0 0 UNDOTBS1 BING 0 0 0 0 7 rows selected. SQL></span></pre> So answering the question listed at the start of this post is fairly easy and straightforward. And you get even more information than originally requested since you also see the tablespaces for which a given user has no quota assigned. Pretty slick if you ask me.<br /><br /> This report query can be modified to also flag when a user is close to reaching the limit on space in a tablespace. The 9i and later version becomes: <pre><span style="color:#6600CC;">SQL> with ttlbytes as ( 2 select tablespace_name, sum(bytes) ttl_bytes, sum(blocks) ttl_blocks 3 from dba_data_files 4 group by tablespace_name 5 ), 6 userquotas as( 7 select 8 TABLESPACE_NAME, 9 USERNAME, 10 BYTES, 11 max_bytes, 12 blocks, 13 MAX_BLOCKS 14 from dba_ts_quotas 15 where username = upper('&&1') 16 ) 17 select 18 s.tablespace_name, 19 nvl(q.username, upper('&&1')) username, 20 nvl(q.bytes,0) bytes, 21 case when q.MAX_BYTES = -1 then s.ttl_bytes else nvl(q.max_bytes,0) end max_bytes, 22 nvl(q.BLOCKS,0) blocks, 23 case when q.MAX_BLOCKS = -1 then s.ttl_blocks else nvl(q.max_blocks,0) end max_blocks, 24 case when abs(q.bytes - q.max_bytes) < 10240000 then 'WARNING' 25 when abs(q.bytes - q.max_bytes) < 1024000 then 'ALERT' 26 else 'OK' end status 27 from userquotas q full outer join ttlbytes s 28 on (q.tablespace_name = s.tablespace_name) 29 order by q.username, s.tablespace_name; old 15: where username = upper('&&1') new 15: where username = upper('bing') old 19: nvl(q.username, upper('&&1')) username, new 19: nvl(q.username, upper('bing')) username, TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS STATUS ------------------------------ --------- ---------- ----------- ---------- ----------- ------- TOOLS BING 0 10485760 0 1280 OK USERS BING 17235968 2899247104 2104 353912 OK EXAMPLE BING 0 0 0 0 OK STATDATA BING 0 0 0 0 OK SYSAUX BING 0 0 0 0 OK SYSTEM BING 0 0 0 0 OK UNDOTBS1 BING 0 0 0 0 OK 7 rows selected. SQL></span></pre> And for releases earlier than 9i: <pre><span style="color:#6600CC;">SQL> select 2 s.tablespace_name, 3 nvl(q.username, upper('&&1')) username, 4 nvl(q.bytes,0) bytes, 5 decode(q.MAX_BYTES, -1, s.ttl_bytes, nvl(q.max_bytes,0)) max_bytes, 6 nvl(q.BLOCKS,0) blocks, 7 decode(q.MAX_BLOCKS, -1, s.ttl_blocks, nvl(q.max_blocks,0)) max_blocks, 8 decode(sign(decode(decode(q.max_bytes, -1, s.ttl_bytes, q.max_bytes) - q.bytes, 0, -1, 1)), -1,'ALARM', 'OK') status 9 from ( 10 select 11 TABLESPACE_NAME, 12 USERNAME, 13 BYTES, 14 max_bytes, 15 blocks, 16 MAX_BLOCKS 17 from dba_ts_quotas 18 where username = upper('&&1') 19 ) q, ( 20 select tablespace_name, sum(bytes) ttl_bytes, sum(blocks) ttl_blocks 21 from dba_data_files 22 group by tablespace_name 23 ) s 24 where q.tablespace_name (+)= s.tablespace_name 25 order by q.username, s.tablespace_name; old 3: nvl(q.username, upper('&&1')) username, new 3: nvl(q.username, upper('bing')) username, old 18: where username = upper('&&1') new 18: where username = upper('bing') TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS STATU ------------------------------ ----------- ---------- ----------- ---------- ----------- ----- TOOLS BING 0 10485760 0 1280 OK USERS BING 17235968 2899247104 2104 353912 OK EXAMPLE BING 0 0 0 0 OK STATDATA BING 0 0 0 0 OK SYSAUX BING 0 0 0 0 OK SYSTEM BING 0 0 0 0 OK UNDOTBS1 BING 0 0 0 0 OK 7 rows selected. SQL></span></pre> Without having CASE the indicator is rudimentary, stating that either there IS space to use or there ISN'T space available. But, it works as well as it can given the limitations of the 8.1.7 and earlier SQL engines.<br /><br /> How much 'wood' can a 'woodchuck' chuck? That depends upon how much 'wood' is left to chuck. And you'll know exactly in releases 9.0.1 and later. And, well, approximately in releases 8.1.7 and earlier (talk about ancient history...), which is, of course, better than not knowing at all.<br /><br /> So if you're still on 8.1.7.4 <em>seriously</em> consider upgrading to 10.2.0 or 11.1. [If you're still on 7.3 or 8.0, maybe you should open a museum.] Because you'd really rather know exactly how much space your users have left.<br /><br /> You would.<br /><br /> Really.<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-6585975616928746913?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-54194992642509087842009-03-05T14:21:00.003Z2009-03-05T15:20:17.455Z2009-03-05T15:20:17.455ZFor years developers have been warned to not rely upon default date formats when writing application code, which is sound advice indeed. However, maybe that should be expanded to include not relying upon what one might expect when passing partial date strings to TO_DATE since the results might not match the desired outcome. Let's look at some examples and see where Oracle may not do what you think it should.<br /><br /> I think by now most people know that if the time is not supplied to TO_DATE it defaults to midnight: <pre><span style="color:#3333ff;">SQL> SQL> -- SQL> -- By default Oracle sets the time to midnight SQL> -- if the time is not supplied to the SQL> -- TO_DATE function SQL> -- SQL> select TO_DATE('03012009','DDMMYYYY') from dual; TO_DATE('03012009', ------------------- 01-03-2009 00:00:00 SQL></span></pre> so this should be expected behaviour. Let's look at what happens when the day is not supplied: <pre><span style="color:#3333ff;">SQL> -- SQL> -- Oracle defaults to the first day of the month SQL> -- when the day is not provided to TO_DATE SQL> -- SQL> select TO_DATE('032009','MMYYYY') from dual; TO_DATE('032009','M ------------------- 03-01-2009 00:00:00 SQL></span></pre> So far this is behaviour to be expected. What happens when only the year is supplied? Let's find out: <pre><span style="color:#3333ff;">SQL> -- SQL> -- Oracle defaults to the first day SQL> -- of the current month when neither month nor day SQL> -- is specified SQL> -- SQL> select TO_DATE('1995','YYYY') from dual; TO_DATE('1995','YYY ------------------- 03-01-1995 00:00:00 SQL></span></pre> Hmmm, I would have expected the current month <em>and</em> day to be returned. Now let's pass the day and the year to TO_DATE: <pre><span style="color:#3333ff;">SQL> -- SQL> -- Oracle defaults to the current month SQL> -- when it is not provided SQL> -- SQL> -- We illustrate this by passing the day and the SQL> -- year to TO_DATE SQL> -- SQL> select TO_DATE('092008','DDYYYY') from dual; TO_DATE('092008','D ------------------- 03-09-2008 00:00:00 SQL></span></pre> And that's what I would expect. Passing in the day and the month causes Oracle to use the current year: <pre><span style="color:#3333ff;">SQL> -- SQL> -- The default is to assume the current year if it SQL> -- is not provided SQL> -- SQL> select TO_DATE('0712','MMDD') from dual; TO_DATE('0712','MMD ------------------- 07-12-2009 00:00:00 SQL></span></pre> and that's, again, what I would expect. Now let's pass in only the day: <pre><span style="color:#3333ff;">SQL> -- SQL> -- Assume the current month and year when only SQL> -- the day is provided SQL> -- SQL> select TO_DATE('23','DD') from dual; TO_DATE('23','DD') ------------------- 03-23-2009 00:00:00 SQL></span></pre> Now let's provide only the time: <pre><span style="color:#3333ff;">SQL> -- SQL> -- Strangeness abounds, as when only the time is provided SQL> -- Oracle defaults to the current month but assumes SQL> -- the first day of that month SQL> -- SQL> select to_date('11:00:00','hh24:mi:ss') 2 from dual; TO_DATE('11:00:00', ------------------- 03-01-2009 11:00:00 SQL></span></pre> I would have thought Oracle would presume the current month and day for that example, but what I think and what Oracle does are two different things.<br /><br /> So, Oracle can do the expected, depending upon which part of the date string is supplied. It can also do the unexpected, and that can be unnerving if you're trying to troubleshoot an application and can't understand why the date arithmetic is off: <pre><span style="color:#3333ff;">SQL> -- SQL> -- This doesn't return the expected result SQL> -- because Oracle assumes the first day of the SQL> -- month, not the current day SQL> -- SQL> select sysdate - to_date('07:00:00','hh24:mi:ss') 2 from dual; SYSDATE-TO_DATE('07:00:00','HH24:MI:SS') ---------------------------------------- 4.08762731 SQL></span></pre> Fixing that problem means rewriting the query a bit: <pre><span style="color:#3333ff;">SQL> -- SQL> -- Let's get the result we expected SQL> -- SQL> select sysdate - to_date(to_char(sysdate, 'MM-DD-RRRR')||' 07:00:00','mm-dd-rrrr hh24:mi:ss') 2 from dual; SYSDATE-TO_DATE(TO_CHAR(SYSDATE,'MM-DD-RRRR')||'07:00:00','MM-DD-RRRRHH24:MI:SS' -------------------------------------------------------------------------------- .087627315 SQL></span></pre> If you're not certain what Oracle will return from a function call you should test the code before assuming anything as the examples above attest. We certainly didn't get the second result from the code in the prior example simply because Oracle didn't return the default data as we thought it should.<br /><br /> Expect the unexpected, and nothing should be a surprise. Well, at least not an unpleasant one.<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-5419499264250908784?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-56271662152114561592009-01-28T16:08:00.003Z2009-05-19T21:49:21.683+01:002009-05-19T21:49:21.683+01:00The following question seems simple enough: <pre><span style="color:#3333ff;">Is there a quick way to convert week number (of the year) to start date of that week? EX: If I pass week number as 1 then it should return 1/1/2009 If I pass week number as 7 then it should return 2/8/2009 </span></pre> This, of course, assumes a number of things which may or may not be true, such as the first day of week 1 is January 1 and that week 7 is defined to include, and also begin on, February 8. Such assumptions depend upon which week numbering 'scheme' one elects to use, and there are two common schemes currently in use by Oracle. Let's look at both of them and see what differences they contain and how they can throw the listed assumptions 'out of the window'.<br /><br /> The two week numbering systems in use by Oracle are the U.S. week numbering system and the ISO week numbering system. They ARE different in how they define Week number 1 and that can throw a 'monkey wrench' into any methodology one could implement to answer the above listed question.<br /><br /> If we use the U.S week numbering system we can easily satisfy the first condition listed in the posted question as Week 1 is defined as the week containing January 1: <pre><span style="color:#3333ff;">SQL> with date_wk as ( 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt 3 from dual 4 connect by level <= 366 5 ), 6 wk_dt as ( 7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr 8 from date_wk 9 ) 10 select dt, wk_of_yr 11 from wk_dt 12 where wk_of_yr = &&1 13 / old 12: where wk_of_yr = &&1 new 12: where wk_of_yr = 1 DT WK_OF_YR --------- ---------- 01-JAN-09 1 02-JAN-09 1 03-JAN-09 1 04-JAN-09 1 05-JAN-09 1 06-JAN-09 1 07-JAN-09 1 01-JAN-10 1 8 rows selected. SQL> SQL> with date_wk as ( 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt 3 from dual 4 connect by level <= 366 5 ), 6 wk_dt as ( 7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr 8 from date_wk 9 ) 10 select min(dt) 11 from wk_dt 12 where wk_of_yr = &&1 13 / old 12: where wk_of_yr = &&1 new 12: where wk_of_yr = 1 MIN(DT) --------- 01-JAN-09 SQL></span></pre> <span style="color:#3333ff;"> [In Oracle syntax the format specifier for the U.S. week numbering system is WW, in either upper or lower case. The first subquery shown generates a list of dates starting with January 1 of the current year and ends 365 days later. The second subquery takes that list and generates the U.S. week number for each date. The final query returns results based upon the supplied week number.]<br /><br /> </span> But Week 7 of that numbering convention doesn't contain February 8, 2009: <pre><span style="color:#3333ff;">SQL> with date_wk as ( 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt 3 from dual 4 connect by level <= 366 5 ), 6 wk_dt as ( 7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr 8 from date_wk 9 ) 10 select dt, wk_of_yr 11 from wk_dt 12 where wk_of_yr = &&1 13 / old 12: where wk_of_yr = &&1 new 12: where wk_of_yr = 7 DT WK_OF_YR --------- ---------- 12-FEB-09 7 13-FEB-09 7 14-FEB-09 7 15-FEB-09 7 16-FEB-09 7 17-FEB-09 7 18-FEB-09 7 7 rows selected. SQL> SQL> with date_wk as ( 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt 3 from dual 4 connect by level <= 366 5 ), 6 wk_dt as ( 7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr 8 from date_wk 9 ) 10 select min(dt) 11 from wk_dt 12 where wk_of_yr = &&1 13 / old 12: where wk_of_yr = &&1 new 12: where wk_of_yr = 7 MIN(DT) --------- 12-FEB-09 SQL></span></pre> Week 6 does, although it's not the starting date of that week: <pre><span style="color:#3333ff;">SQL> with date_wk as ( 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt 3 from dual 4 connect by level <= 366 5 ), 6 wk_dt as ( 7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr 8 from date_wk 9 ) 10 select dt, wk_of_yr 11 from wk_dt 12 where wk_of_yr = &&1 13 / old 12: where wk_of_yr = &&1 new 12: where wk_of_yr = 6 DT WK_OF_YR --------- ---------- 05-FEB-09 6 06-FEB-09 6 07-FEB-09 6 08-FEB-09 6 09-FEB-09 6 10-FEB-09 6 11-FEB-09 6 7 rows selected. SQL> SQL> with date_wk as ( 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt 3 from dual 4 connect by level <= 366 5 ), 6 wk_dt as ( 7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr 8 from date_wk 9 ) 10 select min(dt) 11 from wk_dt 12 where wk_of_yr = &&1 13 / old 12: where wk_of_yr = &&1 new 12: where wk_of_yr = 6 MIN(DT) --------- 05-FEB-09 SQL></span></pre> Now, if the ISO week numbering convention is used the first condition of the question won't be satisfied as Week 1 is defined to contain the first Thursday of the calendar year, thus the starting date for ISO Week 1 can be in December, and for 2009 it is: <pre><span style="color:#3333ff;">SQL> with date_wk as ( 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt 3 from dual 4 connect by level <= 366 5 ), 6 wk_dt as ( 7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr 8 from date_wk 9 ) 10 select dt, wk_of_yr 11 from wk_dt 12 where wk_of_yr = &&1 13 / old 12: where wk_of_yr = &&1 new 12: where wk_of_yr = 1 DT WK_OF_YR --------- ---------- 29-DEC-08 1 30-DEC-08 1 31-DEC-08 1 01-JAN-09 1 02-JAN-09 1 03-JAN-09 1 04-JAN-09 1 7 rows selected. SQL> SQL> with date_wk as ( 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt 3 from dual 4 connect by level <= 366 5 ), 6 wk_dt as ( 7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr 8 from date_wk 9 ) 10 select min(dt) 11 from wk_dt 12 where wk_of_yr = &&1 13 / old 12: where wk_of_yr = &&1 new 12: where wk_of_yr = 1 MIN(DT) --------- 29-DEC-08 SQL></span></pre> <span style="color:#3333ff;"> [The Oracle format specifier for the ISO week numbering system is IW, in either upper or lower case. The change to the format specifier is the only change made to the query posted at the beginning.]<br /><br /> </span> ISO Week 7 doesn't answer the second condition, either, since February 8, 2009 is the last day of ISO Week 6: <pre><span style="color:#3333ff;">SQL> with date_wk as ( 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt 3 from dual 4 connect by level <= 366 5 ), 6 wk_dt as ( 7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr 8 from date_wk 9 ) 10 select dt, wk_of_yr 11 from wk_dt 12 where wk_of_yr = &&1 13 / old 12: where wk_of_yr = &&1 new 12: where wk_of_yr = 7 DT WK_OF_YR --------- ---------- 09-FEB-09 7 10-FEB-09 7 11-FEB-09 7 12-FEB-09 7 13-FEB-09 7 14-FEB-09 7 15-FEB-09 7 7 rows selected. SQL> SQL> with date_wk as ( 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt 3 from dual 4 connect by level <= 366 5 ), 6 wk_dt as ( 7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr 8 from date_wk 9 ) 10 select min(dt) 11 from wk_dt 12 where wk_of_yr = &&1 13 / old 12: where wk_of_yr = &&1 new 12: where wk_of_yr = 7 MIN(DT) --------- 09-FEB-09 SQL></span></pre> How, then, is the ISO week defined? It starts on Monday and ends on Sunday, and ISO Week 1 is defined in the following equivalent terms:<br /><br /> the week with the year's first Thursday in it (the ISO 8601 definition) <br /> the week starting with the Monday which is nearest in time to 1 January <br /> the week with the year's first working day in it (if Saturdays, Sundays, and 1 January are not working days) <br /> the week with January 4 in it <br /> the first week with the majority (four or more) of its days in the starting year <br /> the week starting with the Monday in the period 29 December - 4 January <br /> the week with the Thursday in the period 1 - 7 January <br /> If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year. <br /><br /> Given the above definition there are some years where even the first condition of the original question won't be satisfied, like 2010, where the first day of ISO Week 1 is January 4: <pre><span style="color:#3333ff;">SQL> with date_wk as ( 2 select to_date('01/01/2010', 'MM/DD/RRRR') + rownum - 1 dt 3 from dual 4 connect by level <= 366 5 ), 6 wk_dt as ( 7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr 8 from date_wk 9 ) 10 select dt, wk_of_yr 11 from wk_dt 12 where wk_of_yr = &&1 13 / old 12: where wk_of_yr = &&1 new 12: where wk_of_yr = 1 DT WK_OF_YR --------- ---------- 04-JAN-10 1 05-JAN-10 1 06-JAN-10 1 07-JAN-10 1 08-JAN-10 1 09-JAN-10 1 10-JAN-10 1 7 rows selected. SQL> SQL> with date_wk as ( 2 select to_date('01/01/2010', 'MM/DD/RRRR') + rownum - 1 dt 3 from dual 4 connect by level <= 366 5 ), 6 wk_dt as ( 7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr 8 from date_wk 9 ) 10 select min(dt) 11 from wk_dt 12 where wk_of_yr = &&1 13 / old 12: where wk_of_yr = &&1 new 12: where wk_of_yr = 1 MIN(DT) --------- 04-JAN-10 SQL></span></pre> The U.S. week is defined as starting on Sunday and ending on Saturday. Week number 1 in this convention is defined as the week beginning on January 1, which may be a partial week. As such the last week of the year in this convention can also be a partial week. The full definition of U.S. Week Number 1 is:<br /><br /> The first week of the year contains 1 January, the 1st Saturday and is comprised of days 1-7 of the year.<br /><br /> So, the question, as posed, relies upon a numbering system which allows partial weeks, the weeks always start on Sunday, always end on Saturday and declare Week Number 1 as that week starting with Jauary 1. In such a system February 8, 2009, would be the starting date for Week 7 (because Week 1 only has three days, January 1,2 and 3). I don't know of a numbering scheme which meets that criteria. But, there MIGHT be one in use somewhere which satisfies all of those conditions. Stranger things have happened.<br /><br /> And that was the week that wasn't.<br /><br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-5627166215211456159?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-69088131839143637392008-12-17T21:33:00.007Z2008-12-24T20:13:44.625Z2008-12-24T20:13:44.625ZThis seems to be a typical series of events when things aren't going quite as nicely as originally planned:<br /><br /> "The time comes when someone is, well, unhappy with the performace of the database. For troubleshooting such complaints what tools are available? The most common, and least intrusive, is Statspack and it's fairly easy to install; simply execute the spcreate.sql script, found in $ORACLE_HOME/rdbms/admin, as SYS and, presuming the installation is successful you're ready to go. You'll need to set a password and both the default and temporary tablespaces for this user (PERFSTAT), but you're prompted for that information by the script (the log file is create after the password has been submitted, so that display is not included here): <pre><span style="color:#3333ff;"> Choose the Default tablespace for the PERFSTAT user --------------------------------------------------- Below is the list of online tablespaces in this database which can store user data. Specifying the SYSTEM tablespace for the user's default tablespace will result in the installation FAILING, as using SYSTEM for performance data is not supported. Choose the PERFSTAT users's default tablespace. This is the tablespace in which the STATSPACK tables and indexes will be created. TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE ------------------------------ --------- ---------------------------- EXAMPLE PERMANENT STATDATA PERMANENT SYSAUX PERMANENT * TOOLS PERMANENT USERS PERMANENT Pressing <return> will result in STATSPACK's recommended default tablespace (identified by *) being used. Enter value for default_tablespace: tools Using tablespace TOOLS as PERFSTAT default tablespace. Choose the Temporary tablespace for the PERFSTAT user ----------------------------------------------------- Below is the list of online tablespaces in this database which can store temporary data (e.g. for sort workareas). Specifying the SYSTEM tablespace for the user's temporary tablespace will result in the installation FAILING, as using SYSTEM for workareas is not supported. Choose the PERFSTAT user's Temporary tablespace. TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE ------------------------------ --------- -------------------------- TEMP TEMPORARY * Pressing <return> will result in the database's default Temporary tablespace (identified by *) being used. Enter value for temporary_tablespace: Using tablespace TEMP as PERFSTAT temporary tablespace. ... Creating PERFSTAT user ... Installing required packages ... Creating views ... Granting privileges NOTE: SPCUSR complete. Please check spcusr.lis for any errors. </span></pre> Notice that the TOOLS tablespace was chosen for PERFSTAT to use; any tablespace CAN be used for the PERFSTAT user, but I prefer to keep its objects out of the SYSAUX tablespace (the default set by the script). (But, you don't need to use the TOOLS tablespace; more on that in a bit.)<br /><br /> Tables, sequences, synonyms and packages are created to enable you to gather statistics, instance-wide, with a simple command: <pre><span style="color:#3333ff;">SQL> exec statspack.snap PL/SQL procedure successfully completed. SQL></span></pre> Two or more snapshots are required to generate a report, and it's recommended that a 15-minute interval elapse between snaps. Once you have sufficient snapshots generating a report is easier than falling off of a log (so to speak): <pre><span style="color:#3333ff;">SQL> @?/rdbms/admin/spreport Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 1198289520 ORCL 1 orcl Instances in this Statspack schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ----------- -------- ------------ ------------ ------------ 1198289520 1 ORCL orcl BVL-44B85C84 4D Using 1198289520 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots. Listing all Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level Comment ------------ ------------ --------- ----------------- ----- -------------------- orcl ORCL 1 17 Dec 2008 14:10 5 2 17 Dec 2008 14:24 5 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap:</span></pre> Select the snaps to compare, choose a file name if you don't want the default, and, voila! You have your report."<br /><br /> So you've installed Statspack and used it quite a bit, and all of these statistics are stored somewhere (the PERFSTAT schema resides in the default tablespace you assigned to the PERFSTAT user, and in this case it's TOOLS). But, what if you want to use a <em>different</em> tablespace for the PERFSTAT tables? What if you selected a tablespace you <em>thought</em> was unused by others but discover later the error of your ways? Or, gee whiz, what if you think a change of scenery for the PERFSTAT tables/indexes might make them happier and perform better? The task is fairly simple, really; provide the PERFSTAT user access to the new tablespace you want to use [I usually create a tablespace solely for PERFSTAT to use (STATDATA) so granting an unlimited quota on it doesn't create problems later], then move the tables and indexes to that new tablespace: <pre><span style="color:#3333ff;">SQL> connect / as sysdba Connected. SQL> alter user perfstat quota unlimited on statdata; User altered. SQL> connect perfstat Password: Connected. SQL></span></pre> Then generate scripts to move the tables and rebuild the indexes and execute them as PERFSTAT. A sample index rebuild script is shown: <pre><span style="color:#3333ff;"> alter index STATS$IDLE_EVENT_PK rebuild tablespace statdata; alter index STATS$INTERCONNECT_PINGS_PK rebuild tablespace statdata; alter index STATS$MEMORY_RESIZE_OPS_PK rebuild tablespace statdata; alter index STATS$MEMORY_DYNAMIC_COMPS_PK rebuild tablespace statdata; alter index STATS$MEMORY_TARGET_ADVICE_PK rebuild tablespace statdata; alter index STATS$IOSTAT_FUNCTION_PK rebuild tablespace statdata; alter index STATS$IOSTAT_FUNCTION_NAME_PK rebuild tablespace statdata; alter index STATS$DYNAMIC_REM_STATS_PK rebuild tablespace statdata; alter index STATS$MUTEX_SLEEP_PK rebuild tablespace statdata; alter index STATS$STREAMS_POOL_ADVICE_PK rebuild tablespace statdata; alter index STATS$SGA_TARGET_ADVICE_PK rebuild tablespace statdata; alter index STATS$PROCESS_MEMORY_ROLLUP_PK rebuild tablespace statdata; alter index STATS$$PROCESS_ROLLUP_PK rebuild tablespace statdata; alter index STATS$OSSTAT_PK rebuild tablespace statdata; alter index STATS$OSSSTATNAME_PK rebuild tablespace statdata; alter index STATS$RULE_SET_PK rebuild tablespace statdata; alter index STATS$BUFFERED_SUBSCRIBERS_PK rebuild tablespace statdata; alter index STATS$BUFFERED_QUEUES_PK rebuild tablespace statdata; alter index STATS$PROPAGATION_RECEIVER_PK rebuild tablespace statdata; alter index STATS$PROPAGATION_SENDER_PK rebuild tablespace statdata; alter index STATS$STREAMS_APPLY_SUM_PK rebuild tablespace statdata; alter index STATS$STREAMS_CAPTURE_PK rebuild tablespace statdata; alter index STATS$SESS_TIME_MODEL_PK rebuild tablespace statdata; alter index STATS$SYS_TIME_MODEL_PK rebuild tablespace statdata; alter index STATS$TIME_MODEL_STATNAME_PK rebuild tablespace statdata; alter index STATS$EVENT_HISTOGRAM_PK rebuild tablespace statdata; alter index STATS$FILE_HISTOGRAM_PK rebuild tablespace statdata; alter index STATS$THREAD_PK rebuild tablespace statdata; alter index STATS$JAVA_POOL_ADVICE_PK rebuild tablespace statdata; alter index STATS$PGA_TARGET_ADVICE_PK rebuild tablespace statdata; alter index STATS$SQL_WORKAREA_HIST_PK rebuild tablespace statdata; alter index STATS$SHARED_POOL_ADVICE_PK rebuild tablespace statdata; alter index STATS$STATSPACK_PARAMETER_PK rebuild tablespace statdata; alter index STATS$INSTANCE_RECOVERY_PK rebuild tablespace statdata; alter index STATS$PARAMETER_PK rebuild tablespace statdata; alter index STATS$SQL_PGASTAT_PK rebuild tablespace statdata; alter index STATS$SEG_STAT_OBJ_PK rebuild tablespace statdata; alter index STATS$SEG_STAT_PK rebuild tablespace statdata; alter index STATS$SQL_PLAN_PK rebuild tablespace statdata; alter index STATS$SQL_PLAN_USAGE_PK rebuild tablespace statdata; alter index STATS$SQL_PLAN_USAGE_HV rebuild tablespace statdata; alter index STATS$UNDOSTAT_PK rebuild tablespace statdata; alter index STATS$INST_CACHE_TRANSFER_PK rebuild tablespace statdata; alter index STATS$CURRENT_BLOCK_SERVER_PK rebuild tablespace statdata; alter index STATS$CR_BLOCK_SERVER_PK rebuild tablespace statdata; alter index STATS$DLM_MISC_PK rebuild tablespace statdata; alter index STATS$RESOURCE_LIMIT_PK rebuild tablespace statdata; alter index STATS$SQL_STATISTICS_PK rebuild tablespace statdata; alter index STATS$SQLTEXT_PK rebuild tablespace statdata; alter index STATS$SQL_SUMMARY_PK rebuild tablespace statdata; alter index STATS$ENQUEUE_STATISTICS_PK rebuild tablespace statdata; alter index STATS$WAITSTAT_PK rebuild tablespace statdata; alter index STATS$SESSION_EVENT_PK rebuild tablespace statdata; alter index STATS$SYSTEM_EVENT_PK rebuild tablespace statdata; alter index STATS$SESSTAT_PK rebuild tablespace statdata; alter index STATS$SYSSTAT_PK rebuild tablespace statdata; alter index STATS$SGASTAT_U rebuild tablespace statdata; alter index STATS$SGA_PK rebuild tablespace statdata; alter index STATS$ROWCACHE_SUMMARY_PK rebuild tablespace statdata; alter index STATS$ROLLSTAT_PK rebuild tablespace statdata; alter index STATS$BUFFER_POOL_STATS_PK rebuild tablespace statdata; alter index STATS$LIBRARYCACHE_PK rebuild tablespace statdata; alter index STATS$LATCH_MISSES_SUMMARY_PK rebuild tablespace statdata; alter index STATS$LATCH_PARENT_PK rebuild tablespace statdata; alter index STATS$LATCH_CHILDREN_PK rebuild tablespace statdata; alter index STATS$LATCH_PK rebuild tablespace statdata; alter index STATS$TEMPSTATXS_PK rebuild tablespace statdata; alter index STATS$FILESTATXS_PK rebuild tablespace statdata; alter index STATS$DB_CACHE_ADVICE_PK rebuild tablespace statdata; alter index STATS$SNAPSHOT_PK rebuild tablespace statdata; alter index STATS$LEVEL_DESCRIPTION_PK rebuild tablespace statdata; alter index STATS$DATABASE_INSTANCE_PK rebuild tablespace statdata; </span></pre> Generating such scripts is also a fairly simple task -- let SQL write your SQL for you [remember to connect as PERFSTAT before you run such scripts]: <pre><span style="color:#3333ff;">select 'alter table '||table_name||' move tablespace &lt;tablespacename&gt;;' from user_tables where tablespace_name is not null; </span></pre> and <pre><span style="color:#3333ff;">select 'alter index '||index_name||' rebuild tablespace &lt;tablespacename&gt;;' from user_indexes; </span></pre> Spool the output from each to files and run the resulting scripts; make certain you have enough space in the destination tablespace for the objects else you won't move all of your tables/indexes and you'll need to generate new scripts to finish the tasks. Presuming all goes well the tables and indexes will be relocated to the desired tablespace and Statspack will remain in working order.<br /><br /> So, it isn't really a problem to move the Statspack tables to another location, as long as you're careful and plan ahead.<br /><br /> And this move doesn't require a U-Haul.<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-6908813183914363739?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-5158145181858930672008-12-10T19:55:00.004Z2008-12-15T15:50:25.642Z2008-12-15T15:50:25.642ZA question was posed to google.com recently which stated:<br /><br /> "can we have a row named final in oracle table"<br /><br /> To answer that (presuming it was column, and not row, intended in the question) one must make a trip to V$RESERVED_WORDS: <pre><span style="color:#3333ff;">SQL> desc v$reserved_words Name Null? Type ----------------------------------------- -------- ---------------------------- KEYWORD VARCHAR2(30) LENGTH NUMBER RESERVED VARCHAR2(1) RES_TYPE VARCHAR2(1) RES_ATTR VARCHAR2(1) RES_SEMI VARCHAR2(1) DUPLICATE VARCHAR2(1) SQL></span></pre> The two columns of interest are KEYWORD and RESERVED. Let's look for 'FINAL' in the view and see whether or not it's reserved: <pre><span style="color:#3333ff;">SQL> select keyword, reserved 2 from v$reserved_words 3 where keyword like 'F%' 4 order by 1; KEYWORD R ------------------------------ - FACT N FAILED N FAILED_LOGIN_ATTEMPTS N FAILGROUP N FALSE N FAST N FBTSCAN N FIC_CIV N FIC_PIV N FILE N FILTER N KEYWORD R ------------------------------ - <span style="color:#990000;">FINAL N</span> FINE N FINISH N FIRST N FIRST_ROWS N FLAGGER N FLASHBACK N FLOAT Y FLOB N FLUSH N FOLLOWING N KEYWORD R ------------------------------ - FOR Y FORCE N FORCE_XML_QUERY_REWRITE N FOREIGN N FREELIST N FREELISTS N FREEPOOLS N FRESH N FROM Y FULL N FUNCTION N KEYWORD R ------------------------------ - FUNCTIONS N 34 rows selected. SQL></span></pre> It's in the list of reserved words, but it's not actually reserved, meaning you CAN use it if you REALLY REALLY want to, but it's recommended that you don't. I wouldn't use it. But, that's the way I think.<br /><br /> What other words shouldn't you use for column names/table names/view names/constraint names/sequence names/type names/...? Simply look at the list provided in, yes, V$RESERVED_WORDS. There are 1142 of them in 10gR2, 1733 in 11gR1; they're listed for good reason, in my opinion, and the list should be heeded. And, yes, many of the words are not actually reserved (as indicated by the N in RESERVED) but it's still a really good idea to <em><span style="color:#990000;">not</span></em> use them as, some day, Oracle may decide to actually reserve them and then your scripts won't run: <pre><span style="color:#3333ff;">SQL> create table from( 2 final varchar2(400) 3 ); create table from( * ERROR at line 1: ORA-00903: invalid table name SQL></span></pre> And that would be a shame.<br /><br /> Reservations, anyone?<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-515814518185893067?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com2tag:blogger.com,1999:blog-28935478.post-35121147192421105162008-11-19T15:32:00.012Z2008-12-03T19:00:38.154Z2008-12-03T19:00:38.154ZIn a forum I frequent the following question was presented (I have reposted the question in its entirety here):<br /><br /> <span style="color:#3333ff;">Suppose I have a table structure like T1(C1,C2,C3,C4....). <br /> I creates a index indx1 on (C1,C2,C3). I issue three select <br /> statements like : <br /><br /> 1.select * from T1 where C1=&lt;&gt; <br /> 2.select * from T1 where C2=&lt;&gt; <br /> 3.select * from T1 where C3=&lt;&gt; <br /> 4.select * from T1 where C1= &lt;&gt; and C2= &lt;&gt; and C3 =&lt;&gt; <br /><br /><br /> How the optimizer will use the index for each statements. <br /><br /><br /> Regards, <br /> Sanjoy <br /><br /></span> Of course the only way to begin to answer that, since the original poster did not provide any DDL or sample data, is to create an example and execute it (we'll ignore the fact that his <span style="color:#990000;"><em>three</em></span> questions are numbered 1 through 4). I've posted the results below, this running on an Oracle 11.1.0.6 instance; the statistics reported by autotrace have been removed as they provided no additional value to the example. Notice how the plans change as the data 'topography' changes, illustrating that the original question was ambiguous, at best: <pre><span style="color:#3333ff;">SQL> SQL> -- SQL> -- Suppose I have a table structure like T1(C1,C2,C3,C4....). SQL> -- I creates a index indx1 on (C1,C2,C3). I issue three select SQL> -- statements like : SQL> -- SQL> -- 1.select * from T1 where C1=<> SQL> -- 2.select * from T1 where C2=<> SQL> -- 3.select * from T1 where C3=<> SQL> -- 4.select * from T1 where C1= <> and C2= <> and C3 =<> SQL> -- SQL> -- SQL> -- How the optimizer will use the index for each statements. SQL> -- SQL> -- SQL> -- Regards, SQL> -- Sanjoy SQL> -- SQL> SQL> -- SQL> -- Create the table in question SQL> -- SQL> create table t1( 2 c1 number, 3 c2 varchar2(20), 4 c3 date, 5 c4 varchar2(10), 6 c5 number, 7 c6 number 8 ); Table created. SQL> SQL> -- SQL> -- Create the index specified SQL> -- SQL> create index indx1 2 on t1(c1,c2,c3); Index created. SQL> SQL> -- SQL> -- Load test data SQL> -- SQL> SQL> -- SQL> -- Data with unique C1, C2 and C3 values SQL> -- SQL> begin 2 for i in 1..10101 loop 3 insert into t1 4 values (i, 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963)); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> SQL> commit; Commit complete. SQL> SQL> -- SQL> -- 'Standard' statistics SQL> -- SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING'); PL/SQL procedure successfully completed. SQL> SQL> set autotrace on linesize 132 SQL> SQL> select * From t1 where c1 =433; C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 433 Testing record 433 26-JAN-10 Filler 433 433 Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 3 (0)| 00:00:01 | <span style="color:#990000;">|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |</span> ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=433) SQL> SQL> select * From t1 where c2 = 'Testing record 7748'; C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 7748 Testing record 7748 05-FEB-30 Filler 408 44 Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 22 (0)| 00:00:01 | <span style="color:#990000;">|* 1 | TABLE ACCESS FULL| T1 | 1 | 46 | 22 (0)| 00:00:01 |</span> -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='Testing record 7748') SQL> SQL> select * from t1 where c3 = trunc(sysdate+9); C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 9 Testing record 9 28-NOV-08 Filler 9 9 Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 22 (0)| 00:00:01 | <span style="color:#990000;">|* 1 | TABLE ACCESS FULL| T1 | 1 | 46 | 22 (0)| 00:00:01 |</span> -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C3"=TRUNC(SYSDATE@!+9)) SQL> SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993); C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 9993 Testing record 9993 30-MAR-36 Filler 451 363 Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 2 (0)| 00:00:01 | <span style="color:#990000;">|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |</span> ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=9993 AND "C2"='Testing record 9993' AND "C3"=TRUNC(SYSDATE@!+9993)) SQL> SQL> set autotrace off SQL> SQL> -- SQL> -- Statistics with auto-sized histograms on indexed columns SQL> -- SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'); PL/SQL procedure successfully completed. SQL> SQL> set autotrace on linesize 132 SQL> SQL> select * From t1 where c1 =433; C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 433 Testing record 433 26-JAN-10 Filler 433 433 Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=433) SQL> SQL> select * From t1 where c2 = 'Testing record 7748'; C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 7748 Testing record 7748 05-FEB-30 Filler 408 44 Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 22 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 46 | 22 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='Testing record 7748') SQL> SQL> select * from t1 where c3 = trunc(sysdate+9); C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 9 Testing record 9 28-NOV-08 Filler 9 9 Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 22 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 46 | 22 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C3"=TRUNC(SYSDATE@!+9)) SQL> SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993); C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 9993 Testing record 9993 30-MAR-36 Filler 451 363 Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=9993 AND "C2"='Testing record 9993' AND "C3"=TRUNC(SYSDATE@!+9993)) SQL> SQL> set autotrace off SQL> SQL> truncate table t1; Table truncated. SQL> SQL> -- SQL> -- Data with unique C2 and C3 values, and a reasonably selective C1 SQL> -- SQL> begin 2 for i in 1..10101 loop 3 insert into t1 4 values (mod(i, 43), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963)); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> SQL> commit; Commit complete. SQL> SQL> -- SQL> -- 'Standard' statistics SQL> -- SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING'); PL/SQL procedure successfully completed. SQL> SQL> set autotrace on linesize 132 SQL> SQL> select * From t1 where c1 =433; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=433) SQL> SQL> select * From t1 where c2 = 'Testing record 7748'; C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 8 Testing record 7748 05-FEB-30 Filler 408 44 Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='Testing record 7748') SQL> SQL> select * from t1 where c3 = trunc(sysdate+9); C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 9 Testing record 9 28-NOV-08 Filler 9 9 Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C3"=TRUNC(SYSDATE@!+9)) SQL> SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993); no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=9993 AND "C2"='Testing record 9993' AND "C3"=TRUNC(SYSDATE@!+9993)) SQL> SQL> set autotrace off SQL> SQL> -- SQL> -- Statistics with auto-sized histograms on indexed columns SQL> -- SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'); PL/SQL procedure successfully completed. SQL> SQL> set autotrace on linesize 132 SQL> SQL> select * From t1 where c1 =433; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=433) SQL> SQL> select * From t1 where c2 = 'Testing record 7748'; C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 8 Testing record 7748 05-FEB-30 Filler 408 44 Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='Testing record 7748') SQL> SQL> select * from t1 where c3 = trunc(sysdate+9); C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 9 Testing record 9 28-NOV-08 Filler 9 9 Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C3"=TRUNC(SYSDATE@!+9)) SQL> SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993); no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=9993 AND "C2"='Testing record 9993' AND "C3"=TRUNC(SYSDATE@!+9993)) SQL> SQL> set autotrace off SQL> SQL> truncate table t1; Table truncated. SQL> SQL> -- SQL> -- Data with unique C2 and C3 values, with a cycling C1 set of values SQL> -- SQL> begin 2 for i in 1..10101 loop 3 insert into t1 4 values (mod(i, 3), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963)); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> SQL> commit; Commit complete. SQL> SQL> -- SQL> -- 'Standard' statistics SQL> -- SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING'); PL/SQL procedure successfully completed. SQL> SQL> set autotrace on linesize 132 SQL> SQL> select * From t1 where c1 =433; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=433) SQL> SQL> select * From t1 where c2 = 'Testing record 7748'; C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 2 Testing record 7748 05-FEB-30 Filler 408 44 Execution Plan ---------------------------------------------------------- Plan hash value: 3325179317 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 5 (0)| 00:00:01 | <span style="color:#990000;">|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |</span> ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C2"='Testing record 7748') filter("C2"='Testing record 7748') SQL> SQL> select * from t1 where c3 = trunc(sysdate+9); C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 0 Testing record 9 28-NOV-08 Filler 9 9 Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C3"=TRUNC(SYSDATE@!+9)) SQL> SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993); no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=9993 AND "C2"='Testing record 9993' AND "C3"=TRUNC(SYSDATE@!+9993)) SQL> SQL> set autotrace off SQL> SQL> -- SQL> -- Statistics with auto-sized histograms on indexed columns SQL> -- SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'); PL/SQL procedure successfully completed. SQL> SQL> set autotrace on linesize 132 SQL> SQL> select * From t1 where c1 =433; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=433) SQL> SQL> select * From t1 where c2 = 'Testing record 7748'; C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 2 Testing record 7748 05-FEB-30 Filler 408 44 Execution Plan ---------------------------------------------------------- Plan hash value: 3325179317 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 5 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C2"='Testing record 7748') filter("C2"='Testing record 7748') SQL> SQL> select * from t1 where c3 = trunc(sysdate+9); C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 0 Testing record 9 28-NOV-08 Filler 9 9 Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C3"=TRUNC(SYSDATE@!+9)) SQL> SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993); no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=9993 AND "C2"='Testing record 9993' AND "C3"=TRUNC(SYSDATE@!+9993)) SQL> SQL> set autotrace off SQL> SQL> truncate table t1; Table truncated. SQL> SQL> -- SQL> -- Data with cycling C1 and C2 values and unique C3 values SQL> -- SQL> begin 2 for i in 1..10101 loop 3 insert into t1 4 values (mod(i, 3), 'Testing record '||mod(i,3), trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963)); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> SQL> commit; Commit complete. SQL> SQL> -- SQL> -- 'Standard' statistics SQL> -- SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING'); PL/SQL procedure successfully completed. SQL> SQL> set autotrace on linesize 132 SQL> SQL> select * From t1 where c1 =433; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=433) SQL> SQL> select * From t1 where c2 = 'Testing record 7748'; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 3325179317 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 5 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C2"='Testing record 7748') filter("C2"='Testing record 7748') SQL> SQL> select * from t1 where c3 = trunc(sysdate+9); C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 0 Testing record 0 28-NOV-08 Filler 9 9 Execution Plan ---------------------------------------------------------- Plan hash value: 3325179317 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 5 (0)| 00:00:01 | <span style="color:#990000;">|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |</span> ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C3"=TRUNC(SYSDATE@!+9)) filter("C3"=TRUNC(SYSDATE@!+9)) SQL> SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993); no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=9993 AND "C2"='Testing record 9993' AND "C3"=TRUNC(SYSDATE@!+9993)) SQL> SQL> set autotrace off SQL> SQL> -- SQL> -- Statistics with auto-sized histograms on indexed columns SQL> -- SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'); PL/SQL procedure successfully completed. SQL> SQL> set autotrace on linesize 132 SQL> SQL> select * From t1 where c1 =433; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=433) SQL> SQL> select * From t1 where c2 = 'Testing record 7748'; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 3325179317 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 5 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C2"='Testing record 7748') filter("C2"='Testing record 7748') SQL> SQL> select * from t1 where c3 = trunc(sysdate+9); C1 C2 C3 C4 C5 C6 ---------- -------------------- --------- ---------- ---------- ---------- 0 Testing record 0 28-NOV-08 Filler 9 9 Execution Plan ---------------------------------------------------------- Plan hash value: 3325179317 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 5 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C3"=TRUNC(SYSDATE@!+9)) filter("C3"=TRUNC(SYSDATE@!+9)) SQL> SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993); no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 552572096 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=9993 AND "C2"='Testing record 9993' AND "C3"=TRUNC(SYSDATE@!+9993)) SQL> SQL> set autotrace off SQL></span></pre> Note that as the selectivity of the leading columns (C1 and C2) decreases the plans change from TABLE ACCESS FULL to INDEX SKIP SCAN when column C1 is not included in the where clause. Note also that the queries did NOT change and that histograms made no difference in the plans.<br /><br /> So how does Oracle treat a 'select * from t1 where c3 = trunc(sysdate+9)' query when the index is built on columns (c1,c2,c3)? That depends entirely upon the data and how skewed (or not) it may be. [It also depends upon the Oracle version in use, as releases after 8.1.7.4 implemented changes in the available query plans and how indexes could be used.] Because of deletes/inserts a plan can change even though the query has not, so there is no 'definitive' answer to the question as written. The conditions are simply too vague to produce repeatable results.<br /><br /> And that's a definite maybe.<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-3512114719242110516?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-62781598026032115822008-10-31T13:42:00.004Z2009-02-11T19:21:55.209Z2009-02-11T19:21:55.209ZFlashback query, available since Oracle 9i, can really be a lifesaver when a need arises to resurrect data. But, what if the UNDO has been overwritten by another process? To the rescue comes DBMS_LOGMNR, the LogMiner package. This utility dredges through the redo logs (and archive logs, if they are still available) to return both the SQL statements to redo the transactions and SQL statements to undo those same transactions. Since Oracle will automatically replay any in-doubt transactions occuring at the time of a crash retrieving the redo SQL is usually not necessary. Returning the undo SQL, however, may be useful when flashback query cannot be used.<br /><br /> DBMS_LOGMNR has several procedures available, of which we'll use three in this example: ADD_LOGFILE, START_LOGMNR and END_LOGMNR. You can start up LogMiner then add the logfiles you wish to 'mine', or add the logfiles then start the utility. I prefer the latter method, which is illustrated here. We'll start by updating the EMP table: <pre><span style="color:#009900;">SQL> update emp set comm = 999 where comm = 1000; 10 rows updated. SQL> commit; Commit complete. SQL></span></pre> Now let's mine the redo logs and see if we can undo that change: <pre><span style="color:#009900;">SQL> -- SQL> -- Add every redo log to the 'mix' so LogMiner can SQL> -- use them SQL> -- SQL> select 'exec dbms_logmnr.add_logfile('''||member||''')' 2 from v$logfile 3 SQL> SQL> spool add_logfiles.sql SQL> / 'EXECDBMS_LOGMNR.ADD_LOGFILE('''||MEMBER||''')' ------------------------------------------------------------------------------------- exec dbms_logmnr.add_logfile('/zing/flork/dapplenap/redo01.log') exec dbms_logmnr.add_logfile('/zang/flork/dapplenap/redo02.log') exec dbms_logmnr.add_logfile('/zong/flork/dapplenap/redo03.log') SQL> spool off SQL> SQL> SQL> @add_logfiles SQL> exec dbms_logmnr.add_logfile('/zing/flork/dapplenap/redo01.log') PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile('/zang/flork/dapplenap/redo02.log') PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile('/zong/flork/dapplenap/redo03.log') PL/SQL procedure successfully completed. SQL> SQL> -- SQL> -- Fire up LogMiner SQL> -- SQL> exec dbms_logmnr.start_logmnr(options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG) PL/SQL procedure successfully completed. SQL> SQL> SQL> -- SQL> -- Prepare the environment for the output SQL> -- SQL> set linesize 5000 trimspool on SQL> SQL> SQL> -- SQL> -- Retrieve the SQL statements to 'undo' the SQL> -- committed changes SQL> -- SQL> select sql_undo 2 from v$logmnr_contents 3 where seg_owner = upper('&1') 4 SQL> SQL> spool undo_committed_changes.sql SQL> / Enter value for 1: ortofon old 3: where seg_owner = upper('&1') new 3: where seg_owner = upper('bing') SQL_UNDO ------------------------------------------------------------------------------------------------------------- update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAA'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAD'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAF'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAG'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAH'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAI'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAK'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAL'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAM'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAN'; SQL> spool off SQL> SQL> SQL> -- SQL> -- Shut down LogMiner SQL> -- SQL> exec dbms_logmnr.end_logmnr PL/SQL procedure successfully completed. SQL></span></pre> And we have displayed the statements necessary to undo the updates made to the EMP table earlier. This won't work if the table is created NOLOGGING as no redo entries will be written for such transactions. Also notice that the original update was one statement, and the undo (from the redo logs) generates 10 statements, one for each row updated.<br /><br /> Redo logs were used in this example, however you can also use archivelogs as well (as noted earlier, they must still be available on the server). And you can ask Oracle to add redo logs and archivelogs as necessary; the CONTINUOUS_MINE option provides that functionality, requiring only that the first redo log be added via ADD_LOGFILE or the starting SCN for the transactions of interest be provided: <pre><span style="color:#009900;">SQL> -- SQL> -- Add one redo log to the 'mix' SQL> -- SQL> select 'exec dbms_logmnr.add_logfile('''||member||''')' 2 from v$logfile 3 where rownum = 1 4 SQL> SQL> spool add_logfiles.sql SQL> / 'EXECDBMS_LOGMNR.ADD_LOGFILE('''||MEMBER||''')' -------------------------------------------------------------------------------------exec dbms_logmnr.add_logfile('/zing/flork/dapplenap/redo01.log') SQL> spool off SQL> SQL> SQL> @add_logfiles SQL> exec dbms_logmnr.add_logfile('/zing/flork/dapplenap/redo01.log') PL/SQL procedure successfully completed. SQL> SQL> -- SQL> -- Fire up LogMiner SQL> -- SQL> -- The CONTINUOUS_MINE option cannot be used if the database SQL> -- is not running in ARCHIVELOG mode SQL> -- SQL> -- But, hey, we are, so we're good to go SQL> -- SQL> exec dbms_logmnr.start_logmnr(options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE) PL/SQL procedure successfully completed. SQL> SQL> SQL> -- SQL> -- Prepare the environment for the output SQL> -- SQL> set linesize 5000 trimspool on SQL> SQL> SQL> -- SQL> -- Retrieve the SQL statements to 'undo' the SQL> -- committed changes SQL> -- SQL> select sql_undo 2 from v$logmnr_contents 3 where seg_owner = upper('&1') 4 SQL> SQL> spool undo_committed_changes.sql SQL> / Enter value for 1: ortofon old 3: where seg_owner = upper('&1') new 3: where seg_owner = upper('bing') SQL_UNDO ------------------------------------------------------------------------------------------------------------- update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAA'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAD'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAF'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAG'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAH'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAI'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAK'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAL'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAM'; update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAN'; SQL> spool off SQL> SQL> SQL> -- SQL> -- Shut down LogMiner SQL> -- SQL> exec dbms_logmnr.end_logmnr PL/SQL procedure successfully completed. SQL></span></pre> We, of course, found the same records as in the prior example, but we didn't need to include every log file in the database to get this to work; the CONTINUOUS_MINE option kept adding logs to the mix to find the information we requested. Yes, we supplied more than one option to the options parameter; we simply added the values together and DBMS_LOGMNR was able to know we wanted both options enabled.<br /><br /> LogMiner won't solve every data resurrection problem, nor will flashback query, however knowing these options are available may make your life as a DBA a bit less hectic and stressful.<br /><br /> Well, we can dream.<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-6278159802603211582?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-32753764053949350542008-10-20T18:16:00.003+01:002008-10-30T13:32:33.848Z2008-10-30T13:32:33.848ZThere has been, recently, a large number of requests to google.com about Oracle database locks. I've blogged before on locks and how to see who has which object locked, but I haven't said much as to the nature and intent of locks. I think it's about time that topic was addressed.<br /><br /> Oracle, in the on-line documentation, states:<br /><br /> "Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource — either user objects such as tables and rows or system objects not visible to users, such as shared data structures in memory and data dictionary rows."<br /><br /> <span style="color:#009900;">[To get a bit picky here, locks on shared data structures in memory are usually called latches, but they perform the same task so we'll carry on with the discussion.]</span><br /><br /> So a lock, when taken, prevents 'destructive interaction' between transactions. What DOES that mean? Possibly an example between two sessions trying to modify data in the same table will illustrate (this would be a 'blocking' lock, and querying V$LOCK where block <> 0 would show session #2 blocked by session #1). Session #1 locks and modifies data in the EMP table by taking both a TX (transaction row-level) and a TM (row exclusive) lock: <pre><span style="color:#009900;">SQL> select empno, ename, sal 2 from emp; EMPNO ENAME SAL ---------- ---------- ---------- 7935 SMITH 900 7369 SMYTHE 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 EMPNO ENAME SAL ---------- ---------- ---------- 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 7955 SMITH 900 7956 SMYTHE 800 7957 ALLEN 1600 7958 WARD 1250 7959 JONES 2975 7960 MARTIN 1250 7961 BLAKE 2850 EMPNO ENAME SAL ---------- ---------- ---------- 7962 CLARK 2450 7963 SCOTT 3000 7964 KING 5000 7965 TURNER 1500 7966 ADAMS 1100 7967 JAMES 950 7968 FORD 3000 7969 MILLER 1300 8000 SMITH 900 8001 SMYTHE 1382.4 8002 ALLEN 2764.8 EMPNO ENAME SAL ---------- ---------- ---------- 8003 WARD 2160 8004 JONES 5140.8 8005 MARTIN 2160 8006 BLAKE 4924.8 8007 CLARK 4233.6 8008 SCOTT 5184 8009 KING 8640 8010 TURNER 2592 8011 ADAMS 1900.8 8012 JAMES 1641.6 8013 FORD 5184 EMPNO ENAME SAL ---------- ---------- ---------- 8014 MILLER 2246.4 8015 SMITH 1555.2 8016 SMYTHE 1382.4 8017 ALLEN 2764.8 8018 WARD 2160 8019 JONES 5140.8 8020 MARTIN 2160 8021 BLAKE 4924.8 8022 CLARK 4233.6 8023 SCOTT 5184 8024 KING 8640 EMPNO ENAME SAL ---------- ---------- ---------- 8025 TURNER 2592 8026 ADAMS 1900.8 8027 JAMES 1641.6 8028 FORD 5184 8029 MILLER 2246.4 60 rows selected. SQL> SQL> update emp 2 set sal = sal *.985 3 where empno <= 8000; 31 rows updated. SQL> SQL> exec dbms_lock.sleep(60) </span></pre> At this point we start session #2 and try to modify the same data in the same table: <pre><span style="color:#009900;">SQL> select empno, ename, sal 2 from emp; EMPNO ENAME SAL ---------- ---------- ---------- 7935 SMITH 900 7369 SMYTHE 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 EMPNO ENAME SAL ---------- ---------- ---------- 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 7955 SMITH 900 7956 SMYTHE 800 7957 ALLEN 1600 7958 WARD 1250 7959 JONES 2975 7960 MARTIN 1250 7961 BLAKE 2850 EMPNO ENAME SAL ---------- ---------- ---------- 7962 CLARK 2450 7963 SCOTT 3000 7964 KING 5000 7965 TURNER 1500 7966 ADAMS 1100 7967 JAMES 950 7968 FORD 3000 7969 MILLER 1300 8000 SMITH 900 8001 SMYTHE 1382.4 8002 ALLEN 2764.8 EMPNO ENAME SAL ---------- ---------- ---------- 8003 WARD 2160 8004 JONES 5140.8 8005 MARTIN 2160 8006 BLAKE 4924.8 8007 CLARK 4233.6 8008 SCOTT 5184 8009 KING 8640 8010 TURNER 2592 8011 ADAMS 1900.8 8012 JAMES 1641.6 8013 FORD 5184 EMPNO ENAME SAL ---------- ---------- ---------- 8014 MILLER 2246.4 8015 SMITH 1555.2 8016 SMYTHE 1382.4 8017 ALLEN 2764.8 8018 WARD 2160 8019 JONES 5140.8 8020 MARTIN 2160 8021 BLAKE 4924.8 8022 CLARK 4233.6 8023 SCOTT 5184 8024 KING 8640 EMPNO ENAME SAL ---------- ---------- ---------- 8025 TURNER 2592 8026 ADAMS 1900.8 8027 JAMES 1641.6 8028 FORD 5184 8029 MILLER 2246.4 60 rows selected. SQL> SQL> update emp 2 set sal = sal * 1.005 3 where empno <= 8000; </span></pre> The update process stops here, waiting for session #1 to commit its changes. Session #1 has an exclusive lock on the data we want to modify in session #2, so that session needs to wait until the lock is cleared (by a commit or a rollback) to effect any changes. As we continue on in both sessions we see that session #1 has ended its waiting period and committed its changes: <pre><span style="color:#009900;">PL/SQL procedure successfully completed. SQL> SQL> commit; Commit complete. SQL> SQL> select empno, ename, sal 2 from emp; EMPNO ENAME SAL ---------- ---------- ---------- 7935 SMITH 886.5 7369 SMYTHE 788 7499 ALLEN 1576 7521 WARD 1231.25 7566 JONES 2930.38 7654 MARTIN 1231.25 7698 BLAKE 2807.25 7782 CLARK 2413.25 7788 SCOTT 2955 7839 KING 4925 7844 TURNER 1477.5 EMPNO ENAME SAL ---------- ---------- ---------- 7876 ADAMS 1083.5 7900 JAMES 935.75 7902 FORD 2955 7934 MILLER 1280.5 7955 SMITH 886.5 7956 SMYTHE 788 7957 ALLEN 1576 7958 WARD 1231.25 7959 JONES 2930.38 7960 MARTIN 1231.25 7961 BLAKE 2807.25 EMPNO ENAME SAL ---------- ---------- ---------- 7962 CLARK 2413.25 7963 SCOTT 2955 7964 KING 4925 7965 TURNER 1477.5 7966 ADAMS 1083.5 7967 JAMES 935.75 7968 FORD 2955 7969 MILLER 1280.5 8000 SMITH 886.5 8001 SMYTHE 1382.4 8002 ALLEN 2764.8 EMPNO ENAME SAL ---------- ---------- ---------- 8003 WARD 2160 8004 JONES 5140.8 8005 MARTIN 2160 8006 BLAKE 4924.8 8007 CLARK 4233.6 8008 SCOTT 5184 8009 KING 8640 8010 TURNER 2592 8011 ADAMS 1900.8 8012 JAMES 1641.6 8013 FORD 5184 EMPNO ENAME SAL ---------- ---------- ---------- 8014 MILLER 2246.4 8015 SMITH 1555.2 8016 SMYTHE 1382.4 8017 ALLEN 2764.8 8018 WARD 2160 8019 JONES 5140.8 8020 MARTIN 2160 8021 BLAKE 4924.8 8022 CLARK 4233.6 8023 SCOTT 5184 8024 KING 8640 EMPNO ENAME SAL ---------- ---------- ---------- 8025 TURNER 2592 8026 ADAMS 1900.8 8027 JAMES 1641.6 8028 FORD 5184 8029 MILLER 2246.4 60 rows selected. SQL></span></pre> We can see that the only changes to see are those committed by session #1. Session #2 now has 'free reign' over that same data, and implements its changes: <pre><span style="color:#009900;"> 31 rows updated. SQL> SQL> exec dbms_lock.sleep(60) PL/SQL procedure successfully completed. SQL> SQL> commit; Commit complete. SQL> SQL> select empno, ename, sal 2 from emp; EMPNO ENAME SAL ---------- ---------- ---------- 7935 SMITH 890.93 7369 SMYTHE 791.94 7499 ALLEN 1583.88 7521 WARD 1237.41 7566 JONES 2945.03 7654 MARTIN 1237.41 7698 BLAKE 2821.29 7782 CLARK 2425.32 7788 SCOTT 2969.78 7839 KING 4949.63 7844 TURNER 1484.89 EMPNO ENAME SAL ---------- ---------- ---------- 7876 ADAMS 1088.92 7900 JAMES 940.43 7902 FORD 2969.78 7934 MILLER 1286.9 7955 SMITH 890.93 7956 SMYTHE 791.94 7957 ALLEN 1583.88 7958 WARD 1237.41 7959 JONES 2945.03 7960 MARTIN 1237.41 7961 BLAKE 2821.29 EMPNO ENAME SAL ---------- ---------- ---------- 7962 CLARK 2425.32 7963 SCOTT 2969.78 7964 KING 4949.63 7965 TURNER 1484.89 7966 ADAMS 1088.92 7967 JAMES 940.43 7968 FORD 2969.78 7969 MILLER 1286.9 8000 SMITH 890.93 8001 SMYTHE 1382.4 8002 ALLEN 2764.8 EMPNO ENAME SAL ---------- ---------- ---------- 8003 WARD 2160 8004 JONES 5140.8 8005 MARTIN 2160 8006 BLAKE 4924.8 8007 CLARK 4233.6 8008 SCOTT 5184 8009 KING 8640 8010 TURNER 2592 8011 ADAMS 1900.8 8012 JAMES 1641.6 8013 FORD 5184 EMPNO ENAME SAL ---------- ---------- ---------- 8014 MILLER 2246.4 8015 SMITH 1555.2 8016 SMYTHE 1382.4 8017 ALLEN 2764.8 8018 WARD 2160 8019 JONES 5140.8 8020 MARTIN 2160 8021 BLAKE 4924.8 8022 CLARK 4233.6 8023 SCOTT 5184 8024 KING 8640 EMPNO ENAME SAL ---------- ---------- ---------- 8025 TURNER 2592 8026 ADAMS 1900.8 8027 JAMES 1641.6 8028 FORD 5184 8029 MILLER 2246.4 60 rows selected. SQL></span></pre> No destructive interaction between the sessions could occur; session #2 could not update the data locked by session #1 until that lock was released. Once the first lock was gone a second lock, by another session, could be taken so further updates could be processed. Were there a third session attempting to modify this same data it, too, would be locked as session #2 was before, preventing multiple sessions from updating the same data immediately on top of, and interfering with, the current transaction.<br /><br /> Locks can be a problem, however, especially when one session has locked data another session needs, and the waiting session has locked data the first session wants to modify. This is known as a deadlock, and later releases of Oracle detect such conditions and issue a rollback on the 'newer' transaction (here 'newer' means the transaction which didn't lock the data its waiting upon but has data a transaction higher up the queue needs to access or modify). To give a representation of this:<br /><br /> -- Transaction A modifies data in the EMP table<br /> -- Transaction B modifies data in the DEPT table<br /> -- Transaction A now needs to modify records in the DEPT table,<br /> but transaction B has those records locked<br /> -- Transaction B now wants to modify records in the EMP table<br /> that transaction A has locked<br /> -- Oracle will 'rollback' transaction B as it's in the queue after<br /> transaction A and holds data that transaction A needs to complete<br /> its work<br /><br /> Older releases of Oracle (pre-9.0) will let a deadlock continue forever, so the DBA will need to handle such occurrences in 8.1.7.4 and earlier versions.<br /><br /> Some locks don't affect anyone, such as shared row locks (taken by plain old SELECT statements); others, such as those taken by DDL statements, prevent any action against the affected object. Additionally, since DDL transactions implicitly commit before and after the statement execution the exclusive object lock is only active for a short period of time, which in many cases won't noticeably affect other sessions.<br /><br /> Locks may be considered by some to be inconvenient, but they are necessary to preserve data integrity and provide transaction isolation (see the example above). And transaction isolation and data integrity are important if your data has any worth at all (and everyone's data is worth something to their user community).<br /><br /> "Lock 'em up, Sheriff!"<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-3275376405394935054?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-15734081449928308752008-10-14T19:14:00.002+01:002008-10-14T21:28:25.478+01:002008-10-14T21:28:25.478+01:00It's interesting to see what people submit to google.com; lately this query came to my attention:<br /><br /> "how to see when the table is populated in oracle"<br /><br /> which, on the face of it, seems to be a rather silly question, with a somewhat obvious answer:<br /><br /> "select count(*) from [insert table name here];"<br /><br /> But, is that a truly reliable way to take the inventory of a table's contents? Let's investigate this further and see what might not be so obvious from a cursory glance.<br /><br /> I've discussed, in more than one post, how Oracle provides a consistent image of the data for the point in time when a query begins. And I've proven you can't rely upon a select statement to provide a repeatable and usable result to manually generate sequential numbers. This same mechanism can wreak havoc on determining whether a table is populated or not because at the time you may be querying the table's contents someone else may be deleting those same records that appear before your very eyes. Absent a commit in the 'offending' session you'll never know that Mercantile Flabbenjammitz just obliterated all of that data because, through the miracle of the UNDO records, you can still see every byte that used to be stored in that table. Along comes a commit and -- whoosh! -- the data you saw just a moment earlier now vanishes.<br /><br /> So how DOES one know when a table is populated? Popular 'wisdom' provides several possible answers:<br /><br /> Table statistics<br /> The High-Water Mark (HWM)<br /> The aforementioned "select count(*) from ..."<br /> Querying DBA_EXTENTS<br /><br /> How many of those are reliable? Let's find out.<br /><br /> Table statistics are a really good indicator of whether or not a table is populated, until, well, it isn't populated any more: <pre><span style="color:#3333ff;">SQL> create table blarzenflotz( 2 glerm number, 3 snangul varchar2(40), 4 borm number 5 ); Table created. SQL> SQL> begin 2 for i in 1..1000000 loop 3 insert into blarzenflotz 4 values(i, 'Schnerkenporf vasul '||i, mod(i*47, 3)); 5 end loop; 6 7 commit; 8 9 end; 10 / PL/SQL procedure successfully completed. SQL> SQL> begin 2 for i in 1..1000000 loop 3 insert into blarzenflotz 4 values(i, 'Schnerkenporf vasul '||i, mod(i*47, 3)); 5 end loop; 6 7 commit; 8 9 end; 10 / PL/SQL procedure successfully completed. SQL> SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BLARZENFLOTZ', estimate_percent => 100) PL/SQL procedure successfully completed. SQL> SQL> select count(*) 2 from blarzenflotz; COUNT(*) ---------- 2000000 SQL> SQL> select num_rows 2 from user_tables 3 where table_name = 'BLARZENFLOTZ'; NUM_ROWS ---------- 2000000 SQL> SQL> delete from blarzenflotz; 2000000 rows deleted. SQL> SQL> select count(*) 2 from blarzenflotz; COUNT(*) ---------- 0 SQL> SQL> select num_rows 2 from user_tables 3 where table_name = 'BLARZENFLOTZ'; NUM_ROWS ---------- 2000000 SQL></span></pre> Without gathering new statistics after such an operation (and that's not likely to occur since statistics gathering is usually a scheduled task) you can see how deceiving they can be. There are no rows in the table, yet the statistics state otherwise. Hmmmm. A similar fate befalls you if someone else, in another session, is deleting rows from the table of interest and has yet to commit the deed. You'll go happily along, 'knowing' there are two-million rows in the table, blissfully unaware that Morgenstern Slapdragon has just deleted every last one of them. Your 'select count(*) from ...' query returns the expected 2000000 as a result, and old Morgenstern, running the same query, reports that nothing is left of that data. Sneak in a 'commit' and now both queries return what Morgenstern knew five minutes ago -- the data in that table is gone.<br /><br /> Will querying DBA_EXTENTS tell you anything of worth with respect to the actual data population? I'm afraid not, as that view will only tell you that X extents have been allocated to the object/segment; there is no information in that view to report if those extents are actually populated.<br /><br /> The high-water mark won't tell you anything, either, as that is the end of the blocks which have or did have data in them. The high-water mark is not reset by deletes, only by a truncate, so deleting all of the data in a table won't move the HWM, and using that to indicate data population is as reliable as the statistics or the 'select count(*) from ...' query in a multi-user environment. And even using ROWID is suspect, as those are retrieved from the UNDO blocks to return a consistent data set regardless of the state or number of uncommitted transactions against the table or tables queried.<br /><br /> So how DO you know when a table is populated? You play your cards and you take your chances. Odds are in your favor such tools as described here will provide a good idea whether a table is populated or not. Just remember that because of Oracle's read consistency mechanism sometimes those results could be wrong.<br /><br /> But the odds of the results being correct are far better than any you can get in Las Vegas.<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-1573408144992830875?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-91482076765135474122008-10-01T14:10:00.010+01:002008-10-05T01:48:58.035+01:002008-10-05T01:48:58.035+01:00Careful coding is a must in application development but, occasionally, the best laid plans go astray. Enter the ORA-00947 error, caused by queries and insert statements having too few values. Let's look at a few examples of how this can happen.<br /><br /> A simple query can become a nightmare if the subquery doesn't return the proper number of values: <pre><span style="color:#3333ff;">SQL> select e.empno, e.ename, d.loc, m.ename 2 from emp e , emp m, dept d 3 where m.empno = e.mgr 4 and d.deptno = e.deptno 5 and e.deptno = m.deptno 6 and (d.deptno, d.dname) in (select deptno from emp) 7 / and (d.deptno, d.dname) in (select deptno from emp) * ERROR at line 6: <span style="color:#990000;">ORA-00947: not enough values</span> SQL></span></pre> Yes, this was an obvious example, but it does illustrate the point. Insert statements are also not immune to returning such an error, through the values clause: <pre><span style="color:#3333ff;">SQL> create table yingsplut( 2 hargen number, 3 neebo number, 4 snerm varchar2(75) 5 ); Table created. SQL> SQL> insert into yingsplut 2 values (17, 3) 3 / insert into yingsplut * ERROR at line 1: <span style="color:#990000;">ORA-00947: not enough values </span> SQL></span></pre> Or through a select statement: <pre><span style="color:#3333ff;">SQL> insert into yingsplut 2 select empno, sal from emp 3 / insert into yingsplut * ERROR at line 1: <span style="color:#990000;">ORA-00947: not enough values </span> SQL> </span></pre> Materialized view refreshes can also be affected if the materialized view was created with 'select * from ...' and the source table definition has changed since the materialized view was created. The solution here is to recreate the materialized view using a query listing all of the desired columns explicitly. Yes, in some cases that won't stop base table definition changes from 'hosing' your refresh, but simply adding columns to a base table definition won't result in the refresh job generating an error.<br /><br /> Normally this error wouldn't arise from such simple code; having a table with a large number of columns, though, can easily provide insert statements that miss the mark by one or more values simply because the developer lost count of default or NULL values: <pre><span style="color:#3333ff;">SQL> create table valtest( 2 val_key number, 3 val1 varchar2(12), 4 val2 varchar2(12), 5 val3 varchar2(12), 6 val4 varchar2(12), 7 val5 varchar2(12), 8 val6 varchar2(12), 9 val7 varchar2(12), 10 val8 varchar2(12), 11 val9 varchar2(12), 12 val10 varchar2(12), 13 val11 varchar2(12), 14 val12 varchar2(12), 15 val13 varchar2(12), 16 val14 varchar2(12), 17 val15 varchar2(12), 18 val16 varchar2(12), 19 val17 varchar2(12), 20 val18 varchar2(12), 21 val19 varchar2(12), 22 val20 varchar2(12), 23 val21 varchar2(12), 24 val22 varchar2(12), 25 val23 varchar2(12), 26 val24 varchar2(12), 27 val25 varchar2(12), 28 val26 varchar2(12), 29 val27 varchar2(12), 30 val28 varchar2(12), 31 val29 varchar2(12), 32 val30 varchar2(12), 33 val31 varchar2(12), 34 val32 varchar2(12), 35 val33 varchar2(12), 36 val34 varchar2(12), 37 val35 varchar2(12), 38 val36 varchar2(12), 39 val37 varchar2(12) 40 ); Table created. SQL> SQL> insert into valtest 2 values (1, 3 'This', 4 'is', 5 'the', 6 'first', 7 'record', 8 'inserted', 9 'into', 10 'my', 11 'table', 12 'and', 13 'it''s', 14 'a', 15 'really', 16 'long', 17 'one', 18 'because', 19 'I', 20 'wanted', 21 'to', 22 'be', 23 'as', 24 'difficult', 25 'as', 26 'possible', 27 NULL, 28 NULL, 29 NULL, 30 NULL, 31 NULL); insert into valtest * ERROR at line 1: <span style="color:#990000;">ORA-00947: not enough values </span> SQL> </span></pre> PL/SQL blocks are not immune to this, either, when using BULK COLLECT if the collection is defined absent a column or two: <pre><span style="color:#3333ff;">SQL> create table lotsa_data( 2 data_id number, 3 data_set number, 4 data_val varchar2(40), 5 proc_dt date 6 ); Table created. SQL> SQL> create table less_data( 2 data_id number, 3 data_set number, 4 data_val varchar2(40) 5 ); Table created. SQL> SQL> create table ref_data( 2 data_id number, 3 data_set number 4 ); Table created. SQL> SQL> begin 2 for i in 1..10000 loop 3 insert into lotsa_data 4 values(i, mod(i, 17), 'Test data statement '||i, sysdate); 5 insert into ref_data 6 values(i, mod(i,17)); 7 end loop; 8 9 commit; 10 11 end; 12 / PL/SQL procedure successfully completed. SQL> SQL> declare 2 type ldat_tab_typ is table of less_data%rowtype index by binary_integer; 3 4 d_tab ldat_tab_typ; 5 begin 6 update lotsa_data 7 set data_id = data_id + 10 8 where data_set = 16 9 returning data_id, data_set, data_val, proc_dt bulk collect into d_tab; 10 11 for i in 1..d_tab.count loop 12 dbms_output.put_line(d_tab(i).data_id||' '||d_tab(i).data_set||' '||d_tab(i).data_val); 13 end loop; 14 end; 15 / returning data_id, data_set, data_val, proc_dt bulk collect into d_tab; * ERROR at line 9: ORA-06550: line 9, column 79: PL/SQL: <span style="color:#990000;">ORA-00947: not enough values </span> ORA-06550: line 6, column 9: PL/SQL: SQL Statement ignored SQL> </span></pre> <span style="color:#990000;">[If you go the other way, and have too many columns in your collection an ORA-00913 appears: <pre><span style="color:#990000;">SQL> declare 2 type ldat_tab_typ is table of lotsamore_data%rowtype index by binary_integer; 3 4 d_tab ldat_tab_typ; 5 begin 6 update lotsa_data 7 set data_id = data_id + 10 8 where data_set = 16 9 returning data_id, data_set, data_val, proc_dt bulk collect into d_tab; 10 11 for i in 1..d_tab.count loop 12 dbms_output.put_line(d_tab(i).data_id||' '||d_tab(i).data_set||' '||d_tab(i).data_val); 13 end loop; 14 end; 15 / returning data_id, data_set, data_val, proc_dt bulk collect into d_tab; * ERROR at line 9: ORA-06550: line 9, column 79: PL/SQL: ORA-00913: too many values ORA-06550: line 6, column 9: PL/SQL: SQL Statement ignored SQL> </span></pre> The solution to both conditions is the same, so read on.]</span><br /><br /> Troubleshooting such code is a time-consuming process, as the offending VALUES statements, SELECT queries or collection variables need to be carefully examined and the argument count/column count verified with the number of columns in the inserted table. Let's take the very first example and fix it: <pre><span style="color:#3333ff;">SQL> create table ref_dept 2 as select deptno, dname, loc 3 from dept 4 where deptno in (10, 30, 70); Table created. SQL> SQL> select e.empno, e.ename, d.loc, m.ename 2 from emp e , emp m, dept d 3 where m.empno = e.mgr 4 and d.deptno = e.deptno 5 and e.deptno = m.deptno 6 and (d.deptno, d.dname) in (select deptno, dname from ref_dept) 7 / EMPNO ENAME LOC ENAME ---------- ---------- ------------- ---------- 7934 MILLER NEW YORK CLARK 7782 CLARK NEW YORK KING 7499 ALLEN CHICAGO BLAKE 7654 MARTIN CHICAGO BLAKE 7900 JAMES CHICAGO BLAKE 7844 TURNER CHICAGO BLAKE 7521 WARD CHICAGO BLAKE 7 rows selected. SQL> </span></pre> Notice we created a new reference table, and populated it with the desired values. The reference table was then used in the subquery to return the proper number of values, along with the correct department numbers and names, to return the expected result set. Sharp eyes will note that we didn't need the reference table as we could have queried the DEPT table again with the qualifying WHERE clause: <pre><span style="color:#3333ff;">SQL> select e.empno, e.ename, d.loc, m.ename 2 from emp e , emp m, dept d 3 where m.empno = e.mgr 4 and d.deptno = e.deptno 5 and e.deptno = m.deptno 6 and (d.deptno, d.dname) in (select deptno, dname from dept where deptno in (10,30,70)) 7 / EMPNO ENAME LOC ENAME ---------- ---------- ------------- ---------- 7934 MILLER NEW YORK CLARK 7782 CLARK NEW YORK KING 7499 ALLEN CHICAGO BLAKE 7654 MARTIN CHICAGO BLAKE 7900 JAMES CHICAGO BLAKE 7844 TURNER CHICAGO BLAKE 7521 WARD CHICAGO BLAKE 7 rows selected. SQL> </span></pre> Correcting the INSERT statement takes a bit more time, since the column count and value count must match. But time and patience are rewarded: <pre><span style="color:#3333ff;">SQL> insert into valtest 2 values (1, 3 'This', 4 'is', 5 'the', 6 'first', 7 'record', 8 'inserted', 9 'into', 10 'my', 11 'table', 12 'and', 13 'it''s', 14 'a', 15 'really', 16 'long', 17 'one', 18 'because', 19 'I', 20 'wanted', 21 'to', 22 'be', 23 'as', 24 'difficult', 25 'as', 26 'possible', 27 'since', 28 'my', 29 'goldfish', 30 'can''t', 31 'dance', 32 'after', 33 'thirteen', 34 'cups', 35 'of', 36 'grape', 37 'Kool-Aid', 38 'from', 39 'Duluth'); 1 row created. SQL> </span></pre> Fixing the last example takes nothing more than changing the PL/SQL table to contain all of the referenced columns: <pre><span style="color:#3333ff;">SQL> declare 2 type ldat_tab_typ is table of lotsa_data%rowtype index by binary_integer; 3 4 d_tab ldat_tab_typ; 5 begin 6 update lotsa_data 7 set data_id = data_id + 10 8 where data_set = 16 9 returning data_id, data_set, data_val, proc_dt bulk collect into d_tab; 10 11 for i in 1..d_tab.count loop 12 dbms_output.put_line(d_tab(i).data_id||' '||d_tab(i).data_set||' '||d_tab(i).data_val); 13 end loop; 14 end; 15 / 349 16 Test data statement 339 366 16 Test data statement 356 383 16 Test data statement 373 400 16 Test data statement 390 [... much more output here ...] 9393 16 Test data statement 9383 9410 16 Test data statement 9400 9427 16 Test data statement 9417 9444 16 Test data statement 9434 9461 16 Test data statement 9451 9478 16 Test data statement 9468 9495 16 Test data statement 9485 9512 16 Test data statement 9502 9529 16 Test data statement 9519 PL/SQL procedure successfully completed. SQL></span></pre> There is nothing more satisfying than the joy of a job well done. And there's nothing more frustrating than having an error like an ORA-00947 which somehow eludes correction. Sometimes it's best to let another pair of eyes peruse the code, as a fresh viewpoint can help reveal mistakes often overlooked by the original programmer, and can be of great assistance in fixing any problematic code. I'm not afraid to ask for help. You shouldn't be, either.<br /><br /> Now, let's code!<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-9148207676513547412?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-82006214704704525792008-09-29T18:40:00.008+01:002009-03-04T15:00:52.645Z2009-03-04T15:00:52.645ZFor those who suffer from endless curiosity the prospect of poking around the data dictionary is considered fun, and in the quest of such fun one can run across an interesting view or two. DBA_REFRESH_CHILDREN could be one of those views, and for the inquisitive an examination is in order.<br /><br /> DBA_REFRESH_CHILDREN lists all of the objects affected by every refresh group configured in the given database. <span style="color:#3333ff;">[There are also views which are a bit more restricted: ALL_REFRESH_CHILDREN (listing all refresh groups and affected objects accessible by the connected user) and USER_REFRESH_CHILDREN (listing all refresh groups and affected objects owned by the connected user).]</span> Of course it provides more information, such as the associated job number, the rollback/undo segment the group uses, the interval between refreshes and the date for the next refresh (among other details). The view description is as follows: <pre><span style="color:#3333ff;">SQL> desc dba_refresh_children Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) NAME NOT NULL VARCHAR2(30) TYPE VARCHAR2(30) ROWNER NOT NULL VARCHAR2(30) RNAME NOT NULL VARCHAR2(30) REFGROUP NUMBER IMPLICIT_DESTROY VARCHAR2(1) PUSH_DEFERRED_RPC VARCHAR2(1) REFRESH_AFTER_ERRORS VARCHAR2(1) ROLLBACK_SEG VARCHAR2(30) JOB NUMBER NEXT_DATE DATE INTERVAL VARCHAR2(200) BROKEN VARCHAR2(1) PURGE_OPTION NUMBER(38) PARALLELISM NUMBER(38) HEAP_SIZE NUMBER(38) SQL></span></pre> The PUSH_DEFERRED_RPC column indicates, for updatable materialized views, whether or not to push any changes made to the snapshot data to the master table or master materialized view before the refresh begins. The valid values are Y and N, where Y indicates Oracle will push the changes from the snapshot to the master and N (the default) indicates Oracle will not.<br /><br /> It's fairly straightforward to extract information from the view, as most of the columns names aren't ambigous; a generalized report might look like this: <pre><span style="color:#3333ff;">SQL> select owner, name, type, refgroup, job, next_date, interval 2 from dba_refresh_children; OWNER NAME TYPE REFGROUP JOB NEXT_DATE INTERVAL ---------- --------------------------- --------- --------- ---- ---------- -------------------------- NARBOW YARN_ORDS_PENDING_MV SNAPSHOT 13 95 30-SEP-08 TRUNC(SYSDATE + 1) + 4/24 NARBOW YARN_ORDS_BACKORD_MV SNAPSHOT 14 96 30-SEP-08 TRUNC(SYSDATE + 1) + 5/24 NARBOW DISCONTINUED_STOCK_NOS_MV SNAPSHOT 53 134 30-SEP-08 TRUNC(SYSDATE+1)+5/24 BORTUST RAW_MATL_BACKORD_MV SNAPSHOT 11 414 01-JAN-00 sysdate+365 SQL></span></pre> Job 414 in refresh group 11 is broken. It's broken because the next run date is Jan 1, 4000, the default date Oracle uses for jobs which shouldn't run: <pre><span style="color:#3333ff;">SQL> select owner, name, type, refgroup, job, to_char(next_date, 'DD-MON-RRRR') next_date, interval, broken 2 from dba_refresh_children 3 where refgroup = 11; OWNER NAME TYPE REFGROUP JOB NEXT_DATE INTERVAL B ---------- --------------------- ---------- --------- ---- ----------- -------------------------- - BORTUST RAW_MATL_BACKORD_MV SNAPSHOT 11 414 01-JAN-4000 sysdate+365 Y SQL></span></pre> The DBA_REFRESH_CHILDREN is a good 'one-stop shop' for information which can report on the health of your refresh jobs. You can find the broken jobs: <pre><span style="color:#3333ff;">SQL> select owner, name, job, refgroup 2 from dba_refresh_children 3 where broken = 'Y' 4 / OWNER NAME JOB REFGROUP ---------- ----------------------------------- ---- -------- BORTUST RAW_MATL_BACKORD_MV 414 11 SQL></span></pre> and it's nice to see there is only one. Of course discovering WHY the job is broken is another task; it's quite likely the source table or view has changed and no longer matches the destination definition, and the insert operation fails with either an ORA-00913 (too many values), an ORA-00947 (not enough values) or an error stating a data type mismatch. A search of the alert log may provide the answer; it may not, and the source code for the materialized view will be necessary to understand which local or remote objects were involved. And, in a large shop with a number of DBAs it may be as simple as asking a question. Make certain you're not 'spinning your wheels', though, as the users may not need the view or the job anymore and any effort to fix it would be effort wasted. Again, a quick question to the right people may save you hours of unnecessary work.<br /><br /> The PURGE_OPTION column is probably the most ambiguous of the bunch, and it refers to the method of purging the transaction queue after each 'push' (refresh); 1 indicates a quick purge, and 2 indicates a precise purge. These apply to deferred transactions (which can be used to refresh materialized views). A 'quick' purge is less costly in resources, but may cause deferred transaction records to remain visible for a period of time after the purge. A 'precise' purge consumes more resources but it does offer the benefit of a complete queue flush leaving no lingering traces.<br /><br /> I've said this before, and I'll say it again: having the proper tools for the job at hand can make that job so much easier; when dealing with snapshot/materialized view refreshes the DBA_REFRESH_CHILDREN view can save you time and effort in monitoring jobs and diagnosing problems.<br /><br /> Now, that's refreshing!<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-8200621470470452579?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-22073425841451827652008-09-26T19:58:00.006+01:002009-06-02T16:50:32.859+01:002009-06-02T16:50:32.859+01:00Passing a list of values to a function or procedure should be, well, simple, and it is, really, unless you have a dynamic list of unknown length. Simply trying to use the supplied string, as-is, can be disappointing: <pre><span style="color:#3333ff;">SQL> -- SQL> -- Let's try this the simple way SQL> -- SQL> create or replace function instring_list_test(subtype_list varchar2) 2 return number 3 is 4 lv_ct number; 5 6 cursor get_empinfo is 7 select count(*) 8 from emp 9 where deptno in subtype_list; 10 11 begin 12 13 open get_empinfo; 14 fetch get_empinfo into lv_ct; 15 close get_empinfo; 16 17 return lv_ct; 18 end; 19 / Function created. SQL> SQL> show errors No errors. SQL> SQL> -- SQL> -- The function created without error SQL> -- SQL> -- Let's see if it works SQL> -- SQL> select instring_list_test('10,20,30') from dual; select instring_list_test('10,20,30') from dual * ERROR at line 1: ORA-01722: invalid number ORA-06512: at "BING.INSTRING_LIST_TEST", line 14 SQL> SQL> -- SQL> -- That's silly, it should work ... SQL> --</span></pre> Since '10,20,30' isn't a number, and Oracle can't magically separate the individual values the function call fails. It would succeed were there one value in this dynamic list, but real-life situations usually aren't that simple and straightforward. Whatever shall we do? We need to 'get dirty' and actually code a way for Oracle to separate the values, make them numbers and populate a table, dynamically, so we can select from that table and generate a usable list. So, let's try this again and see if we can get this to do what we want: <pre><span style="color:#3333ff;">SQL> -- SQL> -- Let's try this again SQL> -- SQL> -- We'll create a table type first SQL> -- SQL> create or replace type InNumTab is table of number; 2 / Type created. SQL> SQL> -- SQL> -- Now we'll use that table type to massage SQL> -- the supplied string into a usable list SQL> -- SQL> create or replace function instring_list_test(subtype_list varchar2) 2 return number 3 is 4 -- 5 -- The parsed value 6 -- 7 lv_subtyp number; 8 -- 9 -- The table we'll populate 10 -- 11 lv_sublist InNumTab := InNumTab(); 12 -- 13 -- A place for the result 14 -- 15 lv_ct number; 16 -- 17 -- A variable so we can 'walk' the string 18 -- 19 startpos number:=1; 20 -- 21 -- Record counter to extend the table 22 -- 23 rec number:=1; 24 25 -- 26 -- Query using the dynamic IN list 27 -- 28 cursor get_empinfo (enums InNumTab) is 29 select count(*) 30 from emp 31 where deptno in (select column_value from table(cast(enums as InNumTab))); 32 33 begin 34 -- 35 -- Extend the table so we can start populating it 36 -- 37 lv_sublist.extend(rec); 38 39 -- 40 -- 'Walk' the provided string 41 -- The loop exits when no value separator is found 42 -- We expect the value separator to be a comma 43 -- 44 loop 45 exit when instr(subtype_list, ',', startpos) = 0; 46 lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,',', 1)-1); 47 lv_sublist(rec) := lv_subtyp; 48 startpos := instr(subtype_list, ',', startpos)+1; 49 rec := rec+1; 50 -- 51 -- After each addition we extend the table 52 -- 53 lv_sublist.extend(rec); 54 end loop; 55 56 -- 57 -- We extend the table one more time to hold our last value 58 -- 59 rec := rec+1; 60 lv_sublist.extend(rec); 61 lv_subtyp := substr(subtype_list, startpos); 62 lv_sublist(rec) := lv_subtyp; 63 64 -- 65 -- Get the count 66 -- 67 open get_empinfo(lv_sublist); 68 fetch get_empinfo into lv_ct; 69 close get_empinfo; 70 71 -- 72 -- Return the value to the caller 73 -- 74 return lv_ct; 75 end; 76 / Function created. SQL> SQL> show errors No errors. SQL> SQL> -- SQL> -- Let's test again, this time with our modified function SQL> -- SQL> -- We'll find it works SQL> -- SQL> select instring_list_test('10,20,30') from dual; INSTRING_LIST_TEST('10,20,30') ------------------------------ 27 SQL> select instring_list_test('10,20') from dual; INSTRING_LIST_TEST('10,20') --------------------------- 12 SQL> select instring_list_test('10') from dual; INSTRING_LIST_TEST('10') ------------------------ 3 SQL> select instring_list_test('10,20,30,40') from dual; INSTRING_LIST_TEST('10,20,30,40') --------------------------------- 42 SQL> select instring_list_test('10,20,30,40,50,60') from dual; INSTRING_LIST_TEST('10,20,30,40,50,60') --------------------------------------- 60 SQL> select instring_list_test('10,20,30,40,50') from dual; INSTRING_LIST_TEST('10,20,30,40,50') ------------------------------------ 54 SQL> </span></pre> Notice that the length of the list is immaterial as the loop keeps running until the desired record separator is no longer present; we need to code one additional parse of the supplied string to extract the last value and place it in our dynamic table. We then use the CAST and TABLE functions when we query this 'table'; the result is a list of values, rather than the original string, which makes IN very happy indeed.<br /><br /> So what if you don't want to always use a comma to separate your data values? Don't panic, we can re-write the function to accept a second parameter, the record separator: <pre><span style="color:#3333ff;">SQL> -- SQL> -- We'll try this another way, passing in the desired record separator value SQL> -- SQL> create or replace function instring_list_test(subtype_list varchar2, recsep varchar2) 2 return number 3 is 4 -- 5 -- The parsed value 6 -- 7 lv_subtyp number; 8 -- 9 -- The table we'll populate 10 -- 11 lv_sublist InNumTab := InNumTab(); 12 -- 13 -- A place for the result 14 -- 15 lv_ct number; 16 -- 17 -- A variable so we can 'walk' the string 18 -- 19 startpos number:=1; 20 -- 21 -- Record counter to extend the table 22 -- 23 rec number:=1; 24 25 -- 26 -- Query using the dynamic IN list 27 -- 28 cursor get_empinfo (enums InNumTab) is 29 select count(*) 30 from emp 31 where deptno in (select column_value from table(cast(enums as InNumTab))); 32 33 begin 34 -- 35 -- Extend the table so we can start populating it 36 -- 37 lv_sublist.extend(rec); 38 39 -- 40 -- 'Walk' the provided string 41 -- 42 -- We provide the desired record separator 43 -- 44 loop 45 exit when instr(subtype_list, recsep, startpos) = 0; 46 lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,recsep, 1)-1); 47 lv_sublist(rec) := lv_subtyp; 48 startpos := instr(subtype_list, recsep, startpos)+1; 49 rec := rec+1; 50 -- 51 -- After each addition we extend the table 52 -- 53 lv_sublist.extend(rec); 54 end loop; 55 56 -- 57 -- We extend the table one more time to hold our last value 58 -- 59 rec := rec+1; 60 lv_sublist.extend(rec); 61 lv_subtyp := substr(subtype_list, startpos); 62 lv_sublist(rec) := lv_subtyp; 63 64 -- 65 -- Get the count 66 -- 67 open get_empinfo(lv_sublist); 68 fetch get_empinfo into lv_ct; 69 close get_empinfo; 70 71 -- 72 -- Return the value to the caller 73 -- 74 return lv_ct; 75 end; 76 / Function created. SQL> SQL> show errors No errors. SQL> SQL> -- SQL> -- Let's test again, this time with our modified function SQL> -- SQL> -- We'll find it works SQL> -- SQL> select instring_list_test('10,20,30',',') from dual; INSTRING_LIST_TEST('10,20,30',',') ---------------------------------- 27 SQL> select instring_list_test('10;20',';') from dual; INSTRING_LIST_TEST('10;20',';') ------------------------------- 12 SQL> select instring_list_test('10',',') from dual; INSTRING_LIST_TEST('10',',') ---------------------------- 3 SQL> select instring_list_test('10:20:30:40',':') from dual; INSTRING_LIST_TEST('10:20:30:40',':') ------------------------------------- 42 SQL> select instring_list_test('10-20-30-40-50-60','-') from dual; INSTRING_LIST_TEST('10-20-30-40-50-60','-') ------------------------------------------- 60 SQL> select instring_list_test('10,20,30,40,50',',') from dual; INSTRING_LIST_TEST('10,20,30,40,50',',') ---------------------------------------- 54 SQL></span></pre> And it works like a charm, returning correct values for the counts requested. Is that cool, or what?<br /><br /> In Oracle 10g and later releases the MEMBER OF operator can be used in place of the table function used in the prior two examples: <pre><span style="color:#3333ff;">SQL> SQL> -- SQL> -- We'll try this yet another way SQL> -- SQL> create or replace function instring_list_test(subtype_list varchar2, recsep varchar2) 2 return number 3 is 4 -- 5 -- The parsed value 6 -- 7 lv_subtyp number; 8 -- 9 -- The table we'll populate 10 -- 11 lv_sublist InNumTab := InNumTab(); 12 -- 13 -- A place for the result 14 -- 15 lv_ct number; 16 -- 17 -- A variable so we can 'walk' the string 18 -- 19 startpos number:=1; 20 -- 21 -- Record counter to extend the table 22 -- 23 rec number:=1; 24 25 -- 26 -- Query using the dynamic IN list 27 -- 28 cursor get_empinfo (enums InNumTab) is 29 select count(*) 30 from emp 31 where deptno <b>member of</b> enums; 32 33 begin 34 -- 35 -- Extend the table so we can start populating it 36 -- 37 lv_sublist.extend(rec); 38 39 -- 40 -- 'Walk' the provided string 41 -- 42 -- We expect the value separator to be a comma 43 -- 44 loop 45 exit when instr(subtype_list, recsep, startpos) = 0; 46 lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,recsep, 1)-1); 47 lv_sublist(rec) := lv_subtyp; 48 startpos := instr(subtype_list, recsep, startpos)+1; 49 rec := rec+1; 50 -- 51 -- After each addition we extend the table 52 -- 53 lv_sublist.extend(rec); 54 end loop; 55 56 -- 57 -- We extend the table one more time to hold our last value 58 -- 59 rec := rec+1; 60 lv_sublist.extend(rec); 61 lv_subtyp := substr(subtype_list, startpos); 62 lv_sublist(rec) := lv_subtyp; 63 64 -- 65 -- Get the count 66 -- 67 open get_empinfo(lv_sublist); 68 fetch get_empinfo into lv_ct; 69 close get_empinfo; 70 71 -- 72 -- Return the value to the caller 73 -- 74 return lv_ct; 75 end; 76 / Function created. SQL> SQL> show errors No errors. SQL> SQL> -- SQL> -- Let's test again, this time with our modified function SQL> -- SQL> -- We'll find it works SQL> -- SQL> select instring_list_test('10,20,30',',') from dual; INSTRING_LIST_TEST('10,20,30',',') ---------------------------------- 27 SQL> select instring_list_test('10;20',';') from dual; INSTRING_LIST_TEST('10;20',';') ------------------------------- 12 SQL> select instring_list_test('10',',') from dual; INSTRING_LIST_TEST('10',',') ---------------------------- 3 SQL> select instring_list_test('10:20:30:40',':') from dual; INSTRING_LIST_TEST('10:20:30:40',':') ------------------------------------- 42 SQL> select instring_list_test('10-20-30-40-50-60','-') from dual; INSTRING_LIST_TEST('10-20-30-40-50-60','-') ------------------------------------------- 60 SQL> select instring_list_test('10,20,30,40,50',',') from dual; INSTRING_LIST_TEST('10,20,30,40,50',',') ---------------------------------------- 54 SQL></span></pre> The problem, though simple to state, isn't quite as simple to solve, as proven above. That's because Oracle is a database, it isn't your brain, so it can't draw on prior experience and know that '10,20,30' is glorified shorthand for 10,20,30. And because it's software (really, it is) running on a computer, it does what you tell it to do, whether or not those instructions provide the output you'd intended. Since it can't think like you do you'll have to start 'thinking' like it does, and that may throw a wrench into your logical picture of the situation. Knowing what to do with that wrench is half of the battle.<br /><br /> Passing dynamic lists to procedures and functions is possible, it just isn't as easy as you'd like to think, especially the first time through. But, knowing how to navigate such a situation makes life a bit easier in the IT realm, which may make you pretty nifty.<br /><br /> Of course such knowledge is no substitute for the ultimate cool of driving a Lamborghini to the dollar store ...<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-2207342584145182765?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com1tag:blogger.com,1999:blog-28935478.post-9462664456880740132008-09-18T17:35:00.019+01:002008-10-28T15:25:38.304Z2008-10-28T15:25:38.304ZSometimes, despite the best of intentions, things don't go exactly as planned. And that's really annoying when you're writing database-centric applications and the presumptions you made regarding the data turn out to be ... wrong. One possible undesired outcome revolves around returning more rows than initially expected. And Oracle has a couple of ways to tell us that we need to re-think how our code processes that data.<br /><br /> Let's build a couple of tables and use them to illustrate these points: <pre><span style="color:#3333ff;">SQL> create table lotsa_data( 2 data_id number, 3 data_set number, 4 data_val varchar2(40), 5 proc_dt date 6 ); Table created. SQL> SQL> create table ref_data( 2 data_id number, 3 data_set number 4 ); Table created. SQL> </span></pre> So, let's now load those tables with data: <pre><span style="color:#3333ff;">SQL> begin 2 for i in 1..10000 loop 3 insert into lotsa_data 4 values(i, mod(i, 17), 'Test data statement '||i, sysdate); 5 insert into ref_data 6 values(i, mod(i,17)); 7 end loop; 8 9 commit; 10 11 end; 12 / PL/SQL procedure successfully completed. SQL> </span></pre> Now comes the fun part: let's use a PL/SQL block to update some of the records in the LOTSA_DATA table. We'll use the RETURNING INTO clause to attempt to retrieve values from the modified records: <pre><span style="color:#3333ff;">SQL> declare 2 dataid number; 3 dataset number; 4 dval varchar2(40); 5 begin 6 update lotsa_data 7 set data_id = data_id + 10 8 where data_set = 16 9 returning data_id, data_set, data_val into dataid, dataset, dval; 10 end; 11 / declare * <span style="color:#990000;">ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 6 </span> SQL> </span></pre> It appears we've updated far more than one row, and Oracle wasn't exactly happy about that. We expected to modify one row of data, and we updated a considerably greater number than that, so the variables which were intended to contain the returned values couldn't process that request. Oracle throws the ORA-01422 error to indicate we'd overflow the placeholders and, well, it won't allow that to happen. Can we fix this so it will work? Certainly; we'll use a collection and BULK COLLECT instead: <pre><span style="color:#3333ff;">SQL> declare 2 <span style="color:#3399ff;">type ldat_tab_typ is table of lotsa_data%rowtype index by binary_integer;</span> 3 4 d_tab ldat_tab_typ; 5 begin 6 update lotsa_data 7 set data_id = data_id + 10 8 where data_set = 16 9 returning data_id, data_set, data_val, proc_dt <span style="color:#3399ff;">bulk collect</span> into d_tab; 10 11 for i in 1..d_tab.count loop 12 dbms_output.put_line(d_tab(i).data_id||' '||d_tab(i).data_set||' '||d_tab(i).data_val); 13 end loop; 14 end; 15 / 26 16 Test data statement 16 43 16 Test data statement 33 60 16 Test data statement 50 77 16 Test data statement 67 94 16 Test data statement 84 111 16 Test data statement 101 128 16 Test data statement 118 145 16 Test data statement 135 162 16 Test data statement 152 179 16 Test data statement 169 349 16 Test data statement 339 366 16 Test data statement 356 383 16 Test data statement 373 400 16 Test data statement 390 417 16 Test data statement 407 434 16 Test data statement 424 451 16 Test data statement 441 468 16 Test data statement 458 485 16 Test data statement 475 502 16 Test data statement 492 519 16 Test data statement 509 536 16 Test data statement 526 553 16 Test data statement 543 570 16 Test data statement 560 587 16 Test data statement 577 604 16 Test data statement 594 621 16 Test data statement 611 638 16 Test data statement 628 655 16 Test data statement 645 672 16 Test data statement 662 689 16 Test data statement 679 706 16 Test data statement 696 723 16 Test data statement 713 740 16 Test data statement 730 757 16 Test data statement 747 [... lots more data here ...] 7591 16 Test data statement 7581 7608 16 Test data statement 7598 7625 16 Test data statement 7615 7642 16 Test data statement 7632 [... and more here ...] 9580 16 Test data statement 9570 9597 16 Test data statement 9587 9614 16 Test data statement 9604 9631 16 Test data statement 9621 9648 16 Test data statement 9638 9665 16 Test data statement 9655 9682 16 Test data statement 9672 9699 16 Test data statement 9689 9716 16 Test data statement 9706 9733 16 Test data statement 9723 9750 16 Test data statement 9740 9767 16 Test data statement 9757 9784 16 Test data statement 9774 9818 16 Test data statement 9808 9835 16 Test data statement 9825 9852 16 Test data statement 9842 9869 16 Test data statement 9859 9886 16 Test data statement 9876 9903 16 Test data statement 9893 9920 16 Test data statement 9910 9937 16 Test data statement 9927 9954 16 Test data statement 9944 9971 16 Test data statement 9961 9988 16 Test data statement 9978 10005 16 Test data statement 9995 PL/SQL procedure successfully completed. SQL> </span></pre> Voila!! No error generated, and the update succeeded. What a comforting thought. <span style="color:#990000;">(Note that not all of the result set has been included, as it was a LONG list.)</span><br /><br /> <span style="color:#3333ff;">(If this error is generated by internal code accessing data dictionary views [say, when using the exp or imp utilities] it's likely that one or more views have been corrupted and need to be rebuilt. The easist way to do that is to:<br /><br /> Shutdown the database<br /> <em><span style="color:#990000;">Take a cold backup</span></em><br /> Start it in restricted mode<br /> Run $ORACLE_HOME/rdbms/admin/catalog.sql to rebuild the data dictionary<br /><br /> <em><span style="color:#990000;">Do not proceed until you have a good cold backup of the database in its current state, as something could go horribly wrong with the catalog rebuild requiring a restore and a call to Oracle support.</span></em><br /><br /> This isn't likely to occur, but stranger things have happened. If you're not comfortable with this then contact Oracle support and have them assist you in resolving the issue.)</span><br /><br /> There are occasions when Oracle errors checking for extra rows in an exact fetch and returns an ORA-01423 message. This error is the 'tip of the iceberg', as other errors which caused/contribute to this are reported. After receiving one of these errors it's necessary to check the entire error stack to reveal the underlying problem. <br /><br /> Of course an ORA-01422 isn't the only error Oracle can generate when there are too many rows: <pre><span style="color:#3333ff;">SQL> select data_id, data_set, data_val, proc_dt 2 from lotsa_data 3 where data_id = (select data_id from ref_data where data_set = 16) 4 / where data_id = (select data_id from ref_data where data_set = 16) * <span style="color:#990000;">ERROR at line 3: ORA-01427: single-row subquery returns more than one row </span> SQL> </span></pre> This one is easier to fix, as it requires only a change from '=' to 'IN': <pre><span style="color:#3333ff;">SQL> select data_id, data_set, data_val, proc_dt 2 from lotsa_data 3 where data_id IN (select data_id from ref_data where data_set = 16) 4 / [Lots of data returned] SQL></span></pre> It's good to know the data an application can generate, but it's also good to know how to fix coding blunders and missteps should they arise because the data didn't match the initial assumptions; someone (yes, maybe even you) may make a mistake. It's no crime, we all make them. And being able to recover from them is the key to successful application design and implementation.<br /><br /> And that, in the vernacular of old, is 'fetching'.<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-946266445688074013?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-68556832476970375922008-09-16T18:54:00.004+01:002009-01-02T14:05:37.681Z2009-01-02T14:05:37.681ZA while back I wrote on the causes and solutions for apparently missing tables and views, but I didn't address the problem of 'missing' packages/procedures/functions. Silly me. Let's correct that.<br /><br /> Oracle newsgroups and blogs are filled with suggestions, tips, techniques and scripts intended to help the DBA with his or her chores, and many of these offerings utilize 'standard' packages and procedures installed by Oracle at database creation. Of course some of these packages/procedures/functions aren't meant for the common, every-day user to utilize, and the privileges on those objects are limited to specific types of accounts. Some of these are even restricted to use by SYS as SYSDBA and no one else. There are many, though, that are suitable for any user to execute, provided that user has the requisite privileges. And, unfortunately, such privileges may not have been granted to the user desiring access; calling or attempting to describe such procedures/packages/functions then results in the following undesired output: <pre><span style="color:#3333ff;">SQL> desc dbms_lock ERROR: ORA-04043: object dbms_lock does not exist SQL></span></pre> And, from a PL/SQL block you can get the following unnerving message: <pre><span style="color:#3333ff;">PLS-00201: identifier 'dbms_lock' must be declared </span></pre> But, hey, you KNOW it's there, because all of these wonderful scripts can't be wrong. And they're not; the user account in use simply has not been granted execute privilege on that package. And the same rules apply here that I listed in my prior post:<br /><br /> * the user has no execute privilege on the package/procedure/function<br /> * a synonym is missing and the user is attempting to access the object by name<br /><br /> How to fix this glaring omission? Either grant execute on the desired object to the requesting user, or create a synonym to allow access by name. How can you tell which is required? If this: <pre><span style="color:#3333ff;">SQL> desc dbms_lock ERROR: ORA-04043: object dbms_lock does not exist SQL></span></pre> and this: <pre><span style="color:#3333ff;">SQL> desc sys.dbms_lock ERROR: ORA-04043: object sys.dbms_lock does not exist SQL></span></pre> are the end results then the user has no execute privilege on the package/procedure/function. If, however, access by name fails: <pre><span style="color:#3333ff;">SQL> desc dbms_lock ERROR: ORA-04043: object dbms_lock does not exist SQL></span></pre> but access by owner.name succeeds: <pre><span style="color:#3333ff;">SQL> desc sys.dbms_lock PROCEDURE ALLOCATE_UNIQUE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LOCKNAME VARCHAR2 IN LOCKHANDLE VARCHAR2 OUT EXPIRATION_SECS NUMBER(38) IN DEFAULT FUNCTION CONVERT RETURNS NUMBER(38) Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- ID NUMBER(38) IN LOCKMODE NUMBER(38) IN TIMEOUT NUMBER IN DEFAULT FUNCTION CONVERT RETURNS NUMBER(38) Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LOCKHANDLE VARCHAR2 IN LOCKMODE NUMBER(38) IN TIMEOUT NUMBER IN DEFAULT FUNCTION RELEASE RETURNS NUMBER(38) Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- ID NUMBER(38) IN FUNCTION RELEASE RETURNS NUMBER(38) Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LOCKHANDLE VARCHAR2 IN FUNCTION REQUEST RETURNS NUMBER(38) Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- ID NUMBER(38) IN LOCKMODE NUMBER(38) IN DEFAULT TIMEOUT NUMBER(38) IN DEFAULT RELEASE_ON_COMMIT BOOLEAN IN DEFAULT FUNCTION REQUEST RETURNS NUMBER(38) Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LOCKHANDLE VARCHAR2 IN LOCKMODE NUMBER(38) IN DEFAULT TIMEOUT NUMBER(38) IN DEFAULT RELEASE_ON_COMMIT BOOLEAN IN DEFAULT PROCEDURE SLEEP Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SECONDS NUMBER IN SQL></span></pre> then the issue is a missing synonym. Knowing the corrective action required (and, of course, taking that action) will allow the user to access the desired package/procedure/function.<br /><br /> Knowing what packages/procedures/functions you CAN access is information which is fairly easy to obtain: <pre><span style="color:#3333ff;">select owner, object_name from all_objects where object_type in ('PACKAGE','FUNCTION','PROCEDURE'); </span></pre> You'll get a list (possibly a LONG list) of packages, procedures and functions (and the assiciated owners) which you're allowed to execute: <pre><span style="color:#3333ff;">OWNER OBJECT_NAME ------------------------------ ------------------------------ SYS STANDARD SYS DBMS_STANDARD SYS DBMS_REGISTRY SYS DBMS_REGISTRY_SERVER SYS XML_SCHEMA_NAME_PRESENT SYS UTL_RAW SYS PLITBLM SYS SYS_STUB_FOR_PURITY_ANALYSIS SYS PIDL SYS DIANA SYS DIUTIL OWNER OBJECT_NAME ------------------------------ ------------------------------ SYS SUBPTXT2 SYS SUBPTXT SYS DBMS_PICKLER SYS DBMS_JAVA_TEST SYS DBMS_SPACE_ADMIN SYS DBMS_LOB SYS UTL_SYS_COMPRESS SYS UTL_TCP SYS UTL_HTTP SYS DBMS_TRANSACTION_INTERNAL_SYS SYS DBMS_SQL OWNER OBJECT_NAME ------------------------------ ------------------------------ SYS DBMS_SYS_SQL SYS DBMS_OUTPUT SYS DBMS_LOGSTDBY SYS DBMS_SESSION SYS DBMS_LOCK SYS UTL_FILE SYS DBMS_TYPES SYS GETTVOID SYS XMLSEQUENCEFROMXMLTYPE SYS XQSEQUENCEFROMXMLTYPE SYS XMLSEQUENCEFROMREFCURSOR ... </span></pre> If the package/procedure/function is in that list, but you still can't access it by name you're simply missing a synonym. And, if it's not in that list you have no access to that object so you'll need to discuss that issue with your DBA.<br /><br /> I'll state again in this post that <span style="color:#3333ff;">not all Oracle users are destined to access or use all of the installed packages/procedures/functions Oracle supplies</span>. There may be very good reasons in your organization for not having access to a specific package, procedure or function, so don't be surprised if your request is met with a glorious<br /><br /> "Nope, sorry, can't do that."<br /><br /> Security is the watchword of late, and some organizations may frown upon just any user having privilege to execute certain code, because granting such access may open security holes in the database. <a href="http://www.petefinnigan.com/">Pete Finnigan</a> has an excellent website listing the security issues with Oracle releases; it's worth the time to peruse his site to get a feel for what could disrupt an Oracle installation and give you a 'heads up' on why, possibly, you can't use a certain package or procedure.<br /><br /> It never hurts to ask. Just don't be surprised if the answer is "No" because there is probably a very good reason for that response.<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-6855683247697037592?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com1tag:blogger.com,1999:blog-28935478.post-16868530758819038172008-09-03T15:23:00.008+01:002009-07-03T17:52:55.026+01:002009-07-03T17:52:55.026+01:00Someone asked<br /><br /> "how oracle is keeping track of last few ora- errors"<br /><br /> and the answer is a fairly simple "it isn't". (Granted, Oracle <b>does</b> record the severe errors in the database alert log, but those aren't all of the errors that can be generated by any stretch of the imagination.) It <b>can</b>, though, if you ask it to.<br /><br /> Since Oracle 8.1.6 system event triggers have been available, and one of those triggers is an AFTER SERVERERROR trigger, which can log most errors you encounter. Setting up your database to do that is a fairly simple task (the following works in 10.2 and later releases): <pre><span style="color:#3333ff;">SQL> CREATE TABLE stats$error_log ( 2 err_dt TIMESTAMP, 3 db_user VARCHAR2(30), 4 msg_stack VARCHAR2(2000), 5 sqltxt VARCHAR2(1000)) 6 tablespace tools; Table created. SQL> SQL> CREATE OR REPLACE TRIGGER log_server_errors 2 AFTER SERVERERROR 3 ON DATABASE 4 DECLARE 5 v_sqltext VARCHAR2(1000); 6 nl_sqltext ora_name_list_t; 7 BEGIN 8 -- Capture entire error text 9 FOR i IN 1 .. ora_sql_txt(nl_sqltext) LOOP 10 v_sqltext := v_sqltext || nl_sqltext(i); 11 END LOOP; 12 13 INSERT INTO STATS$ERROR_LOG 14 (err_dt, db_user, msg_stack, sqltxt) 15 VALUES 16 (systimestamp, 17 sys.login_user, 18 dbms_utility.format_error_stack, v_sqltext); 19 20 END log_server_errors; 21 / Trigger created. SQL></span></pre> Now you're set to log most errors in the STATS$ERROR_LOG table: <pre><span style="color:#3333ff;">SQL> select a.object_name 2 from dba_objects a, dba_objects b, dba_objects c 3 order by 1; from dba_objects a, dba_objects b, dba_objects c * ERROR at line 2: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP SQL></span></pre> Let's query that table and see what it reports: <pre><span style="color:#3333ff;">SQL> select * from stats$error_log; ERR_DT --------------------------------------------------------------------------- DB_USER ------------------------------ MSG_STACK -------------------------------------------------------------------------------- SQLTXT -------------------------------------------------------------------------------- 01-SEP-08 09.35.15.159854 AM ****** ORA-01652: unable to extend temp segment by 128 in tablespace TEMP select a.object_name from dba_objects a, dba_objects b, dba_objects c order by 1 SQL></span></pre> We've preserved the error stack and the 'offending' SQL along with the executing USER (obscured for security reasons) and the timestamp when the error was logged (which is approximately when the error occurred). Nice.<br /><br /> How many errors can you preserve in this log table? It all depends upon how much disk space you have to use. It should go without saying (but, hey, I'll say it anyway) that this table needs to be monitored so it doesn't become unwieldy; such data needs to be examined on a regular and fairly frequent basis so that problem areas can be addressed quickly and hopefully resolved.<br /><br /> Which errors won't you capture? It appears to be a short list: <pre><span style="color:#3333ff;">ORA-01403: no data found ORA-01422: exact fetch returns more than requested number of rows ORA-01423: error encountered while checking for extra rows in exact fetch ORA-01034: ORACLE not available ORA-04030: out of process memory when trying to allocate string bytes (string, string) </span></pre> Pretty much any other error should be logged in the table for examination at a later time, which is convenient for the DBA as sometimes he or she cannot address an error when it occurs, or the application doesn't pass such error text through to the end-user. And you, as a DBA, can't address errors of which you're not informed. <br /><br /> An AFTER SERVERERROR trigger may not be something you want to have running all of the time, but it is nice to know you can fire one up and capture problem statements and the errors they create should you need to do so. Just as a carpenter doesn't use a hammer for every task database tools like event triggers aren't the solution to every problem. But it is nice to know they're available when and if you need them, because having the right tools for the job makes the task so much easier.<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-1686853075881903817?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com1tag:blogger.com,1999:blog-28935478.post-42539122366005822242008-08-18T15:09:00.004+01:002008-12-12T14:39:20.281Z2008-12-12T14:39:20.281ZOccasionally certain objects in a database can fail to function, such as packages, procedures, triggers, functions, indexes, synonyms and views. Modifying any of the underlying dependent objects those items rely upon can change the status of such objects from 'VALID' to 'INVALID' or 'UNUSABLE'. Short of attempting to access such objects and failing miserably how does one discover and correct such problems? The solution is fairly simple: ask the database.<br /><br /> Oracle provides, in the *_OBJECTS views, a column named STATUS which lists the status (obviously) of the object in question. When everything is right and proper that status should be 'VALID'; of course no database is problem-free or immune to code changes so there are times when various objects may no longer be usable. Finding these objects is half of the battle; let's look at a query to do that: <pre><span style="color:#3333ff;">SQL> select object_name, object_type, status 2 from user_objects 3 where status <> 'VALID' 4 order by created; OBJECT_NAME OBJECT_TYPE STATUS ----------------------------------- ------------------- ------- OWA_SYLK PACKAGE INVALID SCHEMA_ACCESS PROCEDURE INVALID CHECK_SAL FUNCTION INVALID RAISE_SAL PROCEDURE INVALID STRINGC FUNCTION INVALID JOB_PKG PACKAGE INVALID DATES_PKG PACKAGE INVALID EMP_VW VIEW INVALID VIEW_EMP_DEPT VIEW INVALID GET_EMPNAME FUNCTION INVALID GET_SAL PROCEDURE INVALID PROJECT SYNONYM INVALID 12 rows selected. SQL></span></pre> Notice the objects are ordered by their creation date; this allows the query to be used to write a dynamic script to recompile the objects and avoid dependency invalidations in the process: <pre><span style="color:#3333ff;">SQL> select 'alter '||object_type||' '||object_name||' compile;' 2 from user_objects 3 where status <> 'VALID' 4 and object_type in ('PACKAGE','PROCEDURE','FUNCTION','TYPE','VIEW', 'TRIGGER','SYNONYM') 5 union 6 select 'alter '||substr(object_type, 1, instr(object_type, ' BODY') -1)||' '||object_name||' compile body;' 7 from user_objects 8 where status <> 'VALID' 9 and instr(object_type, ' BODY') > 0 10 / 'ALTER'||OBJECT_TYPE||''||OBJECT_NAME||'COMPILE;' -------------------------------------------------------------------------------- alter FUNCTION CHECK_SAL compile; alter FUNCTION GET_EMPNAME compile; alter FUNCTION STRINGC compile; alter PACKAGE DATES_PKG compile; alter PACKAGE JOB_PKG compile; alter PACKAGE OWA_SYLK compile; alter PROCEDURE GET_SAL compile; alter PROCEDURE RAISE_SAL compile; alter PROCEDURE SCHEMA_ACCESS compile; alter SYNONYM PrOJECT compile; alter VIEW EMP_VW compile; alter VIEW VIEW_EMP_DEPT compile; 12 rows selected. SQL></span></pre> But, wait, there's a neat little script that Oracle has provided to do the same job: utlrp.sql, located in the $ORACLE_HOME/rdbms/admin directory. It calls the UTL_RECOMP package and recompiles all invalid objects in the database (or tries to). It also reports how many of the recompiled objects generated errors and, if this number is larger than you might expect (yes, you may have invalid objects which cannot be 'fixed') then you run the first query listed and see which objects are affected. You can then use the second query to generate a dynamic list, modify that script to include a 'show errors' command after each compile statement and discover why each remaining invalid object would not successfully compile: <pre><span style="color:#3333ff;">SQL> select 'alter '||object_type||' '||object_name||' compile;' 2 from user_objects 3 where status <> 'VALID' 4 and object_type in ('PACKAGE','PROCEDURE','FUNCTION','TYPE','VIEW', 'TRIGGER','SYNONYM') 5 union 6 select 'alter '||substr(object_type, 1, instr(object_type, ' BODY') -1)||' '||object_name||' compile body;' 7 from user_objects 8 where status <> 'VALID' 9 and instr(object_type, ' BODY') > 0 10 / 'ALTER'||OBJECT_TYPE||''||OBJECT_NAME||'COMPILE;' -------------------------------------------------------------------------------- alter FUNCTION CHECK_SAL compile; alter PROCEDURE RAISE_SAL compile; SQL></span></pre> Generating a more detailed error message for each compile: <pre><span style="color:#3333ff;">SQL> alter FUNCTION CHECK_SAL compile; Warning: Function altered with compilation errors. SQL> show errors Errors for FUNCTION CHECK_SAL: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 PL/SQL: Compilation unit analysis terminated 1/30 PLS-00201: identifier 'EMPLOYEES.EMPLOYEE_ID' must be declared SQL> alter PROCEDURE RAISE_SAL compile; Warning: Procedure altered with compilation errors. SQL> show errors Errors for PROCEDURE RAISE_SAL: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 PL/SQL: Compilation unit analysis terminated 2/20 PLS-00201: identifier 'EMPLOYEES.EMPLOYEE_ID' must be declared SQL> </span></pre> So we're missing a table these objects depend upon, and until that table is replaced they will remain invalid, so we can stop trying to compile them.<br /><br /> In most cases views won't need to be recompiled as select access to invalid views automatically performs that action; of course if the situation is like that shown above nothing will make the invalid view usable.<br /><br /> Unusable indexes are treated a bit differently, as they can't be recompiled; they need to be rebuilt. Normally the database would be shutdown and then started in restricted mode to allow the rebuild to commence unhindered (rebuilding indexes really shouldn't be done when users are actively accessing the database as it consumes resources and can cause exceptional delays for other processes while the rebuild of each index is taking place). A similar query to the invalid objects SQL can find the unusable indexes: <pre><span style="color:#3333ff;">SQL> select index_name, status 2 from user_indexes 3 where status <> 'VALID'; no rows selected SQL></span></pre> Had there been any unusable indexes the following query will generate the necessary executable statements: <pre><span style="color:#3333ff;">select 'alter index '||index_name||' rebuild tablespace '||tablespace_name||';' from user_indexes where status <> 'VALID'; </span></pre> Spool that output to a file, verify it wrote correctly (the default line size may be a bit short for some resulting lines, so you need to check that each alter statement is, indeed, on a single line) then prepare to execute the script after the database is in restricted mode. Log the execution of the script so any resource-related errors can be addressed before it's run again (usually, though, one run is sufficient).<br /><br /> <span style="color:#3333ff;">Fixed views are a different story, as they're based upon memory and internal disk structures. If any of these are declared INVALID the only recommended action to be taken is to contact Oracle Support as you cannot recompile such views. It's likely that you'll be told to shutdown and startup the database, but do <em>NOT</em> proceed with that action until told to do so by, you guessed it, Oracle Support.</span><br /><br /> So, finding and correcting invalid database objects is fairly straightforward; it does require attention to detail, however, to ensure that all objects which can be successfuly recompiled/rebuilt are again in a usable state. Practice on a test database is recommended so that if and when this process is required on a production system it's been tested and re-tested and the method is properly defined and documented.<br /><br /> Of course, if it ain't broke ...<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-4253912236600582224?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-28116298984046632472008-08-15T21:42:00.003+01:002008-08-15T21:46:23.237+01:002008-08-15T21:46:23.237+01:00There have been quite a number of searches for information on many of the exu8 views present in an Oracle data dictionary. Such views belong to the export/import catalog, created with the $ORACLE_HOME/rdbms/admin/catexp.sql script. Inside this script, in the internal documentation, lies descriptions of many of these views. Let's look at a few which have made the 'Hit Parade' in Google searches: <pre><span style="color:#3333ff;">exu8poki This view contains the partitioning key columns for partitioned indexes. exu8jbq Contains job queue entries. exu8rif Contains information on referential contraints. exu8snapl Snapshot log information exu81obj This view selects all rows from sys.obj$ that are NOT secondary objects as created by Domain Indexes. Secondary objects are not normally exported because the domain index's CREATE INDEX at import time will create them. </span></pre> I won't cover any more of these views, as you can easily look in the creation script and find descriptions for almost all of the export/import views.<br /><br /> Happy reading.<br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-2811629898404663247?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-37763443663968480072008-08-07T15:06:00.029+01:002009-02-04T19:04:47.173Z2009-02-04T19:04:47.173Z"Oh, what a tangled web we weave, ..." Sir Walter Scott<br /><br /> Julian date numbers prior to AD 1 can be, well, a mess in Oracle. Take, for example, the stated date limit imposed by the database, which is January 1, 4712 BC. According to Oracle this is the start date for the Julian calendar ... but it isn't, as the start date is actually one year earlier.<br /><br /> Huh?!?!?<br /><br /> It's true, and to explain this we need to delve into several calendars and conventions to see exactly how this came to be. We need to define a term, first, and that term is <span style="color:#3333ff;">proleptic</span>. This means to extend a calendar past the date of its inception to extrapolate dates (dates that didn't exist in that calendar because it didn't exist yet or it wasn't stable). The year the Julian calendar became reliable is 4 AD, which is when the leap years stabilized [old Julius Caesar may have been a good ruler, but his math skills were apparently lacking]. Any 'Julian' date prior to 4 AD is considered a proleptic Julian date. And more on that later.<br /><br /> Julian Date Numbers (or Julian dates, as they are commonly considered) are based upon this proleptic calendar as they start at an epoch defined as January 1, 4713 BC, which is Julian Day Number 0 (since 4713 BC is WAY earlier than 4 AD [our stable starting point for the Julian calendar] this is in the proleptic Julian calendar and is, in fact, the considered 'starting' date for that calendar chosen because all three cycles of the Julian calendar [indiction {15-year cycle}, Metonic {roughly, a lunar cycle} and Solar] coincided for the first time). And that's just fine, unless you run afoul of a couple of 'gotchas'. Those 'gotchas' are:<br /><br /> <span style="color:#3333ff;">The Year Zero Rule</span><br /><br /> With the common BC/AD convention there is no Year 0, as 1 BC directly preceeds 1 AD. Not so with an astronomical numbering convention, which designates years with a +/- scheme and includes a year 0 [this convention was first used by French astronomer Jacques Cassini in the mid-18th century]; the 'AD' years remain as-is, but the 'BC' end of the scale changes thusly:<br /><br /> 2 BC is notated as -1<br /> 1 BC is notated as 0<br /><br /> So NOW there IS a year 0, but, well, Oracle doesn't quite know what to do with it. And this is not the only complication, as the inception of the Gregorian calendar resulted in a loss of at least 10 days to correct for an excess of accumulated days due to the original, erroneous leap year rule [and we can't thank Pope Gregory XIII enough for that]. Which 10+ days depends upon the date of adoption: <br /><br /> If the adoption date was October 15, 1582<br /> ----------------&gt; then October 5 through 14 vanish.<br /> If the adoption date was September 14, 1752<br /> ----------------&gt; then September 3 through 13 disappear.<br /><br /> And there's an option of no cutover/adoption date at all, in which the dates are considered either all Julian (going back to our proleptic Julian calendar) or all Gregorian (which establishes a proleptic Gregorian calendar) but preserves the 'missing' dates for eternity. [There are also other adoption dates for the Gregorian calendar throughout Europe; the two listed are the most 'popular'.] Oracle chose to use October, 1582 as its cutover date. UNIX, on the other hand, uses the September, 1752 cutover.<br /><br /> So, why is Oracle unable to handle the year 0? It appears that somewhere in the development cycle a programmer decided to use the astronomical convention to calculate the Julian Day Numbers but use BC/AD notation without a correct 'translation'. Thus the astronomical year -4712 (4712 years before year 0) becomes, in Oracle, 4712 BC instead of its actual proleptic Julian year of 4713 BC. So notation of all of the BC years in Oracle is off by 1, due to the Year 0 issue. The calculations, though, are correct, for any date on or after March 1, 4713 BC (-4712/03/01 using the astronomical notation). Why March 1, 4713? Oracle considers January 1, 4713 as Julian Day Number 1 but it's really Julian Day Number 0 (as mentioned previously). But, the calculation of the Julian Day Number for that date returns ... 1: <pre><span style="color:#3333ff;">SQL> with jdna as ( 2 select floor((14 - 1)/12) a 3 from dual 4 ), 5 jdny as ( 6 select -4712 + 4800 - a y 7 from dual, jdna 8 ), 9 jdnm as ( 10 select 1 + (12*a) - 3 m 11 from dual, jdna 12 ) 13 select 1 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian 14 from jdna, jdnm, jdny, dual; JULIAN ---------- 1.15 SQL></span></pre> [Maybe Julian Day 0 occurs at <b>exactly</b> noon on January 1, 4713, meaning 12:01 PM on that same day would start Julian Day 1. Or not.] And, since Year 4713 would be a leap year (don't ask me why, it just is) it would have a February 29: <pre><span style="color:#3333ff;">SQL> with jdna as ( 2 select floor((14 - 2)/12) a 3 from dual 4 ), 5 jdny as ( 6 select -4712 + 4800 - a y 7 from dual, jdna 8 ), 9 jdnm as ( 10 select 2 + (12*a) - 3 m 11 from dual, jdna 12 ) 13 select 29 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian 14 from jdna, jdnm, jdny, dual; JULIAN ---------- 59.75 SQL></span></pre> but Oracle doesn't know that date exists, so the errors cancel each other by making March 1, 4713 Julian Day Number 60, which is correct. <span style="color:#3333ff;">[Apparently Oracle is rounding the results because if we use the formula to calculate the Julian Day Number for March 1, 4713 we get: <pre><span style="color:#333fff;">SQL> with jdna as ( 2 select floor((14 - 3)/12) a 3 from dual 4 ), 5 jdny as ( 6 select -4712 + 4800 - a y 7 from dual, jdna 8 ), 9 jdnm as ( 10 select 3 + (12*a) - 3 m 11 from dual, jdna 12 ) 13 select 1 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian 14 from jdna, jdnm, jdny, dual; JULIAN ---------- 60.4 SQL></span></pre> Round that calculation and you get 60; round the calculation for February 29, 4713 and you get ... 60. This may be why Oracle refuses to accept -4712/02/29 as a valid date: <pre><span style="color:#333fff;">SQL> select to_date('-4712/02/29','syyyy/mm/dd') from dual; select to_date('-4712/02/29','syyyy/mm/dd') from dual * ERROR at line 1: ORA-01839: date not valid for month specified SQL></span></pre> (two dates cannot possess the same Julian Day Number as they are unique).]</span> Any Julian Day Number from March 1, 4713 onward is correct. To prove this let's generate the rounded, truncated and actual Julian day numbers for the date range of 01/01/4713 BC to 03/01/4713 BC and see what 'falls out': <pre><span style="color:#333fff;">SQL> with jdna as ( 2 select floor((14 - &&1)/12) a 3 from dual 4 ), 5 jdny as ( 6 select &&3 + 4800 - a y 7 from dual, jdna 8 ), 9 jdnm as ( 10 select &&1 + (12*a) - 3 m 11 from dual, jdna 12 ) 13 select &&1 mo, &&2 dy, 14 case when &&3 < 1 then abs(1 - &&3) else &&3 end yr, 15 case when &&3 < 1 then 'BC' else 'AD' end era, 16 &&2 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian, 17 round(&&2 + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0) rnd_julian, 18 trunc(&&2 + (((153*m)+2)/5) + (365*y) + (y/4) -32083) trunc_julian, 19 case when &&1 < 3 and &&3 < 1 then 20 round(&&2 + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0)-1 21 else 22 round(&&2 + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0) 23 end actual_julian 24 from jdna, jdnm, jdny, dual; MO DY YR ER JULIAN RND_JULIAN TRUNC_JULIAN ACTUAL_JULIAN ---------- ---------- ---------- -- ---------- ---------- ------------ ------------- 1 1 4713 BC 1.15 1 1 0 1 2 4713 BC 2.15 2 2 1 1 3 4713 BC 3.15 3 3 2 1 4 4713 BC 4.15 4 4 3 1 5 4713 BC 5.15 5 5 4 1 6 4713 BC 6.15 6 6 5 1 7 4713 BC 7.15 7 7 6 1 8 4713 BC 8.15 8 8 7 1 9 4713 BC 9.15 9 9 8 1 10 4713 BC 10.15 10 10 9 1 11 4713 BC 11.15 11 11 10 1 12 4713 BC 12.15 12 12 11 1 13 4713 BC 13.15 13 13 12 1 14 4713 BC 14.15 14 14 13 1 15 4713 BC 15.15 15 15 14 1 16 4713 BC 16.15 16 16 15 1 17 4713 BC 17.15 17 17 16 1 18 4713 BC 18.15 18 18 17 1 19 4713 BC 19.15 19 19 18 1 20 4713 BC 20.15 20 20 19 1 21 4713 BC 21.15 21 21 20 1 22 4713 BC 22.15 22 22 21 1 23 4713 BC 23.15 23 23 22 1 24 4713 BC 24.15 24 24 23 1 25 4713 BC 25.15 25 25 24 1 26 4713 BC 26.15 26 26 25 1 27 4713 BC 27.15 27 27 26 1 28 4713 BC 28.15 28 28 27 1 29 4713 BC 29.15 29 29 28 1 30 4713 BC 30.15 30 30 29 1 31 4713 BC 31.15 31 <span style="color:#990000;">31</span> 30 2 1 4713 BC 31.75 32 <span style="color:#990000;">31</span> 31 2 2 4713 BC 32.75 33 32 32 2 3 4713 BC 33.75 34 33 33 2 4 4713 BC 34.75 35 34 34 2 5 4713 BC 35.75 36 35 35 2 6 4713 BC 36.75 37 36 36 2 7 4713 BC 37.75 38 37 37 2 8 4713 BC 38.75 39 38 38 2 9 4713 BC 39.75 40 39 39 2 10 4713 BC 40.75 41 40 40 2 11 4713 BC 41.75 42 41 41 2 12 4713 BC 42.75 43 42 42 2 13 4713 BC 43.75 44 43 43 2 14 4713 BC 44.75 45 44 44 2 15 4713 BC 45.75 46 45 45 2 16 4713 BC 46.75 47 46 46 2 17 4713 BC 47.75 48 47 47 2 18 4713 BC 48.75 49 48 48 2 19 4713 BC 49.75 50 49 49 2 20 4713 BC 50.75 51 50 50 2 21 4713 BC 51.75 52 51 51 2 22 4713 BC 52.75 53 52 52 2 23 4713 BC 53.75 54 53 53 2 24 4713 BC 54.75 55 54 54 2 25 4713 BC 55.75 56 55 55 2 26 4713 BC 56.75 57 56 56 2 27 4713 BC 57.75 58 57 57 2 28 4713 BC 58.75 59 58 58 2 29 4713 BC 59.75 <span style="color:#990000;">60</span> 59 59 3 1 4713 BC 60.4 <span style="color:#990000;">60</span> 60 60 SQL></span></pre> Notice if the calculation is rounded then Februrary 29, 4713 BC has the same Julian Day Number as March 1, 4713 BC (as noted earlier); if the calculation is truncated then February 1, 4713 BC has the same Julian Day Number as January 31, 4713 BC and, well, we can't have a calendar where February 1 doesn't exist thus the reason [I suspect] that Oracle chose to round the value and then discard February 29, 4713 BC. But, had Oracle performed the numeric 'gyrations' illustrated in the query above we could have had proper Julian Day numbers and all of the actual proleptic dates of 4713 BC would exist.<br /><br /> <em><span style="color:#990000;">(Notice the query uses the "&amp;&amp;" syntax for positional variables; this allows the variable to be set once, when the script is called, and passes the set value through the script to any other places where it is used. Using a single "&amp;" would cause SQL*Plus to request the value for each occurrence in the statement. The up side to this is that variables need only be supplied values once during a run; the down side is the values are persistent between runs and either need to be submitted again on the command line [for a script] or need an explicit 'undefine' statement to clear them [as in 'undefine 1', for example, to 'clear' the value for &amp;&amp;1]. The double &amp; can also be used on named variables, such as &amp;&amp;my_tbl; the undefine syntax works on these, as well, so that 'undefine my_tbl' will clear any set value supplied so subsequent runs will request a new value.)</span></em><br /><br /> And this gets better. Oracle may not recognize 1 BC as year 0 but the calculations do. Ask Oracle for the number of days difference between December 31, 1 BC and January 1, 1 AD: <pre><span style="color:#333fff;">SQL> select to_date('01/01/0001', 'mm/dd/yyyy') - to_date('12/31/0001 BC', 'mm/dd/yyyy bc') "This Should Be 1" 2 from dual; This Should Be 1 ---------------- <span style="color:#990000;">367</span> SQL></span></pre> Now let's ask Oracle to add 1 to the date December 31, 1 BC: <pre><span style="color:#333fff;">SQL> select to_date('31-DEC-0001 BC', 'DD-MON-YYYY AD')+1 from dual 2 / TO_DATE('31 ----------- 01-JAN-0000 SQL></span></pre> So Year 0 exists (at least according to the JDN formula and an interesting date addition illustrated above) and it's a leap year. Obviously Oracle takes the 1 BC value and simply tacks on a minus sign, making the year -1 in the astronomical convention -- which is wrong as discussed previously. Were Oracle to do this correctly 1 BC would become Year 0 and the returned value for the above query would be 1: <pre><span style="color:#333fff;">SQL> with jdna as ( 2 select floor((14 - &&1)/12) a 3 from dual 4 ), 5 jdny as ( 6 select &&3 + 4800 - a y 7 from dual, jdna 8 ), 9 jdnm as ( 10 select &&1 + (12*a) - 3 m 11 from dual, jdna 12 ) 13 select &&1 mo, &&2 dy, 14 case when &&3 < 1 then abs(1 - &&3) else &&3 end yr, 15 case when &&3 < 1 then 'BC' else 'AD' end era, 16 &&2 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian, 17 round(&&2 + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0) rnd_julian, 18 trunc(&&2 + (((153*m)+2)/5) + (365*y) + (y/4) -32083) trunc_julian 19 from jdna, jdnm, jdny, dual; MO DY YR ER JULIAN RND_JULIAN TRUNC_JULIAN ACTUAL_JULIAN ---------- ---------- ---------- -- ---------- ---------- ------------ ------------- 12 31 1 BC 1721423.8 1721424 1721423 1721424 SQL> SQL> with jdna as ( 2 select floor((14 - &&1)/12) a 3 from dual 4 ), 5 jdny as ( 6 select &&3 + 4800 - a y 7 from dual, jdna 8 ), 9 jdnm as ( 10 select &&1 + (12*a) - 3 m 11 from dual, jdna 12 ) 13 select &&1 mo, &&2 dy, 14 case when &&3 < 1 then abs(1 - &&3) else &&3 end yr, 15 case when &&3 < 1 then 'BC' else 'AD' end era, 16 &&2 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian, 17 round(&&2 + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0) rnd_julian, 18 trunc(&&2 + (((153*m)+2)/5) + (365*y) + (y/4) -32083) trunc_julian 19 from jdna, jdnm, jdny, dual; MO DY YR ER JULIAN RND_JULIAN TRUNC_JULIAN ACTUAL_JULIAN ---------- ---------- ---------- -- ---------- ---------- ------------ ------------- 1 1 1 AD 1721424.4 1721424 1721424 1721424 SQL> SQL> select &after_jc - &before_jc from dual; 1721424-1721423 --------------- <span style="color:#990000;"> 1</span> SQL></span></pre> <span style="color:#990000;">(Oracle recognizes this as a bug <span style="color:#3333ff;">(106242)</span> but, due to possible backwards compatibility issues, hasn't fixed it.)</span><br /><br /> Can you fix the display issue in Oracle? Well, sort of -- you'll need to generate the date string, parse it, then, if it's a BC date add one more to the year: <pre><span style="color:#3333ff;">SQL> declare 2 yr number; 3 bc varchar2(2); 4 mo varchar2(10); 5 dy varchar2(2); 6 7 begin 8 select to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'AD'), 9 to_number(to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'YYYY')), 10 to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'MONTH'), 11 to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'DD') 12 into bc, yr, mo, dy 13 from dual; 14 15 if bc = 'BC' then 16 yr := yr + 1; 17 end if; 18 19 dbms_output.put_line('The date submitted is '||mo||' '||dy||', '||yr||' '||bc); 20 21 end; 22 / The date submitted is JANUARY 01, 4713 BC PL/SQL procedure successfully completed. SQL></span></pre> It may be easier to simply remember the BC date notation is one year off.<br /><br /> But, hey, the Julian Date Numbers are correct ... if you don't count January and February of 4713 BC ... so is Oracle wrong or is the formula wrong? It appears a bit of both, really, as the formula (as proven) returns the wrong values for dates from the epoch through February 29, 4713 BC, and Oracle doesn't 'translate' the astronomical numbering convention correctly, associating -1 with 1 BC. When it rains, it pours.<br /><br /> Now, where did I put that leap year?<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-3776344366396848007?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com1tag:blogger.com,1999:blog-28935478.post-15516865530871071042008-08-05T17:44:00.002+01:002008-08-05T17:50:10.557+01:002008-08-05T17:50:10.557+01:00It's possible that a tablespace may have but one table or index located therein. Usually it doesn't matter how may tables/indexes are in a tablespace, but somehow you ended up with a vast expanse of storage with only one segment in residence. How on earth can you report which tablespace is the hiding place for this segment?<br /><br /> Oracle provides two views to report where segments are located: <pre><span style="color:#3333ff;">DBA_SEGMENTS USER_SEGMENTS </span></pre> Only DBA_SEGMENTS provides the necessary tools to determine which tablespace, if any, houses only one segment, and if you don't have access to that view you can't run the query. The query itself is pretty simple: <pre><span style="color:#3333ff;">select tablespace_name, count(*) from dba_segments group by tablespace_name having count(*) = 1; </span></pre> Such a query will return any and all tablespaces containing one, and only one, segment. You can't modify the query to include the owner, though, as that may return any number of tablespaces having more than one segment, but only one segment owned by a particular user (and this is why you can't use the USER_SEGMENTS view, as that only reports objects owned by the currently logged on user): <pre><span style="color:#3333ff;">SQL> select tablespace_name, count(*) 2 from dba_segments 3 group by tablespace_name 4 having count(*) = 1 5 / no rows selected SQL> select owner, tablespace_name, count(*) 2 from dba_segments 3 group by owner, tablespace_name 4 having count(*) = 1 5 / OWNER TABLESPACE_NAME COUNT(*) ------------------------------ ------------------------------ ---------- SYS TOOLS 1 SQL></span></pre> Note no tablespaces in this database have only one segment, however the TOOLS tablespace does contain ONE segment owned by SYS. So, as shown, the first query is the only query which returns the desired information.<br /><br /> Now, get going and find those stragglers!<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-1551686553087107104?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-68595526066808987972008-07-30T14:56:00.003+01:002008-07-30T15:25:48.884+01:002008-07-30T15:25:48.884+01:00Materialized views and their associated snapshot logs are interesting animals with respect to the exp utility. Attempting to export a snapshot log by itself only provides the snapshot log table definition, as does attempting to do so with a table-level export. But that's not all you need with a materialized view/snapshot to enable a fast refresh scenario, you need the data IN that snapshot log as well. So, how do you go about successfully exporting that data?<br /><br /> There are two modes of a traditional, original exp which can, and will, export snapshot log data: full export mode and user-level export mode. As these two modes export every object in a schema (all schemas for a full-mode export) all dependent objects on a table will be exported, which includes snapshot logs and their data because to export the snapshot log data the master table must also be exported. Yes, a table-level export can copy the master table, but it doesn't follow the dependency tree to copy other tables/objects (and, yes, indexes are exported with the table in a table-level export), thus the snapshot logs are, well, left behind. Think of it in this manner: with a table-level export any table can be exported, by itself, without having to export any dependent tables which may exist (tables upon which foreign key constraints are defined). As such snapshot logs are also not exported in table-level mode.<br /><br /> If you want/need snapshot logs exported, along with their data, then you must perform a full or user-level export. There is no other choice.<br /><br /> <span style="color:#990000;">If the snapshot log is defined using ROWID it will be necessary to perform a complete refresh immediately after importing the schema, as the stored ROWID values will be useless. Once a complete refresh is run all subsequent fast refreshes should execute without error. This is not the case with a primary key-based snapshot log; even immediately after import fast refreshes are possible.</span><br /><br /> So, to avoid surprises later (meaning 'mysteriously' missing snapshot logs) when you have snapshots/materialized views perform nothing less than a user-level export. This ensures you get your snapshot log definitions and data. And keeps your materialized view/snapshot fast refresh jobs running smoothly.<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-6859552606680898797?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com0tag:blogger.com,1999:blog-28935478.post-31143583462746311072008-07-25T20:07:00.004+01:002008-10-06T14:30:22.498+01:002008-10-06T14:30:22.498+01:00I've heard this lament many, many times:<br /><br /> "I need to trace a session that is already connected but isn't mine. How do I do that?"<br /><br /> It's really a fairly easy task. Oracle provides three packaged procedures to make that possible:<br /><br /> DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION<br /><br /> DBMS_SYSTEM.SET_EV<br /><br /> DBMS_SUPPORT.START_TRACE_IN_SESSION<br /><br /> and, of the three, I prefer DBMS_SYSTEM.SET_EV as it provides a level of control not easily implemented with DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION or DBMS_SUPPORT.START_TRACE_IN_SESSION. The procedure is fairly easy to use <span style="color:#990000;">(you must be connected as SYS to use it)</span>:<br /><br /> exec dbms_system.set_ev(&lt;sid&gt;,&lt;serial#&gt;, &lt;event&gt;, &lt;level&gt;, &lt;name, usually NULL&gt;)<br /><br /> So, if your user has been assigned sid 459 and serial# 11703 and you want a full-bore 10046 trace you would execute:<br /><br /> exec dbms_system.set_ev(459, 11703, 10046, 12, NULL)<br /><br /> and you'd have a level 12, 10046 event trace started on that session. Woohoo! So, what is this NM parameter used for? Oracle says it's to document the trace name, and you can use it to 'name' the trace you're starting so you can keep track of whose session you're monitoring. Of course if you do provide a name string when you start the trace then you need to provide that same string when you terminate that trace else it won't be stopped. So, I just leave it NULL.<br /><br /> DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION is a rather simplistic interface to DBMS_SYSTEM.SET_EV, giving you the opportunity to start and stop a trace, period. No setting levels, no deciding what you want to see, just the ability to start and stop a basic trace on a session which is not your own. Oh, and there's also the possibility that you won't get a trace started with this interface (I don't know why it fails, but I've had spotty results using this particular procedure.) I prefer to use the DBMS_SYSTEM.SET_EV procedure for that very reason.<br /><br /> DBMS_SUPPORT.START_TRACE_IN_SESSION provides a bit more flexibility by allowing you to include waits, binds, both or neither in your trace output:<br /><br /> exec dbms_support.start_trace_in_session(&lt;sid&gt;,&lt;serial#&gt;, &lt;waits, boolean&gt;, &lt;binds, boolean&gt;)<br /><br /> To use DBMS_SUPPORT.START_TRACE_IN_SESSION to effect the same level of trace you started using DBMS_SYSTEM.SET_EV you'd submit:<br /><br /> exec dbms_support.start_trace_in_session(459, 11703, TRUE, TRUE)<br /><br /> and you'd have the job done.<br /><br /> So you have the trace started, how on earth do you stop it? The trace will end when:<br /><br /> 1) The traced session terminates<br /> 2) You execute DBMS_SYSTEM.SET_EV(sid,serial#, event, 0, NULL) if that's the procedure you used to start the trace<br /> 3) You execute DBMS_SUPPORT.STOP_TRACE_IN_SESSION(sid,serial#) if you used DBMS_SUPPORT.START_TRACE_IN_SESsION<br /><br /> The DBMS_SYSTEM package is installed when you create a database; unfortunately the DBMS_SUPPORT package isn't, so if you want to use it you'll need to execute the dbmssupp.sql script in $ORACLE_HOME/rdbms/admin:<br /><br /> SQL> @?/rdbms/admin/dbmssupp<br /><br /> Don't run this as any user other than SYS, or it's not likely to work properly, if at all. And, since DBMS_SUPPORT isn't installed by default that's all the more reason to fire up DBMS_SYSTEM.SET_EV to trace other sessions.<br /><br /> Once the trace file is written you'd process it just like any other 10046 trace file, by using the tkprof utility to format the raw trace data into a really pretty report; the trace files generated by 'alter session set events ...' and the DBMS_SYSTEM.SET_EV procedure are the same. The only difference is in how you get the event trace started.<br /><br /> Because Oracle support personnel needed the ability to trace user sessions the DBMS_SYSTEM package was created. Of course you shouldn't be using this all of the time, on every session, because, in reality, not every session needs to be traced. And, if you do feel that need it's simpler and easier to change the init.ora or spfile to enable event 10046 tracing.<br /><br /> You may not use this utility even once per year, but it's nice to know you have access to it should the need arise. It's always better to have something and not need it rather than need something and not have it. So tuck this information away for that 'rainy day' that will eventually arrive. I think you'll be glad that you did.<br /><br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-3114358346274631107?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com1tag:blogger.com,1999:blog-28935478.post-6153351060224585692008-07-17T20:31:00.008+01:002008-10-06T21:04:51.794+01:002008-10-06T21:04:51.794+01:00Autoextend can be very helpful to busy DBAs by allowing Oracle to automatically increase file size when necessary. Some DBAs swear by it. Others swear at it, as it can be one of the largest nightmares a DBA can experience when left to its own devices by not providing a maximum size the file should attain.<br /><br /> Setting a file in a tablespace to autoextend is a fairly easy task as long as the user performing the actions has DBA or SYSDBA privileges: <pre><span style="color:#3333ff;">SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend on maxsize 2000M; Database altered. SQL></span></pre> And, not every datafile in a tablespace needs to be set to autoextend -- you can select one or more datafiles and leave others unaltered. The only true physical limit for autoextend datafiles is the size of the file system where they reside. Which is where the 'swearing at autoextend' part of the discussion begins.<br /><br /> While it may be considered wise by some to set datafiles to autoextend, doing so without first restricting their size is a grievous error. Executing the statement shown below: <pre><span style="color:#990000;">SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend on; Database altered. SQL></span></pre> is, well, irresponsible and careless, as the only restriction you've provided Oracle for the file size is the edge of the disk (figuratively speaking). Oracle will continue to extend this datafile ad infinitum, that is until it reaches the physical end of the storage media, when the process, and likely the database, will come to a grinding halt, displaying the (possibly nauseating) error: <pre><span style="color:#990000;">ORA-01237: cannot extend datafile /whackenhut/smackdoodle/endoplasm/flurst01.dbf </span></pre> followed by the relevant operating system errors indicating you have no space left on the desired device. And, by now, you have corrupted data in this tablespace and your tablespace (or, worse, database, depending upon how widespread this corruption becomes) needs to be restored and recovered. Yes, it (hopefully) ensures your backup and recovery strategy is sound, but I'm fairly certain that management wasn't expecting to test that process by having datafiles attempt to extend beyond their physical limits.<br /><br /> On leveraged systems (systems where more than one database may reside) autoextend, in any form, isn't a wise idea as you now have multiple databases competing for limited resources (disk space) and whichever database gets to that available space first, wins. And you also have X times more opportunities to reach the end of the physical media, and you can do it so much faster when 13 databases are competing for disk space on the same devices.<br /><br /> Okay, so you have autoextend datafiles and can't change that fact (management says 'We need them'); how do you monitor the size? Hopefully a MAXSIZE was set and, if so, it's pretty easy. The DBA_DATA_FILES view provides almost all of the necessary information (what's missing is the db_block_size, and that's fairly easy to find): <pre><span style="color:#3333ff;">SQL> select file_name, bytes, maxbytes, 2 increment_by*(bytes/blocks) "INCREMENT", 3 maxbytes-bytes remaining, 4 (maxbytes-bytes)/(increment_by*(bytes/blocks)) EXTENSIONS 5 from dba_data_files 6 where autoextensible = 'YES' 7 / FILE_NAME BYTES MAXBYTES INCREMENT REMAINING EXTENSIONS -------------------------- ---------- ---------- ---------- ---------- ---------- /d909/data/users01.dbf 52428800 314572800 10485760 262144000 25 SQL></span></pre> Notice the current size, the maximum size, the incremental value, space remaining and the extensions available are reported, to give you an idea of how much room is left to allocate to your autoextend datafile. You could execute this query on a daily basis to see how quickly or slowly your datafile is growing, then plan for either modifying the maxsize or adding another datafile. And all of this presumes you have sufficient disk space for the remaining extensions to occur; monitoring autoextend tablespaces also means monitoring the disk space at the operating system level to ensure that you don't autoextend yourself out of a database.<br /><br /> Can you set a datafile, already configured to autoextend, to have a maximum size? Certainly, and it's done with the same command used to turn autoextend on with a maximum size. To change the setting for the file we set at the beginning of this post we would: <pre><span style="color:#3333ff;">SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend on maxsize 2200M; Database altered. SQL></span></pre> and, voila!, it will now extend to 2200 M instead of the 2000 M we originally set as its limit.<br /><br /> Of course one can always turn off autoextend: <pre><span style="color:#3333ff;">SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend off; Database altered. SQL></span></pre> On the whole I cannot recommend to anyone the use of autoextend for datafiles. Others may disagree, and that's what makes this such an interesting world in which to live. It's your choice to make; the decision should be a wise one, not one of convenience. I'd prefer to be 'inconvenienced' by a monitoring tool or script rather than the necessity of restoring a tablespace, or a database, because autoextend was implemented and left unrestricted.<br /><br /> As I learned early on, don't over-extend yourself; it can only get you into trouble later.<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/28935478-615335106022458569?l=oratips-ddf.blogspot.com' alt='' /></div>d_d_fhttp://www.blogger.com/profile/03203479596583639865oratune@msn.com1