Filegroups

In oracle, to move objects from one tablespace to another
I can write some DDL and move the object. How do you
move, for example tables from one filegroup to another?
Only method I've come up with so far is rename the object,
recreate it in the filegroup I want it in and move any
associated data over. Is there an easier method?Yes, assuming the table has a clustered index, you can recreate this
index on the target filegroup with use of the DROP EXISTING keyword. For
example
SET QUOTED_IDENTIFIER ON
GO
CREATE CLUSTERED UNIQUE INDEX MyIndex ON MyTable(MyColumn) WITH
DROP_EXISTING ON "MyFilegroup"
Hope this helps,
Gert-Jan
"Cathy S." wrote:
> In oracle, to move objects from one tablespace to another
> I can write some DDL and move the object. How do you
> move, for example tables from one filegroup to another?
> Only method I've come up with so far is rename the object,
> recreate it in the filegroup I want it in and move any
> associated data over. Is there an easier method?|||Thanks...I figured it out.
>--Original Message--
>In oracle, to move objects from one tablespace to another
>I can write some DDL and move the object. How do you
>move, for example tables from one filegroup to another?
>Only method I've come up with so far is rename the
object,
>recreate it in the filegroup I want it in and move any
>associated data over. Is there an easier method?
>.
>|||Cathy
You can also do it from the design table window. Bring up
the table properties and change the filegroup in the drop
down option.
It pretty much does what Gert-Jan says, creates a
clustered index(although in a much more complicated way, I
captured it in profiler to see what it does, it does a
lot), even if the table does not have one (then drops it).
Regards
John

Filegroup

When a database is created, all system objects will be stored in this
default filegroup, and also the later user tables, right ?
So if I then create another filegroup as default filegroup, are all previous
user tables also move to this default filegroup ?
No. (Re)-create the clustered index on a table to move it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> When a database is created, all system objects will be stored in this
> default filegroup, and also the later user tables, right ?
> So if I then create another filegroup as default filegroup, are all previous
> user tables also move to this default filegroup ?
>
|||So how about:
When I create a databbase in EM, I also create secondary database file in
secondary file group in the dialog box.
Wiil all user tables be stored in the secondary database file in the
secondary file group ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> No. (Re)-create the clustered index on a table to move it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
previous
>
|||Current or future tables? To have current tables move to the filegroup, (re) create the tables
clustered index (as I mentioned earlier). For future tables, either specify ON <FGNAME> when you
create the table or index, or make the file groups the default filegroups for the database (see the
ALTER DATABASE command).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uCcjB8B0EHA.3808@.TK2MSFTNGP15.phx.gbl...
> So how about:
> When I create a databbase in EM, I also create secondary database file in
> secondary file group in the dialog box.
> Wiil all user tables be stored in the secondary database file in the
> secondary file group ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> previous
>

Filegroup

When a database is created, all system objects will be stored in this
default filegroup, and also the later user tables, right ?
So if I then create another filegroup as default filegroup, are all previous
user tables also move to this default filegroup ?No. (Re)-create the clustered index on a table to move it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> When a database is created, all system objects will be stored in this
> default filegroup, and also the later user tables, right ?
> So if I then create another filegroup as default filegroup, are all previous
> user tables also move to this default filegroup ?
>|||So how about:
When I create a databbase in EM, I also create secondary database file in
secondary file group in the dialog box.
Wiil all user tables be stored in the secondary database file in the
secondary file group ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> No. (Re)-create the clustered index on a table to move it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> > When a database is created, all system objects will be stored in this
> > default filegroup, and also the later user tables, right ?
> > So if I then create another filegroup as default filegroup, are all
previous
> > user tables also move to this default filegroup ?
> >
> >
>|||Current or future tables? To have current tables move to the filegroup, (re) create the tables
clustered index (as I mentioned earlier). For future tables, either specify ON <FGNAME> when you
create the table or index, or make the file groups the default filegroups for the database (see the
ALTER DATABASE command).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uCcjB8B0EHA.3808@.TK2MSFTNGP15.phx.gbl...
> So how about:
> When I create a databbase in EM, I also create secondary database file in
> secondary file group in the dialog box.
> Wiil all user tables be stored in the secondary database file in the
> secondary file group ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> > No. (Re)-create the clustered index on a table to move it.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> > news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> > > When a database is created, all system objects will be stored in this
> > > default filegroup, and also the later user tables, right ?
> > > So if I then create another filegroup as default filegroup, are all
> previous
> > > user tables also move to this default filegroup ?
> > >
> > >
> >
> >
>

Filegroup

When a database is created, all system objects will be stored in this
default filegroup, and also the later user tables, right ?
So if I then create another filegroup as default filegroup, are all previous
user tables also move to this default filegroup ?No. (Re)-create the clustered index on a table to move it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> When a database is created, all system objects will be stored in this
> default filegroup, and also the later user tables, right ?
> So if I then create another filegroup as default filegroup, are all previo
us
> user tables also move to this default filegroup ?
>|||So how about :
When I create a databbase in EM, I also create secondary database file in
secondary file group in the dialog box.
Wiil all user tables be stored in the secondary database file in the
secondary file group ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> No. (Re)-create the clustered index on a table to move it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
previous[vbcol=seagreen]
>|||Current or future tables? To have current tables move to the filegroup, (re)
create the tables
clustered index (as I mentioned earlier). For future tables, either specify
ON <FGNAME> when you
create the table or index, or make the file groups the default filegroups fo
r the database (see the
ALTER DATABASE command).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uCcjB8B0EHA.3808@.TK2MSFTNGP15.phx.gbl...
> So how about :
> When I create a databbase in EM, I also create secondary database file in
> secondary file group in the dialog box.
> Wiil all user tables be stored in the secondary database file in the
> secondary file group ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> previous
>

File Group Changes

How do i place table and index objects which resides in same Primary Group to seperate User File Groups?
How can i achieve this task through T-SQL statements instead of Enterprise Manager?
sentil
If your table has a clustered index sp you can re-create a clustered index
to specify a filegroup. The data will be followed by clustered index.
For more details please refer to the BOL
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?
|||As Uri says, when you move the clustered index, the data moves as well...
The generic syntax you would use would be
Create index .....
on myfilegroup
with Drop_Existing
Read about drop_existing in BOL under "Create Index"
You would do this for each index you wish to move...
Be aware that the t-log can grow tremendously during this process... So
either back it up during, or go to simple recovery mode (kicking everyone
else off ) do the builds, and move back to full recovery.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?
|||Hi ,
FYI, If you need to move the non clustered index as well to the new file
group you have to execute the below command for each of the non clustered
indexes.
For Non-clustered index
create index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
For CLustered index and data
create clustered index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
Command to list the table name with file group names
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.indid < 2
and i.groupid = s.groupid
Thanks
Hari
MCDBA
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:u2$dw4sUEHA.1604@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> As Uri says, when you move the clustered index, the data moves as well...
> The generic syntax you would use would be
> Create index .....
> on myfilegroup
> with Drop_Existing
>
> Read about drop_existing in BOL under "Create Index"
> You would do this for each index you wish to move...
> Be aware that the t-log can grow tremendously during this process... So
> either back it up during, or go to simple recovery mode (kicking everyone
> else off ) do the builds, and move back to full recovery.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "senthil" <senthil@.discussions.microsoft.com> wrote in message
> news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
Group
> to seperate User File Groups?
> Enterprise Manager?
>

File Group Changes

How do i place table and index objects which resides in same Primary Group t
o seperate User File Groups?
How can i achieve this task through T-SQL statements instead of Enterprise M
anager?sentil
If your table has a clustered index sp you can re-create a clustered index
to specify a filegroup. The data will be followed by clustered index.
For more details please refer to the BOL
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?|||As Uri says, when you move the clustered index, the data moves as well...
The generic syntax you would use would be
Create index .....
on myfilegroup
with Drop_Existing
Read about drop_existing in BOL under "Create Index"
You would do this for each index you wish to move...
Be aware that the t-log can grow tremendously during this process... So
either back it up during, or go to simple recovery mode (kicking everyone
else off ) do the builds, and move back to full recovery.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?|||Hi ,
FYI, If you need to move the non clustered index as well to the new file
group you have to execute the below command for each of the non clustered
indexes.
For Non-clustered index
--
create index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
For CLustered index and data
--
create clustered index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
Command to list the table name with file group names
----
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.indid < 2
and i.groupid = s.groupid
Thanks
Hari
MCDBA
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:u2$dw4sUEHA.1604@.TK2MSFTNGP12.phx.gbl...
> As Uri says, when you move the clustered index, the data moves as well...
> The generic syntax you would use would be
> Create index .....
> on myfilegroup
> with Drop_Existing
>
> Read about drop_existing in BOL under "Create Index"
> You would do this for each index you wish to move...
> Be aware that the t-log can grow tremendously during this process... So
> either back it up during, or go to simple recovery mode (kicking everyone
> else off ) do the builds, and move back to full recovery.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "senthil" <senthil@.discussions.microsoft.com> wrote in message
> news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
Group[vbcol=seagreen]
> to seperate User File Groups?
> Enterprise Manager?
>

File Group Changes

How do i place table and index objects which resides in same Primary Group to seperate User File Groups?
How can i achieve this task through T-SQL statements instead of Enterprise Manager?sentil
If your table has a clustered index sp you can re-create a clustered index
to specify a filegroup. The data will be followed by clustered index.
For more details please refer to the BOL
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?|||As Uri says, when you move the clustered index, the data moves as well...
The generic syntax you would use would be
Create index .....
on myfilegroup
with Drop_Existing
Read about drop_existing in BOL under "Create Index"
You would do this for each index you wish to move...
Be aware that the t-log can grow tremendously during this process... So
either back it up during, or go to simple recovery mode (kicking everyone
else off ) do the builds, and move back to full recovery.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?|||Hi ,
FYI, If you need to move the non clustered index as well to the new file
group you have to execute the below command for each of the non clustered
indexes.
For Non-clustered index
--
create index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
For CLustered index and data
--
create clustered index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
Command to list the table name with file group names
----
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.indid < 2
and i.groupid = s.groupid
Thanks
Hari
MCDBA
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:u2$dw4sUEHA.1604@.TK2MSFTNGP12.phx.gbl...
> As Uri says, when you move the clustered index, the data moves as well...
> The generic syntax you would use would be
> Create index .....
> on myfilegroup
> with Drop_Existing
>
> Read about drop_existing in BOL under "Create Index"
> You would do this for each index you wish to move...
> Be aware that the t-log can grow tremendously during this process... So
> either back it up during, or go to simple recovery mode (kicking everyone
> else off ) do the builds, and move back to full recovery.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "senthil" <senthil@.discussions.microsoft.com> wrote in message
> news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> >
> > How do i place table and index objects which resides in same Primary
Group
> to seperate User File Groups?
> >
> > How can i achieve this task through T-SQL statements instead of
> Enterprise Manager?
>