You Can't Do That ... Or, Can You?

Views 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:

ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table

how do you know if you can update a particular view? Ask Oracle, of course.

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:

USER_UPDATABLE_COLUMNS
ALL_UPDATABLE_COLUMNS
DBA_UPDATABLE_COLUMNS

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:

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

This, of course, returns rows for tables and views; restricting this to just the views is a simple task:

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>

In this example it's possible to reduce the output considerably since all columns in a given view report the same value for UPDATABLE:

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>

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:

select distinct owner, table_name, updatable
from all_updatable_columns
where table_name in (select view_name from all_views)
and updatable = 'YES';

The list generated by that query may be quite large.

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.

So, it's easy to find the views a user can update because Oracle knows which views those are. Neat.

They call it Oracle for a reason.

Hanging With The Locals

There 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' is 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.

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:

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>

Let's look at some information about tablespaces created as locally managed:

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATIO MIN_EXTLEN
------------------------------ -------------- ----------- ---------- --------- ----------
UNDOTBS1                                65536             LOCAL      SYSTEM         65536
SYSAUX                                  65536             LOCAL      SYSTEM         65536

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 DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure:

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATIO MIN_EXTLEN
------------------------------ -------------- ----------- ---------- --------- ----------
TOOLS                                   32768       32768 LOCAL      USER           32768
USERS                                  131072      131072 LOCAL      USER          131072

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.

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

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.

How Much Wood Could A Woodchuck Chuck ...

Some of the most interesting questions pass through www.google.com, like this one:

"how to know how much table space is allocated for a user in oracle"

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.

DBA_TS_QUOTAS provides information on which tablespaces a given user can use and how much space they can consume:

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>

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.

Onward and upward to DBA_DATA_FILES:

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>

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.

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

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>

Notice the report returns all of the tablespaces in the database, with user information for the tablespaces the given user can utilize.

Can this be done for a database release earlier than 9i? You bet (but, really, you should upgrade to a supported release of Oracle):

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>

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.

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:

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>

And for releases earlier than 9i:

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>

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.

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.

So if you're still on 8.1.7.4 seriously 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.

You would.

Really.

Expect The Unexpected

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

I think by now most people know that if the time is not supplied to TO_DATE it defaults to midnight:

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>

so this should be expected behaviour. Let's look at what happens when the day is not supplied:

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>

So far this is behaviour to be expected. What happens when only the year is supplied? Let's find out:

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>

Hmmm, I would have expected the current month and day to be returned. Now let's pass the day and the year to TO_DATE:

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>

And that's what I would expect. Passing in the day and the month causes Oracle to use the current year:

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>

and that's, again, what I would expect. Now let's pass in only the day:

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>

Now let's provide only the time:

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>

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.

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:

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>

Fixing that problem means rewriting the query a bit:

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>

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.

Expect the unexpected, and nothing should be a surprise. Well, at least not an unpleasant one.

That Was The Week That Wasn't

The following question seems simple enough:

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

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

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.

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:

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>

[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.]

But Week 7 of that numbering convention doesn't contain February 8, 2009:

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>

Week 6 does, although it's not the starting date of that week:

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>

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:

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>

[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.]

ISO Week 7 doesn't answer the second condition, either, since February 8, 2009 is the last day of ISO Week 6:

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>

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:

the week with the year's first Thursday in it (the ISO 8601 definition)
the week starting with the Monday which is nearest in time to 1 January
the week with the year's first working day in it (if Saturdays, Sundays, and 1 January are not working days)
the week with January 4 in it
the first week with the majority (four or more) of its days in the starting year
the week starting with the Monday in the period 29 December - 4 January
the week with the Thursday in the period 1 - 7 January
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.

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:

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>

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:

The first week of the year contains 1 January, the 1st Saturday and is comprised of days 1-7 of the year.

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.

And that was the week that wasn't.

It's Moving Day

This seems to be a typical series of events when things aren't going quite as nicely as originally planned:

"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):

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

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

Tables, sequences, synonyms and packages are created to enable you to gather statistics, instance-wide, with a simple command:

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

SQL>

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

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

Select the snaps to compare, choose a file name if you don't want the default, and, voila! You have your report."

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 different tablespace for the PERFSTAT tables? What if you selected a tablespace you thought 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:

SQL> connect / as sysdba
Connected.
SQL> alter user perfstat quota unlimited on statdata;

User altered.

SQL> connect perfstat
Password:
Connected.
SQL>

Then generate scripts to move the tables and rebuild the indexes and execute them as PERFSTAT. A sample index rebuild script is shown:

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;

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]:

select 'alter table '||table_name||' move tablespace <tablespacename>;'
from user_tables
where tablespace_name is not null;

and

select 'alter index '||index_name||' rebuild tablespace <tablespacename>;'
from user_indexes;

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.

So, it isn't really a problem to move the Statspack tables to another location, as long as you're careful and plan ahead.

And this move doesn't require a U-Haul.