alter table move command

"alter table move" command


This command is generally used to move the segments of table from one tablespace to other tablespace, so
 how to use this command:

ALTER TABLE TABLE_NAME MOVE TABLESPCE tablespace_name;

after this command indexes are become invalid, in that way we need to rebuild the indexes and at same we can change the segment of indexes
how to:

(MOVE INDEX TO TABLESPACE (NOT DOMAIN INDEX / FULL TEXT INDEXES,.,,, IOT- TOP  AND LOB ARE THESE ARE THE CUISINES OF DOMAIN INDEX))

ALTER INDEX INDEX_NAME REBUID TABLESPACE TRY;

then need to MOVE LOB SEGMENT TO NEW TABLESPACE
the table_name , and column_name information can get from the user_lobs data dictionary table:

alter table table_name move lob(column_name) store as segment_name_unique ( tablespace try);

Domain index lob segment can moved by rebuild with replace parameters 

ALTER INDEX DOMAIN_INDEX_NAME REBUILD PARAMETERS('REPLACE LEXER HYPHEN_LEXER STORAGE MYSTORE');

you may also like 

for last command follow this post::

Full Text Index / Domain Index ( create datastore, assign tablespace for storage)

Space Tuning / Reclaim space from tablespace / Freeup unused space

First of all identified most wasted space tables after this we will move the table into another tablespace. For this create a new tablespace or we can move table in a preexisting tablespace with the help of  "alter table move" command.

alter table table_name move tablespace tablespace_name;

after executing this command now we need to rebuild index (with or without tablespace clause)

alter index index_name rebuild tablespace tablespace_name;

*note table should not contain Full text index or domain index
If it contains then the scenerio is :

1. Collect create index script only for domain indexes 
2. Save it at some safe place
3. Drop domain index
4. Move table with "alter table move" command as above.
5. Rebuild indexes as above
6. And Rebuild domain indexes

If you forgot to drop domain index , No problem u might got some error as below:
ORA-02327: cannot create index on expression with datatype LOB
ORA-30967: operation directly on the Path Table is disallowed
 

SQL> alter table DR$BLB_01$I move tablespace try;
alter table DR$BLB_01$I move tablespace try
            *
ERROR at line 1:
ORA-30967: operation directly on the Path Table is disallowed


SQL> alter table DR$BLB_01$K move tablespace try;

Table altered.

SQL> alter table DR$BLB_01$N move tablespace try;

Table altered.

SQL> alter table DR$BLB_01$R move tablespace try;
alter table DR$BLB_01$R move tablespace try
            *
ERROR at line 1:
ORA-30967: operation directly on the Path Table is disallowed


SQL> alter table EMP move tablespace try;

Table altered.

and indexes
SQL> alter index SYS_IL0000074265C00002$$ rebuild tablespace try;
alter index SYS_IL0000074265C00002$$ rebuild tablespace try
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

solution to this

alter table table_name move lob(column_name_having_blob_clob_datatype) store as a tablespace tablespace_name;


Read also::
short and brief description on "alter table move"

Finding USED UNUSED INDEX IN ORACLE

Finding USED UNUSED INDEX IN ORACLE

In Oracle by default index tracking / monitoring is off. In order to find unused used index we need to set "INDEX MONITORING" on.

command:
alter index index_name monitoring usage;

data will save in table "v$object_usage"

select * from v$object_usage;

This data dictionary table is independently store the schema data. To access this data dictionary table a schema/user does not need any extra grant or privileges.

Nanoseconds / Millisecond in Mysql

MySQL 5.6.4 milliseconds (fractional seconds) are supported by the newly introduced type TIMESTAMP(fsp), where fsp stands for fractional seconds precision. fsp ranges from 0 to 6 with 0 indicating there's no fractional part.

Mycurrent mysql version is 5.5.0024
after upgrade it to 5.6.0013, now i able save millisecond in database.

create table tymdate( column1 datetime(6));
insert into tymdate values ('2013-10-19 11:12:59.045673');

note*- Application users / Developers Please don't forget to update mysql j connector.

RowID Equivalent in SQLSERVER

Physical location of a row in SQL Server

Introduction

In Oracle, each row can be identified by the ROWID column. It is a pseudo column. This column contains the information about the address of row saved in datafile.

ROWNUM is quite different thing, if we compare it to ROWID, rownum to particular row will not remain fix, it gets change over any dml operation done on a table.

ROWID equivalent in sqlserver, we have  

SQLSERVER 2008 - %%physloc%%
SQLSERVER 2005 - %%lockres%%


QUERY USING PHYSLOC

select %%physloc%% from table_name;

To decode the value of above column, we can use below function
sys.fn_PhysLocFormatter



select %%physloc%% , sys.fn_physlocformatter(%%physloc%%) from table_name;

 

Result will be as below:

0xA901000001000000  (1:425:0)

How to read above format ?
row with ID = ???? is located in the file 1 on page 425 and in slot 0.

using this we can identify the actual data file of the row, using the view sys.database_files.

Some Queries

select * from table_name where %%physloc%% = 0xA901000001000000

this will return row