Log in
updated 11:29 AM UTC, May 4, 2016

Move table and dependent LOB to other tablespace

Requirement :-

User: RCAADMIN9
  • Create  tablespace DATA_SWAP_RCA9 with datafile DATA_SWAP_RCA9_01.dbf  
  • Create  tablespace LOB_SWAP_RCA9 with datafile LOB_SWAP_RCA9_01.dbf  
  • Move SWAP_RCA9 table from tablespace DATA_DEMO_RCA9 to tablespace DATA_SWAP_RCA9   
  • and LOB columns to LOB_SWAP_RCA9 

Here are the steps for implementation and validation:-


col FILE_NAME for a70

set long 999999

col COLUMN_NAME for a20

col TABLE_NAME for a20

set lines 300

 

SQL> select tablespace_name, file_name from dba_data_files where tablespace_name='DATA_SWAP_RCA9';

no rows selected

 

create tablespace DATA_SWAP_RCA9 datafile '/database/oradata6/DATA_SWAP_RCA9_01.dbf' size 1G autoextend on ;

create tablespace LOB_SWAP_RCA9 datafile '/database/oradata6/LOB_SWAP_RCA9_01.dbf' size 10G autoextend on ;

 

 

check the ddl of SWAP_RCA9


 

SELECT DBMS_METADATA.GET_DDL('TABLE','SWAP_RCA9','RCAADMIN9') FROM dual ;

 

 

 

Move tables & LOB :-

alter table  RCAADMIN9.SWAP_RCA9 move tablespace DATA_SWAP_RCA9;

ALTER TABLE  RCAADMIN9.SWAP_RCA9 MOVE  LOB ("B_DATA") STORE AS (TABLESPACE LOB_SWAP_RCA9);

 
Validation :-
 

select tablespace_name, file_name ,bytes/1024/1024 "in MB" from dba_data_files where tablespace_name='DATA_SWAP_RCA9';

TABLESPACE_NAME                FILE_NAME                                                                   in MB

------------------------------ ---------------------------------------------------------------------- ----------

DATA_SWAP_RCA9                 /database/oradata6/DATA_SWAP_RCA9_01.dbf                               1024

 

select tablespace_name, file_name ,bytes/1024/1024 "in MB" from dba_data_files where tablespace_name='LOB_SWAP_RCA9';


select segment_name,tablespace_name, segment_name ,segment_type, bytes/1024/1024 "in MB" from dba_segments

where segment_name  ='SWAP_RCA9';

 

SEGMENT_NAME         TABLESPACE_NAME                SEGMENT_NAME         SEGMENT_TYPE            in MB

-------------------- ------------------------------ -------------------- ------------------ ----------

SWAP_RCA9            DATA_SWAP_RCA9                 SWAP_RCA9            TABLE                   .0625

 

select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs

where table_name='SWAP_RCA9'

;

OWNER                          TABLE_NAME           COLUMN_NAME          SEGMENT_NAME                   TABLESPACE_NAME

------------------------------ -------------------- -------------------- ------------------------------ ------------------------------

RCAADMIN9                      SWAP_RCA9            B_DATA               SYS_LOB0000102097C00003$$      LOB_SWAP_RCA9

 

SELECT DBMS_METADATA.GET_DDL('TABLE','SWAP_RCA9','RCAADMIN9') FROM dual ;

Add a comment (1)

  • Written by Manjunatha Srinivas
  • Category: Database
  • Hits: 1249

Local Indexes for Partitioning

SELECT i.table_name,
  i.index_name,
  ip.partition_name,
  ip.NUM_ROWS,
  ip.blevel,
  ip.LAST_ANALYZED,
  i.status  AS index_status,
  ip.status AS partition_status
FROM user_indexes i
JOIN user_ind_partitions ip
ON (i.index_name = ip.index_name )
where table_name ='&tbl'
ORDER BY i.table_name,
  i.index_name,
  ip.partition_name;


If partition_status is "USABLE" then everything is ok. You don't need to rebuild anything.

If partition_status is "UNUSABLE" then you can rebuild the unusable partition by doing:

ALTER INDEX Index Name
REBUILD PARTITION partition name;
 
-- to confirm that indexes are global or local 
select Index_name,LOCALITY FROM  ALL_PART_INDEXES 
where  table_name='table_name'  ;
 
select INDEX_NAME, UNIQUENESS, TABLESPACE_NAME,NUM_ROWS, DISTINCT_KEYS 
from user_indexes
where table_name='table_name'
; 
 

To gather stats of the partition of the local index

 
  SELECT i.table_name,
  ip.index_name,
  ip.partition_name,
  ip.NUM_ROWS,
  ip.DISTINCT_KEYS,
  ip.HIGH_VALUE,
  IP.PARTITION_POSITION "POS",  
  ip.blevel,
  ip.LAST_ANALYZED,
  --ip.status  AS index_status,
  ip.status AS partition_status
FROM user_indexes i
JOIN user_ind_partitions ip
ON (i.index_name = ip.index_name )
where table_name ='&tbl'
--AND PARTITION_POSITION=12
ORDER BY i.table_name,
  i.index_name,
  ip.partition_name;
 
  EXEC  DBMS_STATS.GATHER_INDEX_STATS('USER','INDNAME','PART_NAME',20);  

Add a comment (2)

  • Written by Manjunatha Srinivas
  • Category: Database
  • Hits: 1271

How to install Couchdb in Ubuntu 13.04

Hi Guys,

Since you are trying to find out how to install couchdb, you must have already researched about Couchdb. 

Its pretty easy to install couchdb using ubuntu package manager, no need to compile from source or anything.

just type in this command for prosperity :)

sudo apt-get install couchdb

To test if the couchdb has installed properly.

Just try the URL : http://localhost:5984

it should return something similar to:

{"couchdb":"Welcome","version":"1.0.1"}

 

Add a comment (1)

  • Written by Madhu V Rao
  • Category: Database
  • Hits: 2042

How to connect remotely to Postgresql database 9.1

In-order to connect to a remote postgresql database , the following changes have to be done on the postgresql side:

1) Connect to the System where Postgresql is installed. (Use SSH or login directly to the server using username postgres).

2) Enable the client authentication by editing the file pg_hba.conf ( please check the file '/etc/postgresql/9.1/main/postgresql.conf' for the location of the pg_hba.conf )

vi /etc/postgresql/9.1/main/pg_hba.conf

Add the following configuration (replace Your_IP with your actual IP address)

host all all Your_IP/24 trust

3) Enable networking for Postgresql by editing the file:

vi /etc/postgresql/9.1/main/postgresql.conf

search for 'listen_addresses' and uncomment it (if its commented) and modify to look it as below: 

listen_addresses='*'

4) Restart the postgresql server by using command:

sudo service postgresql restart

OR

/etc/init.d/postgresql restart

5) Test your set-up. Connect to Remote database using pg_admin on your client system. Or use psql to connect to it.

 Note:

In case you are wondering how to check the Port number on which your postgresql is running, just type in command:

  pg_lsclusters

You will get output like below:

Version Cluster Port Status Owner Data directory ...       
9.1     main      5433 online postgres /mnt/data              ....            

That's it. Now you can connect remotely to your postgresql.

Add a comment (2)

  • Written by Madhu V Rao
  • Category: Database
  • Hits: 2535

How to Install Postgresql on Ubuntu 12.10

Postgresql is one of the most advanced open source database. It is known for its Data consistency and integrity. Its fully ACID (Atomicity , Consistency , Isolation, Durability) compliant. Enough of praising Postgresql, let's get back to installation procedure now :)

Ubuntu12.10 cache currently provides postgresql 9.1 version.  you can check the versions available in the Ubuntu cache by typing in the command:

apt-cache search postgresql-9*

Run following command on your terminal:

sudo apt-get install postgresql-9.1

if your system is not upto date , the installation might fail.

No need to worry, just update your Ubuntu packages with command:

sudo apt-get update

Once the postgresql is installed you can login to sql interface by typing in command:

sudo -u postgres psql

once logged in - set a password for Postgres role

type in \password

and enter your new password , enter \q to exit from the sql interface.

If you are managing the Postgresql from a different workstation , you have to enable remote access.

Please find it in the article: How to connect remotely to Postgresql database 9.1

 

Add a comment (2)

  • Written by Madhu V Rao
  • Category: Database
  • Hits: 2998
Follow Us on Twitter
Find Us on Facebook
Follow Us on Google
Follow Us on Pinterest