/* Formatted on 2009/12/30 20:36 (Formatter Plus v4.8.7) */
CREATE TABLE sourceTable(
column_num NUMBER(18,0),
column_chr VARCHAR2(2)
)
PARTITION BY RANGE (column_num)
(
PARTITION tab_h1 VALUES LESS THAN
(11),
PARTITION tab_h2 VALUES LESS THAN
(21)
);
--Insert test Records
INSERT INTO sourceTable
SELECT ROWNUM, somecode
FROM (SELECT ROWNUM noofrows,'TD' somecode
FROM dba_objects)
WHERE noofrows < 21;
COMMIT ;
--Check no of records in Table
SELECT count(*)
FROM sourceTable;
SELECT *
FROM sourceTable;
--Create test Table 2
CREATE TABLE DestinationTable(
column_num NUMBER(18,0),
column_chr VARCHAR2(2)
);
--check step 1
ALTER TABLE sourceTable
EXCHANGE PARTITION tab_h1
WITH TABLE DestinationTable;
--or check step 2
ALTER TABLE sourceTable
EXCHANGE PARTITION tab_h2
WITH TABLE DestinationTable;
SELECT *
FROM sourcetable;
SELECT *
FROM DestinationTable;
--drop test tables
DROP TABLE sourceTable;
DROP TABLE destinationTable;
Many time we have done “create table
7 comments:
Hi There I'd love to congratulate you for such a great made forum!
Was thinking this would be a perfect way to introduce myself!
Sincerely,
Monte Phil
if you're ever bored check out my site!
[url=http://www.partyopedia.com/articles/bowling-party-supplies.html]bowling Party Supplies[/url].
Do we need to re-build indexes after that ? Does Sqlserver have local index concept ?
Wasim Iqbal
Hi Wasim,
you are totally correct after this process we need to REBUILD our index. because after finish this process all index become UNUSABLE State.
Check this,
--
SELECT index_name, index_type, table_name, status
FROM user_indexes
WHERE index_name='your index name';
--
Solution,
1. Drop the specified index and/or recreate the index
2. Rebuild the specified index
sql server 2005 and latest versions support Partitioned Tables and Indexes concept.
http://msdn.microsoft.com/en-us/library/ms345146%28SQL.90%29.aspx
Thank you
If on oracle use the data pump. Great tool to move data to different enviroments, like getting parts of production data to test environment.
Music is therapy. Music moves people. It connects people in ways that no other medium can. It pulls heart strings. It acts as medicine. See the link below for more info.
#moves
www.matreyastudios.com
Thank you for this post. Keep it up. Hope to read more post from you guys.
Viena
www.gofastek.com
It is great to have the opportunity to read a good quality article with useful information on topics that plenty are interested on.
www.imarksweb.org
Post a Comment