Home » RDBMS Server » Server Administration » default partition issue (11.2.0.1.0 Windos XP)
default partition issue [message #527251] Mon, 17 October 2011 04:28 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
How can i check whether a partition tbale have default partition?
Re: default partition issue [message #527255 is a reply to message #527251] Mon, 17 October 2011 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Didn't we already say you how to get the (high) value of partitions?

Regards
Michel
Re: default partition issue [message #527257 is a reply to message #527255] Mon, 17 October 2011 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dba_tab_partitions.high_value

Regards
Michel
Re: default partition issue [message #527292 is a reply to message #527257] Mon, 17 October 2011 06:52 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks!
Re: default partition issue [message #527301 is a reply to message #527292] Mon, 17 October 2011 07:39 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
The flowing sql can find the default partitions:

Select A.*
  From Dba_Tab_Partitions a
  Join Dba_Objects b
    On (A.Table_Owner = B.Owner And A.Table_Name = B.Object_Name And
       A.Partition_Name = B.Subobject_Name)
 Where B.Object_Id In (Select A.Obj#
                         From Sys.Tabpartv$ a
                         Join Sys.Obj$ b
                           On (A.Obj# = B.Obj#)
                         Join Sys.User$ c
                           On (B.Owner# = C.User#)
                        Where Upper(Sys.Dbms_Metadata_Util.Long2varchar(10,
                                                                        'SYS.TABPARTV$',
                                                                        'HIBOUNDVAL',
                                                                        A.Rowid)) =
                              'DEFAULT'
                          And C.Name = 'HXL' --schema name)

Re: default partition issue [message #527309 is a reply to message #527301] Mon, 17 October 2011 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure?

SQL> Select A.*
  2    From Dba_Tab_Partitions a
  3    Join Dba_Objects b
  4      On (A.Table_Owner = B.Owner And A.Table_Name = B.Object_Name And
  5         A.Partition_Name = B.Subobject_Name)
  6   Where B.Object_Id In (Select A.Obj#
  7                           From Sys.Tabpartv$ a
  8                           Join Sys.Obj$ b
  9                             On (A.Obj# = B.Obj#)
 10                           Join Sys.User$ c
 11                             On (B.Owner# = C.User#)
 12                          Where Upper(Sys.Dbms_Metadata_Util.Long2varchar(10,
 13                                                                          'SYS.TABPARTV$',
 14                                                                          'HIBOUNDVAL',
 15                                                                          A.Rowid)) =
 16                                'DEFAULT'
 17                            And C.Name = 'HXL' --schema name)
 18  
SQL> /
                          And C.Name = 'HXL' --schema name)
                                                          *
ERROR at line 17:
ORA-00907: missing right parenthesis

Regards
Michel
Re: default partition issue [message #527378 is a reply to message #527309] Mon, 17 October 2011 20:08 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,

 Select A.*
   From Dba_Tab_Partitions a
   Join Dba_Objects b
     On (A.Table_Owner = B.Owner And A.Table_Name = B.Object_Name And
        A.Partition_Name = B.Subobject_Name)
  Where B.Object_Id In (Select A.Obj#
                          From Sys.Tabpartv$ a
                          Join Sys.Obj$ b
                            On (A.Obj# = B.Obj#)
                          Join Sys.User$ c
                            On (B.Owner# = C.User#)
                         Where Upper(Sys.Dbms_Metadata_Util.Long2varchar(10,
                                                                         'SYS.TABPARTV$',
                                                                         'HIBOUNDVAL',
                                                                         A.Rowid)) =
                               'DEFAULT'
                           And C.Name = 'OSS03' --schema name
                           )
Re: default partition issue [message #527380 is a reply to message #527378] Mon, 17 October 2011 21:19 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
If you want to find a composite partition table whether there is a default subpartition,you can using the flowing sql:


SQL> create table TB_HXL_USER
  2  (
  3    STATEDATE DATE not null,
  4    PROVCODE NUMBER not null,
  5    USERNUMBER VARCHAR2(13) not null,
  6    REM1 VARCHAR2(1024),
  7    CREATE_DATE DATE,
  8    CREATE_BY NUMBER,
  9    LAST_UPDATE_DATE DATE,
 10    LAST_UPDATE_BY NUMBER
 11  )
 12  partition by range (statedate) subpartition by list(provcode)
 13  subpartition template (
 14   subpartition p1 values (1) ,
 15   subpartition p2 values (2) ,
 16   subpartition p3 values (3) ,
 17   subpartition p4 values (4) ,
 18   subpartition p5 values (5) ,
 19   subpartition p_default values (default)
 20   )
 21  (
 22    partition HXL_USER_20111117 values less than 
(TO_DATE('2011-11-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGOR
IAN'))
 23  );

Table created.

SQL> set long 10000000;

SQL> Select a.subpartition_name,a.high_value
  2    From dba_tab_subpartitions a
  3    Join Dba_Objects b
  4      On (a.Table_Owner = b.Owner
  5          And a.Table_Name = b.Object_Name
  6          And a.subPartition_Name = b.Subobject_Name
  7         )
  8   Where b.Object_Id In
  9         (Select a.Obj#
 10            From sys.Tabsubpart$ a
 11            Join Sys.Obj$ b
 12              On (a.Obj# = b.Obj#)
 13            Join Sys.User$ c
 14              On (b.Owner# = c.User#)
 15           Where Upper(Long_To_Char(a.Rowid, 'SYS', 'TABSUBPART$', 'HIBOUNDVAL'))
 16           In ('DEFAULT','MAXVALUE')
 17             And c.Name = 'HXL' --schema name
 18          )
 19     And a.table_name = 'TB_HXL_USER'
 20     /

SUBPARTITION_NAME
------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
HXL_USER_20111117_P_DEFAULT
default

[Updated on: Tue, 18 October 2011 00:56] by Moderator

Report message to a moderator

Re: default partition issue [message #527382 is a reply to message #527380] Mon, 17 October 2011 22:27 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Append function Long_To_Char:
Create Or Replace Function Long_To_Char(In_Rowid      Rowid,
                                        In_Owner      Varchar,
                                        In_Table_Name Varchar,
                                        In_Column     Varchar2)
  Return Varchar As
  Text_C1 Varchar2(32767);
  Sql_Cur Varchar2(2000);
  --
Begin
  Sql_Cur := 'select ' || In_Column || ' from
' || In_Owner || '.' || In_Table_Name || ' where rowid =
' || Chr(39) || In_Rowid || Chr(39);
  Execute Immediate Sql_Cur
    Into Text_C1;

  Text_C1 := Substr(Text_C1, 1, 4000);
  Return Text_C1;
End;
Re: default partition issue [message #527410 is a reply to message #527382] Tue, 18 October 2011 01:31 Go to previous message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your function can be hacked and SQL injected.
It should be something like:
Create Or Replace Function Long_To_Char(
   In_Rowid      Rowid,
--    In_Owner      Varchar,
   In_Table_Name Varchar, -- table name may contain a schema like SCOTT.EMP
   In_Column     Varchar2)
  Return Varchar As
  Text_C1 Varchar2(32767);
  Sql_Cur Varchar2(2000);
  --
Begin
  If regexp_replace(In_Rowid, '[A-Za-z0-9+/]') is not null then
     raise_application_error(-20000, 'Invalid rowid');
  end if;
  Sql_Cur := 
'select ' || dbms_assert.simple_sql_name(In_Column) || ' 
from ' || dbms_assert.sql_object_name(In_Table_Name) || ' 
where rowid = ''' || In_Rowid || '''';
  Execute Immediate Sql_Cur Into Text_C1;
  Text_C1 := Substr(Text_C1, 1, 4000);
  Return Text_C1;
End;

Regards
Michel

[Updated on: Tue, 18 October 2011 01:41]

Report message to a moderator

Previous Topic: diag_dest
Next Topic: ORA-01101 while creating DB
Goto Forum:
  


Current Time: Mon Jul 28 20:33:32 CDT 2014

Total time taken to generate the page: 0.11572 seconds