Re: Storage Claus on Partitioned Global Index
Date: Tue, 17 Nov 2009 21:21:18 +0000 (UTC)
Message-ID: <pan.2009.11.17.21.21.17_at_email.here.invalid>
On Tue, 17 Nov 2009 12:17:56 -0800, Steve Howard wrote:
> This is interesting, because I thought *new* partitions without a
> default tablespace would go into USERS, but that doesn't appear to be
> the case (in 10.2.0.4 at least)...
As far as I can remember, the OP had 2 partitions, each with a tablespace specification.
>
> SQL> create table t1117(c number)
> 2 tablespace users
> 3 partition by range(c)
> 4 (partition p1 values less than (2), 5 partition p2 values
> less than (3), 6 partition p_max values less than (maxvalue)) 7
> /
>
> Table created.
>
> SQL> create index i1117 on t1117(c)
> 2 tablespace users
> 3 global partition by range(c)
> 4 (partition p1 values less than (2) tablespace tools, 5
> partition p2 values less than (3) tablespace tools, 6 partition
> p_max values less than (maxvalue) tablespace
> tools)
> 7 /
>
> Index created.
>
> SQL> select tablespace_name from user_ind_partitions where index_name =
> 'I1117'
> 2 /
>
> TABLESPACE_NAME
> ------------------------------
> TOOLS
> TOOLS
> TOOLS
>
> SQL> alter table t1117 split partition p_max at (4) into ( partition p3,
> partition p_max)
> 2 /
>
> Table altered.
>
> SQL> alter index i1117 split partition p_max at (4) into ( partition p3,
> partition p_max)
> 2 /
>
> Index altered.
>
> SQL> select tablespace_name from user_ind_partitions where index_name =
> 'I1117'
> 2 /
>
> TABLESPACE_NAME
> ------------------------------
> TOOLS
> TOOLS
> TOOLS
> TOOLS
>
> Also, the default tablespace for the user is USERS.
>
> Regards,
>
> Steve
I believe that you've just uncovered a "feature". Let me check for 11.2:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
create table t1117(c number)
tablespace users
partition by range(c)
(partition p1 values less than (2),
partition p2 values less than (3),
partition p_max values less than (maxvalue))
6 7 /
Table created.
Elapsed: 00:00:00.27
SQL> connect system_at_ora11
Enter password:
Connected.
SQL> alter user scott quota unlimited on tools;
User altered.
Elapsed: 00:00:00.11
SQL> connect scott/tiger_at_ora11
Connected.
create index i1117 on t1117(c)
tablespace users
global partition by range(c)
(partition p1 values less than (2) tablespace tools,
partition p2 values less than (3) tablespace tools,
partition p_max values less than (maxvalue) tablespace
tools)
8 /
Index created.
Elapsed: 00:00:00.19
select tablespace_name from user_ind_partitions where index_name
2 = 'I1117';
TABLESPACE_NAME
TOOLS
TOOLS
TOOLS Elapsed: 00:00:00.16
alter table t1117 split partition p_max at (4) into ( partition 2 p3, partition p_max);
Table altered.
Elapsed: 00:00:00.27
select tablespace_name from user_ind_partitions where index_name
2 = 'I1117';
TABLESPACE_NAME
TOOLS
TOOLS
TOOLS Elapsed: 00:00:00.12
SQL> connect system_at_ora11
Enter password:
Connected.
SQL> select default_tablespace from dba_users where username='SCOTT';
DEFAULT_TABLESPACE
USERS Elapsed: 00:00:00.13
SQL> It's the same result. I believe this is a "feature".
-- http://mgogala.byethost5.comReceived on Tue Nov 17 2009 - 15:21:18 CST