Data and Database: tablespace

Showing posts with label tablespace. Show all posts

Friday, 3 November 2017

Online Move of a Table in Oracle Database 12c Release 2 (12.2)

Oracle Database 12c Release 1 (12.1) introduced the ability to move table partitions and sub-partitions online. In Oracle Database 12c Release 2 (12.2) you can now perform an online move of a table, as well as individual partitions and sub-partitions.

MOVE ONLINE

The following example shows how to online move a table.

-- Create table.

DROP TABLE t1 PURGE;

CREATE TABLE t1

(id            NUMBER,

 description   VARCHAR2(50),

 created_date  DATE,

 CONSTRAINT t1_pk PRIMARY KEY (id)

);

-- Populate it.

INSERT INTO t1

SELECT level,

       'Description for ' || level,

       CASE

         WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2015', 'DD/MM/YYYY')

         ELSE TO_DATE('01/07/2016', 'DD/MM/YYYY')

END

FROM   dual

CONNECT BY level <= 1000;

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 't1');

We can now move the table using the ONLINE keyword.

-- Basic move.

ALTER TABLE t1 MOVE ONLINE TABLESPACE users;

-- Change table compression.

ALTER TABLE t1 MOVE ONLINE TABLESPACE users COMPRESS UPDATE INDEXES;

ALTER TABLE t1 MOVE ONLINE TABLESPACE users NOCOMPRESS UPDATE INDEXES;

-- Change storage parameters.

ALTER TABLE t1 MOVE ONLINE STORAGE (PCTINCREASE 0);

-------------xxxxxxxxxxxxx--------------------------------------

I check the state of the table index and note that it remains valid

select index_name, table_name, status from user_indexes where table_name='T1';

INDEX_NAME TABLE_NAME    STATUS
-------------------- -------------------- --------
I_MOVE1    T1             VALID


Here  I rebuild the index online in the tablespace USERS2

alter index i_move1 rebuild online tablespace users2;

I check the segments again

select segment_name, segment_type, tablespace_name from user_segments;

 SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------

I_MOVE1       INDEX          USERS2
T1            TABLE          USERS2

Table and index moved to the USERS2 tablespace without generating unavailability

for the application using the new MOVE ONLINE feature of Oracle Database 12cR2

Restrictions

There are some restrictions associated with online moves of tables described here.

It can't be combined with any other clause.
It can't be used on a partitioned index-organized table or index-organized tables that have a column defined as a LOB, VARRAY, Oracle-supplied type, or user-defined object type.
It can't be used if there is a domain index on the table.

Parallel DML and direct path inserts are not supported against an object with an ongoing online move.

Reclaiming Unused Space in Datafiles : Part -2

Here is Reclaiming Unused Space in Datafiles : Part - 1

There are a number of scenarios that can lead to unused space in datafiles. The two most common I see are:

A lack of housekeeping/maintenance means that one or more tables have grown excessively. After the data is pruned the datafiles contain unused space that needs to be reclaimed.
One or more segments (tables, partitions or indexes) have been moved to another tablespace leaving empty areas in the datafiles that previously held them.

Here is Reclaiming Unused Space in Datafiles : Part - 1
where these were discussed

Setup Test Environment

Identify Tablespaces with Free Space

Export/Import

Manual Tablespace Reorganization

Enterprise Manager Tablespace Reorganization

In this article I will discuss a few of the ways of reclaiming this unused space

Shrink?
Tablespaces with Multiple Datafiles
Undo Tablespace
Temp Tablespace
SYSAUX Tablespace

Shrink?

If there is only a single object in the datafile, it's possible a online shrink operation will actually be enough to compact the data and free up the empty blocks, allowing the datafile to be resized to a smaller size.

Remember, the shrink only compacts the data. It doesn't guarantee the blocks will be placed at the front of the datafile, so it's possible you will have blocks at the end of the datafile, which means the datafile can't be resized smaller. If this is the case you will need to use one of the methods mentioned previously.

Tablespaces with Multiple Datafiles

So far I've conveniently side-stepped the issue of tablespaces with multiple datafiles. Why? Because it makes the tablespace extent map a little more confusing to look at. To see what I mean, perform the setup again, but this time before building the tables add an additional datafile to the tablespace.

ALTER TABLESPACE reclaim_ts ADD DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim02.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;

With the tables built, populated and the "T1" table truncated, the extent map will look something like this.

The problem here is we can't tell what files the gaps are in without hovering over the gaps and reading the tool tip. That's not very simple when we have lots of files, segments and gaps to contend with. At this point I stop looking at the extent map and just use a script to identify all the gaps in the tablespace, or individual datafiles.

The ts_extent_map.sql script produces a list of all the free space in the tablespace or individual datafile. The combined output and separate output for each datafile is shown below.

SQL> @ts_extent_map reclaim_ts all

Tablespace Block Size (bytes): 8192

*** GAP *** (23 -> 896) FileID=8 Blocks=872 Size(MB)=6.81

*** GAP *** (959 -> 1024) FileID=8 Blocks=64 Size(MB)=.5

*** GAP *** (127 -> 768) FileID=9 Blocks=640 Size(MB)=5

Total Gap Blocks: 1576

Total Gap Space (MB): 12.31

SQL> @ts_extent_map reclaim_ts 8

Tablespace Block Size (bytes): 8192

*** GAP *** (23 -> 896) FileID=8 Blocks=872 Size(MB)=6.81

*** GAP *** (959 -> 1024) FileID=8 Blocks=64 Size(MB)=.5

Total Gap Blocks: 936

Total Gap Space (MB): 7.31

SQL> @ts_extent_map reclaim_ts 9

Tablespace Block Size (bytes): 8192

*** GAP *** (127 -> 768) FileID=9 Blocks=640 Size(MB)=5

Total Gap Blocks: 640

Total Gap Space (MB): 5

SQL>

This can help you make a judgment as to whether a tablespace reorganization is necessary.

Undo Tablespace

The simplest way to reclaim space from the undo tablespace is to create a new undo tablespace, make it the database undo tablespace and drop the old tablespace. In the following example I've used autoextend, but you may wish to remove this if you want manual control over the datafile size.

CREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/app/oracle/oradata/DB11G/undotbs02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1M;

ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Remember, flashback operations requiring undo will not be possible because you have deleted the retained undo. You may want to consider your UNDO_RETENTION parameter setting.

Temp Tablespace

If you are using oracle 11g, you can shrink a temporary tablespace using the ALTER TABLESPACE command.

If you are using a database version prior to 11g, reducing the size of the temporary tablespace is similar to reclaiming space from the undo tablespace. Create a new temp tablespace, move the users on to it, then drop the old temp tablespace.

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u01/app/oracle/oradata/DB11G/temp02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- Switch all existing users to new temp tablespace.

BEGIN

  FOR cur_user IN (SELECT username FROM dba_users WHERE temporary_tablespace = 'TEMP') LOOP

    EXECUTE IMMEDIATE 'ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE temp2';

  END LOOP;

END;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

SYSAUX Tablespace

The SYSAUX tablespace is similar to any other tablespace, so the techniques discussed in this article will work on the SYSAUX tablespace too. If you've moved some of the occupants to a new tablespace, you may want to reduce the size of the SYSAUX tablespace to recovery the room you've saved. Options include the following.

Online shrink of objects towards the end of the datafile may allow you to reduce the datafile size.
Move the SYSAUX objects with extents towards the end of the datafile to a temporary tablespace, reduce the datafile size and move them back.

Data and Database