Saturday, June 28, 2014

Tablespace Reorganization

Tablespace Reorganization

Tablespace reorganization is a process of reorganizing the contents of a tablespace to reclaim free space from the datafiles of a tablespace. When a datafiles is resized (shrink), the size of a datafiles can only be reduced till the high water mark (last allocated extent to a segment) of the datafile. This does not guarantee that all free space have been reclaimed from within the datafile because there could be free space available in the datafile bellow the high water mark which can not be reclaimed until tablespace data is made compact after reorganizing. Tim Hall has demonstrated it with example here,

While replying to a thread (click here) in OTN forums, I tried to explain that using same tablespace for move/rebuild of segments won't help releasing all free space from within the datafile. I am copying and pasting same contents from the forums here in the following which explain how using same tablespace for rebuilding/moving segments would leave free space “islands” in the datafiles of tablespace.

While allocating extents during data load, it does allocate space in round robin fashion across the datafiles, but it does not guarantee that if you are rebuilding/moving segment in the same tablespace, it would allocate the extents from the beginning of the datafiles because extents from the beginning of the file might already have been allocated to same segment being rebuilt/moved, and can't be de-allocated until its rebuilt is completed (by allocating next available extents in a datafile). And once segment rebuild/move finishes, previously allocated extents (which might be at the beginning of the datafile(s) ), would be de-allocated leaving - free space at the beginning of the datafile(s) . Have a look at the following example where I am trying to simulate same what I just said.

1) A tablespace with one datafile with a size of 25 MB (1 MB uniform extent size allocation - so total 25 extents or 1MB can be allocated in the datafile).

2) 3 tables in the tablespace having allocated total 15 MB as follows allocated to them as follows. "E" means extent.
Table1: E5, E9, E13, E15, E17, E18
Table2: E2, E4, E8, E20
Table3: E1, E3, E6, E23, E24

3) Since allocation is uniform 1M, I am also considering free space as an "extent of 1M" for simplicity and denoting it as "F"

Extent map of datafile is as follows
E1, E2, E3, E4, E5, F6F7, E8, E9, F10F11F12, E13, E14, E15, F16, E17, E18, F19, E20, F21F22, E23, E24, F25

So ideally we should be able to reclaim 10 MB from the datafile, we can reclaim only 1 MB because last allocated extent is E24. Now we rebuild all 3 tables one by one. Rebuilding each table would allocate it new extents first and then drop the previously allocated extents.

Table1
Rebuilding Table1 will have following new extents allocated from free space
Table1: E6(F6), E7(F7), E10(F10), E11(F11), E12(F12), E16(F16)

Now it will free up the extents: F5(E5), F9(E9), F13(E13), F15(E15), F17(E17), F18(E18)

New extent map will be as follows
E1, E2, E3, E4, F5, E6, E7, E8, F9, E10, E11, E12, F13, E14, F15, E16, F17, F18, F19, E20, F21, F22, E23, E24, F25

Table2
Rebuilding Table2 will have following new extents allocated from free space
Table2: E5(F5), E9(F9), E13(F13), E15(F15)

Now it will free up the extents: F2(E2), F4(E4), F8(E8), F20(E20)

New extent map will be as follows
E1, F2, E3, F4, E5, E6, E7, F8, E9, E10, E11, E12, E13, E14, E15, E16, F17, F18, F19, F20, F21, F22, E23, E24, F25

Table3
Rebuilding Table3 will have following new extents allocated from free space
Table2: E2(F2), E4(F4), E8(F8), E17(F17), E18 (F18)

Now it will free up the extents: F1(E1), F3(E3), F6(E6), F23(E23), F24(E24)

New extent map will be as follows

F1, E2, F3, E4, E5, F6, E7, E8, E9, E10, E11, E12, E13, E14, E15, E16, E17, E18, F19, F20, F21, F22, F23, F24, F25

We can see that we still have free space at the beginning of the datafile . This was a simple example, and in more complex environment; we might have more unused space space left at the beginning of the datafile(s).

Another Example
Following is the example which shows a single table rebuilt/move in same tablespace and how it leaves free space at the beginning of the datafile after a rebuilt

SQL> create table test as select * from dba_tables;
Table created.

SQL> alter table test move tablespace testtbs;
Table altered.

SQL> select min(block_id),max(block_id) from dba_extents where tablespace_name='TESTTBS';

MIN(BLOCK_ID) MAX(BLOCK_ID)
---------------------    -------------------------
          128           224

SQL> alter table test move tablespace testtbs ;
Table altered.

SQL> select min(block_id),max(block_id) from dba_extents where tablespace_name='TESTTBS';

MIN(BLOCK_ID) MAX(BLOCK_ID)
---------------------    -------------------------
          232           328

You can see above that during rebuild of table, new extents were allocated after the last extent already occupied by the same table. In the following, when we rebuild/move of table again, now it will allocate extents again from the beginning of the datafile.

SQL> alter table test move tablespace testtbs ;
Table altered.

SQL> select min(block_id),max(block_id) from dba_extents where tablespace_name='TESTTBS';

MIN(BLOCK_ID) MAX(BLOCK_ID)
---------------------    -------------------------

          128           224

Tuesday, June 24, 2014

ORA-01031 insufficient privileges - While logging in AS SYSDBA

OS Authentication is a setup whereby we can log into the oracle database as SYSDBA or SYSOPER without supplying any password because OS user (currently logged in) is already authenticated to log into the database as a privileged user. This is handy specially if you have forgotten the SYS password (another way to recover SYS password is to recreate the password file).
As explained above, in Oracle, OS authentication can be enabled so that DBAs don't need to provide password while logging in as "AS SYSDBA" and to do so, we need to add the OS user in OSDBA group (group name is ORA_DBA on Windows, and dba in Unix based systems) and specifically for windows based systems, we also need to add a parameter "sqlnet.authentication_services= (nts)" in the sqlnet.ora file found under ORACLE_HOME\network\admin folder.
ORA-01031: insufficient privileges
A few days back I faced an issue while working at a customer site on a Windows based server, where I was trying to login as "as SYSDBA" without supplying a password (using OS Authentication), and I faced error message "ORA-01031: insufficient privileges". I realized that all prerequisites for OS authentication are already met as my OS user was member of ORA_DBA group, and parameter "sqlnet.authentication_services= (nts)" was already present in sqlnet.ora file.

I was really baffled and opened a case with Oracle support who mentioned MOS document 730067.1 which explains how to enable OS authentication on different platforms, but, I already had fulfilled all prerequisites which did not help.
Solution

Thanks to one of my colleagues who helped me in that by mentioning a similar issue he faced and which was related to TNS_ADMIN setting in registry.

TNS_ADMIN is a parameter in windows registry (under oracle database software registry key) which points to the location of TNS files (tnsnames.ora, sqlnet.ora, listener.ora etc.). In my case, there were 2 installations on that server (Oracle application server 10g and Oracle database server 11g) and TNS_ADMIN in the registry (under Oracle database software key) was actually pointing to TNS files of application server home, and in application server home (as you can see in bellow image)



and SQLNET.AUTHENTICATION_SERVICES=(NTS) was disabled/commented in application server home SQLNET.ORA file as you can see bellow

####################################################
#SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= ( TNSNAMES)
####################################################

So I had two options: either un-comment this parameter here in application server home, or set my TNS_ADMIN value to point to database home TNS files location so that it can read sqlnet.ora file in oracle database home where this parameter is already set.

Moral of the story: In case of any network/connectivity issue, don't forget to check TNS_ADMIN setting and make sure it is pointing to correct TNS files locations where you have made your required settings.

On UNIX based systems, SQLNET.AUTHENTICATION_SERVICES is not required to be set for OS authentication.

On UNIX based systems, TNS_ADMIN is used as environment variable to point to the network files from a specific ORACLE_HOME which we want to use. This can either be set in .bash_profile/.profile or also can be set at command prompt.

Sunday, June 22, 2014

Oracle IO Consumption - Throughput (IOPS) and Transfer Rate (MBPS)

Oracle Database IO Requirements (Throughput/IOPS and Transfer Rate/MBPS)

If we plan to migration an Oracle database to a new/different hardware, planning for storage is also a very important thing. It is good to find out the IO requirements of the database so that a better decision could be made while purchasing the storage. Storage speed is measured for Throughput (IOPS or IOs Per Second) and Transfer Rate(MBPS or Megabytes Per Second)
Within the Oracle database, 2 Metrics are available to have a look for "I/O Requests per Second"; which is for Throughput, and "I/O Megabytes per Second"; which is for the Transfer Rate.


In Oracle 10g and above, to measure the IO requirements; we can use DBA_HIST_SYSMETRIC_HISTORY (This view contains snapshots of V$SYSMETRIC_HISTORY) and DBA_HIST_SYSMETRIC_SUMMARY (This view contains snapshots of V$SYSMETRIC_SUMMARY) to analyze the IO requirement of current database by looking into the values of aforementioned metrics.

DBA_HIST_SYSMETRIC_HISTORY contains information in details, but querying DBA_HIST_SYSMETRIC_SUMMARY would suffice.

How DBA_HIST_SYSMETRIC_SUMMARY Works

Please note that using %HIST% views needs Oracle Diagnostic Pack license.


%HIST% views get information from AWR repository which is based on AWR snapshots. We can specify a time duration between which we want to view the IO related statistics. If your AWR snapshot interval is 10 minutes and you want to get the IO information between 23-jun-14 00:00:00 to 23-jun-14 08:00:00 (8 hours), then you should have roughly 48 rows returned from the view DBA_HIST_SYSMETRIC_SUMMARY- where you can see maximum and average IOs Per Second. The column NUM_INTERVAL will show total intervals in which this IO information was captured. For my case, it is 10 because AWR snapshot interval is set to 10 minutes.

Example 1 (Throughput/IOPs)

Use following query to see what were IOs per second over a period of time. For RAC, you can add column INSTANCE_NUMBER in WHERE clause to restrict result to a specific instance. 

select begin_time,end_time,round(maxval) maximum_iops,round(average) averate_iops,NUM_INTERVAL from DBA_HIST_SYSMETRIC_SUMMARY where begin_time between 
to_date('23-jun-14 00:00:00','DD-mon_yy hh24:mi:ss') and to_date('23-jun-14 08:00:00','DD-mon_yy hh24:mi:ss') and METRIC_NAME='I/O Requests per Second' order by begin_time; 

BEGIN_TIME END_TIME MAXIMUM_IOPS AVERATE_IOPS NUM_INTERVAL
23-JUN-14 00:09:21 23-JUN-14 00:19:21 136 82 10
23-JUN-14 00:19:21 23-JUN-14 00:29:21 87 65 10
23-JUN-14 00:29:21 23-JUN-14 00:39:21 105 71 10
23-JUN-14 00:39:21 23-JUN-14 00:49:21 144 77 10
23-JUN-14 00:49:21 23-JUN-14 00:59:22 254 127 10
23-JUN-14 00:59:22 23-JUN-14 01:09:21 192 127 10
23-JUN-14 01:09:21 23-JUN-14 01:19:22 149 97 10
23-JUN-14 01:19:22 23-JUN-14 01:29:21 116 87 10
23-JUN-14 01:29:21 23-JUN-14 01:39:22 158 96 10
23-JUN-14 01:39:22 23-JUN-14 01:49:22 146 72 10
23-JUN-14 01:49:22 23-JUN-14 01:59:22 329 209 10
23-JUN-14 01:59:22 23-JUN-14 02:09:22 594 467 10
23-JUN-14 02:09:22 23-JUN-14 02:20:22 396 322 11
23-JUN-14 02:20:22 23-JUN-14 02:30:22 405 250 10
23-JUN-14 02:30:22 23-JUN-14 02:40:22 264 211 10
23-JUN-14 02:40:22 23-JUN-14 02:50:22 338 195 10
23-JUN-14 02:50:22 23-JUN-14 03:00:23 98 74 10
23-JUN-14 03:00:23 23-JUN-14 03:10:22 292 124 10
23-JUN-14 03:10:22 23-JUN-14 03:20:23 318 185 10
23-JUN-14 03:20:23 23-JUN-14 03:30:23 200 168 10
23-JUN-14 03:30:23 23-JUN-14 03:40:23 273 185 10
23-JUN-14 03:40:23 23-JUN-14 03:50:23 145 90 10
23-JUN-14 03:50:23 23-JUN-14 04:00:23 171 101 10
23-JUN-14 04:00:23 23-JUN-14 04:10:23 174 117 10
23-JUN-14 04:10:23 23-JUN-14 04:20:24 143 90 10
23-JUN-14 04:20:24 23-JUN-14 04:30:23 215 102 10
23-JUN-14 04:30:23 23-JUN-14 04:40:23 196 91 10
23-JUN-14 04:40:23 23-JUN-14 04:50:23 209 93 10
23-JUN-14 04:50:23 23-JUN-14 05:00:24 78 65 10
23-JUN-14 05:00:24 23-JUN-14 05:10:24 134 67 10
23-JUN-14 05:10:24 23-JUN-14 05:20:23 152 71 10
23-JUN-14 05:20:23 23-JUN-14 05:30:24 201 136 10
23-JUN-14 05:30:24 23-JUN-14 05:40:24 155 121 10
23-JUN-14 05:40:24 23-JUN-14 05:50:24 228 99 10
23-JUN-14 05:50:24 23-JUN-14 06:00:25 291 107 10
23-JUN-14 06:00:25 23-JUN-14 06:10:24 1035 333 10
23-JUN-14 06:10:24 23-JUN-14 06:20:24 565 256 10
23-JUN-14 06:20:24 23-JUN-14 06:30:24 455 321 10
23-JUN-14 06:30:24 23-JUN-14 06:40:24 367 301 10
23-JUN-14 06:40:24 23-JUN-14 06:50:25 372 317 10
23-JUN-14 06:50:25 23-JUN-14 07:00:25 287 188 10
23-JUN-14 07:00:25 23-JUN-14 07:10:25 200 136 10
23-JUN-14 07:10:25 23-JUN-14 07:20:25 126 93 10
23-JUN-14 07:20:25 23-JUN-14 07:30:25 144 115 10
23-JUN-14 07:30:25 23-JUN-14 07:40:25 250 145 10
23-JUN-14 07:40:25 23-JUN-14 07:50:25 219 127 10
23-JUN-14 07:50:25 23-JUN-14 08:00:25 149 120 10 

Example 2 (Transfer Rate/MBPS)

Use following query to see what was “transfer rate per second” over a period of time. For RAC, you can add column INSTANCE_NUMBER in WHERE clause to restrict result to a specific instance.

select begin_time,end_time,round(maxval) maximum_iops,round(average) averate_iops,NUM_INTERVAL from DBA_HIST_SYSMETRIC_SUMMARY where begin_time between 
to_date('23-jun-14 00:00:00','DD-mon_yy hh24:mi:ss') and to_date('23-jun-14 08:00:00','DD-mon_yy hh24:mi:ss') and METRIC_NAME='I/O Megabytes per Second' order by begin_time

BEGIN_TIME END_TIME MAXIMUM_MBPS AVERATE_MBPS NUM_INTERVAL
23-JUN-14 00:09:21 23-JUN-14 00:19:21 7 2 10
23-JUN-14 00:19:21 23-JUN-14 00:29:21 25 3 10
23-JUN-14 00:29:21 23-JUN-14 00:39:21 1 1 10
23-JUN-14 00:39:21 23-JUN-14 00:49:21 7 1 10
23-JUN-14 00:49:21 23-JUN-14 00:59:22 31 4 10
23-JUN-14 00:59:22 23-JUN-14 01:09:21 1 1 10
23-JUN-14 01:09:21 23-JUN-14 01:19:22 7 1 10
23-JUN-14 01:19:22 23-JUN-14 01:29:21 25 3 10
23-JUN-14 01:29:21 23-JUN-14 01:39:22 7 1 10
23-JUN-14 01:39:22 23-JUN-14 01:49:22 1 1 10
23-JUN-14 01:49:22 23-JUN-14 01:59:22 26 8 10
23-JUN-14 01:59:22 23-JUN-14 02:09:22 71 62 10
23-JUN-14 02:09:22 23-JUN-14 02:20:22 72 65 11
23-JUN-14 02:20:22 23-JUN-14 02:30:22 84 66 10
23-JUN-14 02:30:22 23-JUN-14 02:40:22 68 58 10
23-JUN-14 02:40:22 23-JUN-14 02:50:22 62 58 10
23-JUN-14 02:50:22 23-JUN-14 03:00:23 88 63 10
23-JUN-14 03:00:23 23-JUN-14 03:10:22 77 59 10
23-JUN-14 03:10:22 23-JUN-14 03:20:23 62 58 10
23-JUN-14 03:20:23 23-JUN-14 03:30:23 95 58 10
23-JUN-14 03:30:23 23-JUN-14 03:40:23 70 61 10
23-JUN-14 03:40:23 23-JUN-14 03:50:23 70 60 10
23-JUN-14 03:50:23 23-JUN-14 04:00:23 73 59 10
23-JUN-14 04:00:23 23-JUN-14 04:10:23 66 61 10
23-JUN-14 04:10:23 23-JUN-14 04:20:24 58 45 10
23-JUN-14 04:20:24 23-JUN-14 04:30:23 69 38 10
23-JUN-14 04:30:23 23-JUN-14 04:40:23 46 40 10
23-JUN-14 04:40:23 23-JUN-14 04:50:23 25 22 10
23-JUN-14 04:50:23 23-JUN-14 05:00:24 25 4 10
23-JUN-14 05:00:24 23-JUN-14 05:10:24 1 1 10
23-JUN-14 05:10:24 23-JUN-14 05:20:23 9 1 10
23-JUN-14 05:20:23 23-JUN-14 05:30:24 26 6 10
23-JUN-14 05:30:24 23-JUN-14 05:40:24 8 3 10
23-JUN-14 05:40:24 23-JUN-14 05:50:24 1 1 10
23-JUN-14 05:50:24 23-JUN-14 06:00:25 24 3 10
23-JUN-14 06:00:25 23-JUN-14 06:10:24 9 5 10
23-JUN-14 06:10:24 23-JUN-14 06:20:24 11 4 10
23-JUN-14 06:20:24 23-JUN-14 06:30:24 35 11 10
23-JUN-14 06:30:24 23-JUN-14 06:40:24 11 6 10
23-JUN-14 06:40:24 23-JUN-14 06:50:25 11 5 10
23-JUN-14 06:50:25 23-JUN-14 07:00:25 26 6 10
23-JUN-14 07:00:25 23-JUN-14 07:10:25 7 2 10
23-JUN-14 07:10:25 23-JUN-14 07:20:25 1 1 10
23-JUN-14 07:20:25 23-JUN-14 07:30:25 25 4 10
23-JUN-14 07:30:25 23-JUN-14 07:40:25 8 3 10
23-JUN-14 07:40:25 23-JUN-14 07:50:25 8 2 10
23-JUN-14 07:50:25 23-JUN-14 08:00:25 25 4 10 

Friday, June 20, 2014

Online Partition Move

As you know that Oracle partition is a database object used to partition the table’s data into multiple segments which might be stored in different tablespace for the manageability purposes. There are several partition operations that can be performed on the partitions which are called partition maintenance operations and one of them is the “partition move” which means: to move partition from one tablespace to another tablespace. Partition move could be required in order to manage the data/partition easily and it also helps us to leverage the available storage efficiently. Partitions which are not accessed very frequently and also not hotspots, can be put in the tablespace(s) which reside on a cheaper and slower storage whereas partitions which are hotspots and are also accessed very frequently can be place in the tablespace(s) which reside on a high-end storage.
Prior to 12c, if you plan to move a partition of a table from one tablespace to another tablespace, it would require the partition data to remain unavailable during the execution of this task. It means that if you have huge data in a partition , you might need quite a long time to finish the partition move operation and data would not be available (transactions would need to wait until this move operation finishes.
In oracle 12c and above, you can move a partition from one tablespace to another tablespace online - which means that your partition remains available for operations during the partitions movement is in progress. (To learn about online datafile move/rename feature, click here)

Syntax

ALTER TABLE <table_name> MOVE PARTITION <PARTITION_NAME> TABLESPACE <tablespace_name> UPDATE INDEXES ONLINE;


Example

15:28:51 SQL> CREATE TABLE test
15:28:55   2    ( id       NUMBER(6)
15:28:55   3      , test_date DATE
15:28:55   4    )
15:28:55   5   PARTITION BY RANGE (test_date)
15:28:55   6   ( PARTITION yesterday VALUES LESS THAN (TO_DATE('19-JUN-20yyy'))
15:28:55   7      TABLESPACE mytbs
15:28:55   8   , PARTITION today VALUES LESS THAN (maxvalue)
15:28:55   9      TABLESPACE mytbs
15:28:55  10   );

Table created.

15:28:56 SQL> insert into test values(1,sysdate);

1 row created.

15:29:07 SQL>  insert into test values(1,sysdate-2);

1 row created.

15:29:14 SQL> insert into test select * from test;

2 rows created.

-- Repeat several times to generate huge amount of data to make partition size huge by using following command.
Insert into test select * from test;

15:30:216 SQL> commit;

Commit complete.

15:30:26 SQL> select count(*) from test partition(today);

  COUNT(*)
----------
    262144

15:30:53 SQL> select count(*) from test partition(yesterday);

  COUNT(*)
----------
    262144


15:37:35 SQL> alter table test move partition today tablespace users online ;

While this was in progress, in another window, I did some insertions in partition "today" and I was able to to that, please see bellow image

15:38:05 SQL> select count(*) from test partition(today);

  COUNT(*)
----------
    262163





















As above test demonstrates, the facility to move partitions from one tablespace to another tablespace is very handy, specially when downtime can’t be afforded.

One thing to note is that UPDATE INDEX still needs to be specified to update the indexes otherwise indexes would become UNUSABLE. ONLINE clause is also required to be specified to make sure that partition move operation is ONLINE otherwise it would be an OFFLINE operation and data in the partition would not be available for normal operations.

Wednesday, June 18, 2014

Online Datafile Move or Rename

Here I am going to discuss one of my favourites new feature of Oracle 12c which has taken the down time away from the DBAs while moving or renaming a datafile online. There is no requirement of any downtime during this operation

Renaming/Moving a Datafile Prior to 12c
Prior to Oracle 12c, if we need to move or rename a datafile, we would need to take tablespace offline, or take full database off line (mount) in case of SYSTEM, SYSAUX and/or current UNDO tablespace in order to move or rename a datafile. Prior to executing the RENAME command, it was also required to manually copy/move the datafile to the destination location. As mentioned above, this downtime could be substantial if database is huge.
Renaming/Moving a Datafile in 12c and above
In Oracle 12c, a brilliant new feature is moving/renaming datafile online from one location to another location. The source location can be either ASM or file system and destination could also be either ASM or file system. While datafile move is in process all contents of datafile remains accessible and hence this is a totally online procedure with a few exceptions - A datafile can't be moved if
·                     Datafile is offline
·                     Any flashback or recovery operation is currently running on the datafile
·                     Tablespace of datafile is offline
·                     Datafile resize (shrink only) operation is currently in progress for this datafile


Please note again that there is no need to manually move the datafile to destination as Oracle will perform this this task itself.  (To learn about online partition move feature, click here)
Syntax
ALTER DATABASE MOVE DATAFILE <file_name> TO <new_location_and_name>;

Original file can be preserved at the original location while moving to the other location. This file on the source location can be used as a backup copy. In this case, the syntax would add keyword "KEEP" at the end of the statement.

ALTER DATABASE MOVE DATAFILE <file_name> TO <new_location_and_name> KEEP;

Example (Moving system datafile from ASM to Filesystem and then moving back from Filesystem to ASM)

In the following I am illustrating an example where I would be moving SYSTEM tablespace datafile to the file system, and after that I would move back this file from file system to the ASM diskgroup from where it was moved originally.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/CDB/DATAFILE/system.261.850522599
+DATA/CDB/F79E49A638DF11C3E0430A20A8C0DEA9/DATAFILE/system.268.845569249
+DATA/CDB/DATAFILE/sysaux.256.845569259
+DATA/CDB/F79E49A638DF11C3E0430A20A8C0DEA9/DATAFILE/sysaux.279.845569277
+DATA/CDB/DATAFILE/undotbs1.276.845569283
+DATA/CDB/DATAFILE/users.267.845569333
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/system.259.847554537
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/sysaux.258.847554537
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/salman.264.847991093
+DATA/CDB/DATAFILE/salman.278.848010629
+DATA/CDB/DATAFILE/testdbs.260.848593699

11 rows selected.

SQL> alter database move datafile '+DATA/CDB/DATAFILE/system.261.850522599' to '/u01/app/oracle/system01.dbf';

Database altered.

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/system01.dbf
+DATA/CDB/F79E49A638DF11C3E0430A20A8C0DEA9/DATAFILE/system.268.845569249
+DATA/CDB/DATAFILE/sysaux.256.845569259
+DATA/CDB/F79E49A638DF11C3E0430A20A8C0DEA9/DATAFILE/sysaux.279.845569277
+DATA/CDB/DATAFILE/undotbs1.276.845569283
+DATA/CDB/DATAFILE/users.267.845569333
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/system.259.847554537
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/sysaux.258.847554537
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/salman.264.847991093
+DATA/CDB/DATAFILE/salman.278.848010629
+DATA/CDB/DATAFILE/testdbs.260.848593699

11 rows selected.

SQL>  alter database move datafile '/u01/app/oracle/system01.dbf' to '+DATA';

Database altered.

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/CDB/DATAFILE/system.261.850525283

+DATA/CDB/F79E49A638DF11C3E0430A20A8C0DEA9/DATAFILE/system.268.845569249
+DATA/CDB/DATAFILE/sysaux.256.845569259
+DATA/CDB/F79E49A638DF11C3E0430A20A8C0DEA9/DATAFILE/sysaux.279.845569277
+DATA/CDB/DATAFILE/undotbs1.276.845569283
+DATA/CDB/DATAFILE/users.267.845569333
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/system.259.847554537
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/sysaux.258.847554537
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/salman.264.847991093
+DATA/CDB/DATAFILE/salman.278.848010629
+DATA/CDB/DATAFILE/testdbs.260.848593699

11 rows selected.

Sunday, June 15, 2014

Oracle Segment Adviser Recommendations and Redo/Undo Generation During Segment Shrink

Since Oracle 10g, Oracle provided facility to reclaim the unused space from Tables and Indexes by using"ALTER TABLE <TABLE_NAME> SHRINK SPACE | COMPACT"  command.
Before we go through details of Oracle Segment Adviser Recommendations, it should be noted that during shrink space operation, a lot of undo and redo data is generated. As per my experience, I was shrinking a 19G table which had 3.3 G of reclaimable space and the redo it generated was above 52G. MOS note 577375.1 says that table shrinking is done by delete/insert pair of every row movement and hence generates the redo. It also points to a bug 3888229 which was closed as NOT A BUG but, as expected behaviour

Segment Advisor
Oracle Segment Advisor runs automatically during daily and weekly maintenance windows to give you recommendations for the segments which can be shrunk for reclaiming unused space.
OEM (Cloud Control, Grid Control, Database Control) is the easiest way to look at the recommendations and implementing them. If OEM is not available, data dictionary views could be used to see the details of recommendations and their implementation.

In the following, you would see how information is extracted from data dictionary views for Segment Advisor tasks.

Findout Segment Advisor Tasks

set lines 200
set pages 1000
col message format a60
alter session set nls_date_format='DD-MON-YY HH24:MI:SS';
select TASK_NAME,EXECUTION_START,EXECUTION_END,status from DBA_ADVISOR_TASKS where task_name like '%AUTO%'order by EXECUTION_END ;

TASK_NAME                      EXECUTION_START    EXECUTION_END      STATUS
------------------------------ ------------------ ------------------ -----------
SYS_AUTO_SPCADV_190221362014   14-JUN-14 06:00:21 14-JUN-14 06:31:52 COMPLETED

Findout Findings, Recommendations and Actions (to implement recommendations) of this Tasks

Either you should use slqdeveloper or some other GUI tool for the following queries, or use html output spool from sqlplus.

set markup html on
spool segment_advisor.html

select owner,message,more_info from DBA_ADVISOR_FINDINGS where task_name='SYS_AUTO_SPCADV_190221362014';

select benefit_type from DBA_ADVISOR_RECOMMENDATIONS where task_name='SYS_AUTO_SPCADV_190221362014';

select command,attr1,attr2,attr3 from DBA_ADVISOR_ACTIONS where TASK_NAME='SYS_AUTO_SPCADV_190221362014';

In above statement, attr1 column shows the command for SHRINK SPACE COMPACT, attr2 column shows the command SHRINK SPACE, and attr3 shows the command ENABLE ROW MOVEMENT (For tables, and only for the tables which don't have row movement enabled).

Following is an html output example.


SQL> select owner,message,more_info from DBA_ADVISOR_FINDINGS where task_name='SYS_AUTO_SPCADV_190221362014';


OWNER

MESSAGE

MORE_INFO

SYS

Perform shrink, estimated savings is 152515307 bytes.

Allocated Space:6888827713: Used Space:6736312406: Reclaimable Space :152515307:

SYS

Perform shrink, estimated savings is 338077372 bytes.

Allocated Space:3965640175: Used Space:3627562803: Reclaimable Space :338077372:

SYS

Perform shrink, estimated savings is 972566831 bytes.

Allocated Space:7491616504: Used Space:6519049673: Reclaimable Space :972566831:

SYS

Enable row movement of the table LOCATION_ACTIVITY_DB.MENU_ITEM_FIXED_PERIOD_TOTAL and perform shrink, estimated savings is 7174778795 bytes.

Allocated Space:28587327488: Used Space:21412548693: Reclaimable Space :7174778795:

SYS

Perform shrink, estimated savings is 1695011163 bytes.

Allocated Space:3667262590: Used Space:1972251427: Reclaimable Space :1695011163:

SYS

Perform shrink, estimated savings is 26915027 bytes.

Allocated Space:272817251: Used Space:245902224: Reclaimable Space :26915027:

SYS

Perform shrink, estimated savings is 186165922 bytes.

Allocated Space:569549857: Used Space:383383935: Reclaimable Space :186165922:

SYS

Perform shrink, estimated savings is 3805087976 bytes.

Allocated Space:20801650688: Used Space:16996562712: Reclaimable Space :3805087976:

SYS

Perform shrink, estimated savings is 6571518631 bytes.

Allocated Space:14791213056: Used Space:8219694425: Reclaimable Space :6571518631:

SYS

Enable row movement of the table LOCATION_ACTIVITY_DB.GUEST_CHECK_LINE_ITEM_HIST and perform shrink, estimated savings is 45914073940 bytes.

Allocated Space:236381536256: Used Space:190467462316: Reclaimable Space :45914073940:

SYS

Perform shrink, estimated savings is 369138267 bytes.

Allocated Space:571017546: Used Space:201879279: Reclaimable Space :369138267:

SYS

Perform shrink, estimated savings is 1605121139 bytes.

Allocated Space:4417690273: Used Space:2812569134: Reclaimable Space :1605121139:

SYS

Perform shrink, estimated savings is 1361873923 bytes.

Allocated Space:2797883788: Used Space:1436009865: Reclaimable Space :1361873923:

SYS

Perform shrink, estimated savings is 582892504 bytes.

Allocated Space:1954883915: Used Space:1371991411: Reclaimable Space :582892504:

SYS

Perform shrink, estimated savings is 415096816 bytes.

Allocated Space:1207959552: Used Space:792862736: Reclaimable Space :415096816:

SYS

Perform shrink, estimated savings is 18624540 bytes.

Allocated Space:873596896: Used Space:854972356: Reclaimable Space :18624540:

SYS

Perform shrink, estimated savings is 174072396 bytes.

Allocated Space:759759905: Used Space:585687509: Reclaimable Space :174072396:

SYS

Perform shrink, estimated savings is 13669512498 bytes.

Allocated Space:39972765696: Used Space:26303253198: Reclaimable Space :13669512498:

SYS

Perform shrink, estimated savings is 1608177008 bytes.

Allocated Space:6342095033: Used Space:4733918025: Reclaimable Space :1608177008:

SYS

Perform shrink, estimated savings is 1140415597 bytes.

Allocated Space:3118465024: Used Space:1978049427: Reclaimable Space :1140415597:

SYS

Perform shrink, estimated savings is 14403745 bytes.

Allocated Space:184580637: Used Space:170176892: Reclaimable Space :14403745:

SYS

Perform shrink, estimated savings is 37412954 bytes.

Allocated Space:48751120: Used Space:11338166: Reclaimable Space :37412954:

SYS

Perform shrink, estimated savings is 1579004278 bytes.

Allocated Space:2965183085: Used Space:1386178807: Reclaimable Space :1579004278:

SYS

Enable row movement of the table LOCATION_ACTIVITY_DB.CUSTOMER and perform shrink, estimated savings is 13698342 bytes.

Allocated Space:286132713: Used Space:272434371: Reclaimable Space :13698342:

SYS

Perform shrink, estimated savings is 4776212475 bytes.

Allocated Space:11581521920: Used Space:6805309445: Reclaimable Space :4776212475:

SYS

Perform shrink, estimated savings is 21775884 bytes.

Allocated Space:836745045: Used Space:814969161: Reclaimable Space :21775884:

SYS

Perform shrink, estimated savings is 83184156 bytes.

Allocated Space:142430869: Used Space:59246713: Reclaimable Space :83184156:

SYS

Perform shrink, estimated savings is 617476668 bytes.

Allocated Space:1880568733: Used Space:1263092065: Reclaimable Space :617476668:

SYS

Perform shrink, estimated savings is 1543292155 bytes.

Allocated Space:5368709120: Used Space:3825416965: Reclaimable Space :1543292155:

SYS

Perform shrink, estimated savings is 1616630802 bytes.

Allocated Space:6592115719: Used Space:4975484917: Reclaimable Space :1616630802:

SYS

Perform shrink, estimated savings is 22487826 bytes.

Allocated Space:42991616: Used Space:20503790: Reclaimable Space :22487826:

SYS

Perform shrink, estimated savings is 11368732116 bytes.

Allocated Space:34092351488: Used Space:22723619372: Reclaimable Space :11368732116:

SYS

Perform shrink, estimated savings is 6523514368 bytes.

Allocated Space:21847080960: Used Space:15323566592: Reclaimable Space :6523514368:

SYS

Perform shrink, estimated savings is 3630829978 bytes.

Allocated Space:12722198991: Used Space:9091369013: Reclaimable Space :3630829978:

SYS

Perform shrink, estimated savings is 2785864111 bytes.

Allocated Space:6525407047: Used Space:3739542936: Reclaimable Space :2785864111:

SYS

Perform shrink, estimated savings is 1548030343 bytes.

Allocated Space:6405624363: Used Space:4857594020: Reclaimable Space :1548030343:

SYS

Perform shrink, estimated savings is 1114855395 bytes.

Allocated Space:3290431488: Used Space:2175576093: Reclaimable Space :1114855395:

SYS

Perform shrink, estimated savings is 69896742 bytes.

Allocated Space:144500485: Used Space:74603743: Reclaimable Space :69896742:

SYS

Perform shrink, estimated savings is 29947353 bytes.

Allocated Space:100963207: Used Space:71015854: Reclaimable Space :29947353:

SYS

Enable row movement of the table LOCATION_ACTIVITY_DB.MENU_ITEM_DAILY_TOTAL_COM and perform shrink, estimated savings is 17011879 bytes.

Allocated Space:408362145: Used Space:391350266: Reclaimable Space :17011879:

SYS

Perform shrink, estimated savings is 138625040 bytes.

Allocated Space:528171253: Used Space:389546213: Reclaimable Space :138625040:

SYS

Perform shrink, estimated savings is 27682695 bytes.

Allocated Space:72188743: Used Space:44506048: Reclaimable Space :27682695:

SYS

Enable row movement of the table LOCATION_ACTIVITY_DB.MYFORECAST_DETAIL and perform shrink, estimated savings is 22250014 bytes.

Allocated Space:637534208: Used Space:615284194: Reclaimable Space :22250014:

SYS

Perform shrink, estimated savings is 2672724787 bytes.

Allocated Space:6951010304: Used Space:4278285517: Reclaimable Space :2672724787:

SYS

Perform shrink, estimated savings is 38011132 bytes.

Allocated Space:209098157: Used Space:171087025: Reclaimable Space :38011132:

SYS

Perform shrink, estimated savings is 1716505983 bytes.

Allocated Space:2922381312: Used Space:1205875329: Reclaimable Space :1716505983:

SYS

Perform shrink, estimated savings is 6904370274 bytes.

Allocated Space:23085449216: Used Space:16181078942: Reclaimable Space :6904370274:

SYS

Perform shrink, estimated savings is 478154588 bytes.

Allocated Space:2281701376: Used Space:1803546788: Reclaimable Space :478154588:

SYS

Perform shrink, estimated savings is 142287020 bytes.

Allocated Space:252452072: Used Space:110165052: Reclaimable Space :142287020:


49 rows selected.


SQL> select benefit_type from DBA_ADVISOR_RECOMMENDATIONS where task_name='SYS_AUTO_SPCADV_190221362014';


BENEFIT_TYPE

Perform shrink, estimated savings is 152515307 bytes.

Perform shrink, estimated savings is 338077372 bytes.

Perform shrink, estimated savings is 972566831 bytes.

Enable row movement of the table LOCATION_ACTIVITY_DB.MENU_ITEM_FIXED_PERIOD_TOTAL and perform shrink, estimated savings is 7174778795 bytes.

Perform shrink, estimated savings is 1695011163 bytes.

Perform shrink, estimated savings is 26915027 bytes.

Perform shrink, estimated savings is 186165922 bytes.

Perform shrink, estimated savings is 3805087976 bytes.

Perform shrink, estimated savings is 6571518631 bytes.

Enable row movement of the table LOCATION_ACTIVITY_DB.GUEST_CHECK_LINE_ITEM_HIST and perform shrink, estimated savings is 45914073940 bytes.

Perform shrink, estimated savings is 369138267 bytes.

Perform shrink, estimated savings is 1605121139 bytes.

Perform shrink, estimated savings is 1361873923 bytes.

Perform shrink, estimated savings is 582892504 bytes.

Perform shrink, estimated savings is 415096816 bytes.

Perform shrink, estimated savings is 18624540 bytes.

Perform shrink, estimated savings is 174072396 bytes.

Perform shrink, estimated savings is 13669512498 bytes.

Perform shrink, estimated savings is 1608177008 bytes.

Perform shrink, estimated savings is 1140415597 bytes.

Perform shrink, estimated savings is 14403745 bytes.

Perform shrink, estimated savings is 37412954 bytes.

Perform shrink, estimated savings is 1579004278 bytes.

Enable row movement of the table LOCATION_ACTIVITY_DB.CUSTOMER and perform shrink, estimated savings is 13698342 bytes.

Perform shrink, estimated savings is 4776212475 bytes.

Perform shrink, estimated savings is 21775884 bytes.

Perform shrink, estimated savings is 83184156 bytes.

Perform shrink, estimated savings is 617476668 bytes.

Perform shrink, estimated savings is 1543292155 bytes.

Perform shrink, estimated savings is 1616630802 bytes.

Perform shrink, estimated savings is 22487826 bytes.

Perform shrink, estimated savings is 11368732116 bytes.

Perform shrink, estimated savings is 6523514368 bytes.

Perform shrink, estimated savings is 3630829978 bytes.

Perform shrink, estimated savings is 2785864111 bytes.

Perform shrink, estimated savings is 1548030343 bytes.

Perform shrink, estimated savings is 1114855395 bytes.

Perform shrink, estimated savings is 69896742 bytes.

Perform shrink, estimated savings is 29947353 bytes.

Enable row movement of the table LOCATION_ACTIVITY_DB.MENU_ITEM_DAILY_TOTAL_COM and perform shrink, estimated savings is 17011879 bytes.

Perform shrink, estimated savings is 138625040 bytes.

Perform shrink, estimated savings is 27682695 bytes.

Enable row movement of the table LOCATION_ACTIVITY_DB.MYFORECAST_DETAIL and perform shrink, estimated savings is 22250014 bytes.

Perform shrink, estimated savings is 2672724787 bytes.

Perform shrink, estimated savings is 38011132 bytes.

Perform shrink, estimated savings is 1716505983 bytes.

Perform shrink, estimated savings is 6904370274 bytes.

Perform shrink, estimated savings is 478154588 bytes.

Perform shrink, estimated savings is 142287020 bytes.

49 rows selected.

SQL> select command,attr1,attr2,attr3 from DBA_ADVISOR_ACTIONS where TASK_NAME='SYS_AUTO_SPCADV_190221362014';


COMMAND

ATTR1

ATTR2

ATTR3

SHRINK SPACE

alter table "PORTALDB"."PORTAL_WEATHER" shrink space

alter table "PORTALDB"."PORTAL_WEATHER" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."KPI_JC_FIXED_PERIOD_TOTAL" shrink space

alter table "LOCATION_ACTIVITY_DB"."KPI_JC_FIXED_PERIOD_TOTAL" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."TRANSACTION_KEY" shrink space

alter table "LOCATION_ACTIVITY_DB"."TRANSACTION_KEY" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."MENU_ITEM_FIXED_PERIOD_TOTAL" shrink space

alter table "LOCATION_ACTIVITY_DB"."MENU_ITEM_FIXED_PERIOD_TOTAL" shrink space COMPACT

alter table "LOCATION_ACTIVITY_DB"."MENU_ITEM_FIXED_PERIOD_TOTAL" enable row movement

SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XPKTRANSACTION_KEY" shrink space

alter index "LOCATION_ACTIVITY_DB"."XPKTRANSACTION_KEY" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."MYFORECAST_DAY_PART_TOTAL" shrink space

alter table "LOCATION_ACTIVITY_DB"."MYFORECAST_DAY_PART_TOTAL" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."DELIVERY_DETAIL" shrink space

alter table "LOCATION_ACTIVITY_DB"."DELIVERY_DETAIL" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."GUEST_CHECK_HIST" shrink space

alter table "LOCATION_ACTIVITY_DB"."GUEST_CHECK_HIST" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XTRMENU_ITEM_FP_TOTAL" shrink space

alter index "LOCATION_ACTIVITY_DB"."XTRMENU_ITEM_FP_TOTAL" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."GUEST_CHECK_LINE_ITEM_HIST" shrink space

alter table "LOCATION_ACTIVITY_DB"."GUEST_CHECK_LINE_ITEM_HIST" shrink space COMPACT

alter table "LOCATION_ACTIVITY_DB"."GUEST_CHECK_LINE_ITEM_HIST" enable row movement

SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."GUEST_CHECK_LINE_ITEM" shrink space

alter table "LOCATION_ACTIVITY_DB"."GUEST_CHECK_LINE_ITEM" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."OPERATIONS_FIXED_PERIOD_TOTAL" shrink space

alter table "LOCATION_ACTIVITY_DB"."OPERATIONS_FIXED_PERIOD_TOTAL" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."JOB_CODE_FIXED_PERIOD_TOTAL" shrink space

alter table "LOCATION_ACTIVITY_DB"."JOB_CODE_FIXED_PERIOD_TOTAL" shrink space COMPACT


SHRINK SPACE

alter index "PORTALDB"."XN1PORTAL_WEATHER" shrink space

alter index "PORTALDB"."XN1PORTAL_WEATHER" shrink space COMPACT


SHRINK SPACE

alter index "PORTALDB"."XN2PORTAL_WEATHER" shrink space

alter index "PORTALDB"."XN2PORTAL_WEATHER" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."MENU_ITEM_PRICE" shrink space

alter table "LOCATION_ACTIVITY_DB"."MENU_ITEM_PRICE" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."NON_SALES_DETAIL" shrink space

alter table "LOCATION_ACTIVITY_DB"."NON_SALES_DETAIL" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XN2GUEST_CHECK_LINE_ITEM_HIST" shrink space

alter index "LOCATION_ACTIVITY_DB"."XN2GUEST_CHECK_LINE_ITEM_HIST" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."LABOR_CATEGORY_FP_TOTAL" shrink space

alter table "LOCATION_ACTIVITY_DB"."LABOR_CATEGORY_FP_TOTAL" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XN1GUEST_CHECK_HIST" shrink space

alter index "LOCATION_ACTIVITY_DB"."XN1GUEST_CHECK_HIST" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."MENU_ITEM" shrink space

alter table "LOCATION_ACTIVITY_DB"."MENU_ITEM" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."GUEST_CHECK" shrink space

alter table "LOCATION_ACTIVITY_DB"."GUEST_CHECK" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XTRKPI_JC_FP_TOTAL" shrink space

alter index "LOCATION_ACTIVITY_DB"."XTRKPI_JC_FP_TOTAL" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."CUSTOMER" shrink space

alter table "LOCATION_ACTIVITY_DB"."CUSTOMER" shrink space COMPACT

alter table "LOCATION_ACTIVITY_DB"."CUSTOMER" enable row movement

SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XN1MENU_ITEM_FP_TOTAL" shrink space

alter index "LOCATION_ACTIVITY_DB"."XN1MENU_ITEM_FP_TOTAL" shrink space COMPACT


SHRINK SPACE

alter table "COREDB"."CORE_LOG" shrink space

alter table "COREDB"."CORE_LOG" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XTRGUEST_CHECK_LINE_ITEM" shrink space

alter index "LOCATION_ACTIVITY_DB"."XTRGUEST_CHECK_LINE_ITEM" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."TENDER_MEDIA_FP_TOTAL" shrink space

alter table "LOCATION_ACTIVITY_DB"."TENDER_MEDIA_FP_TOTAL" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XCNLABOR_CAT_FP_TOT" shrink space

alter index "LOCATION_ACTIVITY_DB"."XCNLABOR_CAT_FP_TOT" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."INVENTORY_ITEM_DAILY_TOTAL" shrink space

alter table "LOCATION_ACTIVITY_DB"."INVENTORY_ITEM_DAILY_TOTAL" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XN5GUEST_CHECK_LINE_ITEM" shrink space

alter index "LOCATION_ACTIVITY_DB"."XN5GUEST_CHECK_LINE_ITEM" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XTRGUEST_CHECK_LINE_ITEM_HIST" shrink space

alter index "LOCATION_ACTIVITY_DB"."XTRGUEST_CHECK_LINE_ITEM_HIST" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XN4GUEST_CHECK_LINE_ITEM_HIST" shrink space

alter index "LOCATION_ACTIVITY_DB"."XN4GUEST_CHECK_LINE_ITEM_HIST" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."EMPLOYEE_DP_MENU_ITEM_TOTAL" shrink space

alter table "LOCATION_ACTIVITY_DB"."EMPLOYEE_DP_MENU_ITEM_TOTAL" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."CASH_MGMT_DETAIL" shrink space

alter table "LOCATION_ACTIVITY_DB"."CASH_MGMT_DETAIL" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."MENU_ITEM_DAILY_TOTAL" shrink space

alter table "LOCATION_ACTIVITY_DB"."MENU_ITEM_DAILY_TOTAL" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XAKJOB_CODE_FP_TOTAL" shrink space

alter index "LOCATION_ACTIVITY_DB"."XAKJOB_CODE_FP_TOTAL" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XN1GUEST_CHECK_LINE_ITEM" shrink space

alter index "LOCATION_ACTIVITY_DB"."XN1GUEST_CHECK_LINE_ITEM" shrink space COMPACT


SHRINK SPACE

alter index "AGGREGATE_DB"."XN5AGG_IC_CUSTOMER_ACCOUNT" shrink space

alter index "AGGREGATE_DB"."XN5AGG_IC_CUSTOMER_ACCOUNT" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."MENU_ITEM_DAILY_TOTAL_COM" shrink space

alter table "LOCATION_ACTIVITY_DB"."MENU_ITEM_DAILY_TOTAL_COM" shrink space COMPACT

alter table "LOCATION_ACTIVITY_DB"."MENU_ITEM_DAILY_TOTAL_COM" enable row movement

SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XCNMENU_ITEM_PRICE" shrink space

alter index "LOCATION_ACTIVITY_DB"."XCNMENU_ITEM_PRICE" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XTROPS_FP_TOTAL_COM" shrink space

alter index "LOCATION_ACTIVITY_DB"."XTROPS_FP_TOTAL_COM" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."MYFORECAST_DETAIL" shrink space

alter table "LOCATION_ACTIVITY_DB"."MYFORECAST_DETAIL" shrink space COMPACT

alter table "LOCATION_ACTIVITY_DB"."MYFORECAST_DETAIL" enable row movement

SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XAKMENU_ITEM_DAILY_TOTAL" shrink space

alter index "LOCATION_ACTIVITY_DB"."XAKMENU_ITEM_DAILY_TOTAL" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."INVENTORY_TRANSFER_LOG" shrink space

alter table "LOCATION_ACTIVITY_DB"."INVENTORY_TRANSFER_LOG" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XTRJOB_CODE_FP_TOTAL" shrink space

alter index "LOCATION_ACTIVITY_DB"."XTRJOB_CODE_FP_TOTAL" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XPKGUEST_CHECK_LINE_ITEM_HIST" shrink space

alter index "LOCATION_ACTIVITY_DB"."XPKGUEST_CHECK_LINE_ITEM_HIST" shrink space COMPACT


SHRINK SPACE

alter index "LOCATION_ACTIVITY_DB"."XCNJOB_CODE_FP_TOTAL" shrink space

alter index "LOCATION_ACTIVITY_DB"."XCNJOB_CODE_FP_TOTAL" shrink space COMPACT


SHRINK SPACE

alter table "LOCATION_ACTIVITY_DB"."WEBSERVICE_ACCESS_LOG" shrink space

alter table "LOCATION_ACTIVITY_DB"."WEBSERVICE_ACCESS_LOG" shrink space COMPACT


49 rows selected.