Home » SQL & PL/SQL » SQL & PL/SQL » partition
partition [message #336337] Fri, 25 July 2008 14:37 Go to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member
Is there anyway, can we check from any data dictionary table, the time when the partition got created on any table.

Thanks in advance
Re: partition [message #336338 is a reply to message #336337] Fri, 25 July 2008 14:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

How many time do you have to be directed to follow Posting Guidelines?

DBA|ALL|USER_OBJECTS contains your answer.
Re: partition [message #336342 is a reply to message #336338] Fri, 25 July 2008 14:48 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member
Sorry.. i will read it again, buti am not sure which point i missed.

But Thanks and apprecaite your help.
Re: partition [message #336344 is a reply to message #336337] Fri, 25 July 2008 14:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Where did you provide OS name & version; plus Oracle version to 4 decimal places?
Re: partition [message #336347 is a reply to message #336344] Fri, 25 July 2008 15:03 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member
Release 9.2.0.1.0 & Window operating system..

I don't have prviliges to see any dba_objects tables. I am unable to see the partition information on all_objects table.Even though table containing parititions.
Re: partition [message #336348 is a reply to message #336347] Fri, 25 July 2008 15:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you didn't see the partitions then either your table is not partitioned or your didn't look at the correct column (subobject_name).

Regards
Michel
Re: partition [message #336350 is a reply to message #336337] Fri, 25 July 2008 15:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

  1  select distinct object_type from dba_objects
  2* order by 1
SQL> /

OBJECT_TYPE
-------------------
CLUSTER
CONSUMER GROUP
CONTEXT
DATABASE LINK
DIRECTORY
EVALUATION CONTEXT
FUNCTION
INDEX
INDEX PARTITION
INDEXTYPE
JAVA CLASS
JAVA DATA
JAVA RESOURCE
JOB
JOB CLASS
LIBRARY
LOB
LOB PARTITION
OPERATOR
PACKAGE
PACKAGE BODY
PROCEDURE
PROGRAM
QUEUE
RESOURCE PLAN
RULE
RULE SET
SCHEDULE
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
TYPE BODY
UNDEFINED
VIEW
WINDOW
WINDOW GROUP
XML SCHEMA

>I am unable to see the partition information on all_objects table.

Too bad you refuse to follow posting guidelines.

You're on Your On (YOYO)!

Re: partition [message #336354 is a reply to message #336350] Fri, 25 July 2008 15:23 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member
Window Professional 2004 and oracle Release 9.2.0.1.0

>I am unable to see the partition information on all_objects table

select distinct(object_type) from all_objects

CONSUMER GROUP
DIRECTORY
EVALUATION CONTEXT
FUNCTION
INDEXTYPE
JAVA CLASS
JAVA RESOURCE
LIBRARY
OPERATOR
PACKAGE
PROCEDURE
SEQUENCE
SYNONYM
TABLE
TYPE
VIEW


No information for parititions.. Although table is partitions in database..

select table_name, partition_name from all_tab_partitions
table_name              Partition_name
AB_TYPES	        P20080315
AROLE_DTL	        P20080625
AB_USER	                P20080625
AB_ADJ	                P20080625
AB_ADJ_COLUMNS	        P20080625
AT_BATCH_CODE_LU	P20080625
AT_BATCH_PYMT	        P20080625


> I don't have priviliges to see any dba tables.

Thanks for you valuable time and help.

[Updated on: Fri, 25 July 2008 15:38]

Report message to a moderator

Re: partition [message #336357 is a reply to message #336337] Fri, 25 July 2008 15:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It appears the lack of privilege prevents you from accessing the information you desire.
We can't assist with that reality.
You require a political solution; not a technical one.
Re: partition [message #336445 is a reply to message #336337] Sat, 26 July 2008 18:11 Go to previous messageGo to next message
Lynn T
Messages: 8
Registered: July 2008
Junior Member
Maybe this will help you do a bit of research: I listed all the tables that you have access to (USER), and mention partitions. Then I got a list of date fields contained in them. None of them look to me like they mention partition creation though.

1 SELECT TABLE_NAME, COLUMN_NAME
2 FROM ALL_TAB_COLUMNS
3 WHERE TABLE_NAME IN
4 (
5 select table_name
6 from all_tab_columns
7 where column_name like '%PART%'
8 AND TABLE_NAME LIKE 'USER%'
9 )
10* AND DATA_TYPE = 'DATE'

TABLE_NAME COLUMN_NAME
------------------------------ -----------------
USER_ALL_TABLES LAST_ANALYZED
USER_EXPFIL_PREDTAB_PLAN TIMESTAMP
USER_INDEXES LAST_ANALYZED
USER_IND_PARTITIONS LAST_ANALYZED
USER_IND_STATISTICS LAST_ANALYZED
USER_IND_SUBPARTITIONS LAST_ANALYZED
USER_OBJECT_TABLES LAST_ANALYZED
USER_PART_COL_STATISTICS LAST_ANALYZED
USER_SQLSET_PLANS TIMESTAMP
USER_SQLTUNE_PLANS TIMESTAMP
USER_SUBPART_COL_STATISTICS LAST_ANALYZED
USER_TABLES LAST_ANALYZED
USER_TAB_MODIFICATIONS TIMESTAMP
USER_TAB_PARTITIONS LAST_ANALYZED
USER_TAB_STATISTICS LAST_ANALYZED
USER_TAB_SUBPARTITIONS LAST_ANALYZED

16 rows selected.
Re: partition [message #336446 is a reply to message #336337] Sat, 26 July 2008 18:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

  1  SELECT distinct OBJECT_NAME, TRUNC(CREATED) FROM USER_OBJECTS where object_type = 'TABLE PARTITION'
  2   and object_name like 'STREAM%'
  3* order by 1
SQL> /

OBJECT_NAME                      TRUNC(CRE
-------------------------------- ---------
STREAMS$_APPLY_SPILL_MSGS_PART   19-DEC-06


SQL> select table_name, partition_count from user_part_tables where table_name = 'STREAMS$_APPLY_SPILL_MSGS_PART';

TABLE_NAME                     PARTITION_COUNT
------------------------------ ---------------
STREAMS$_APPLY_SPILL_MSGS_PART               1
Re: partition [message #336603 is a reply to message #336337] Mon, 28 July 2008 06:17 Go to previous message
Lynn T
Messages: 8
Registered: July 2008
Junior Member
Thanks, Ana. I'd forgotten about that.
Previous Topic: Performance Essue
Next Topic: Calculate 1 week before given date
Goto Forum:
  


Current Time: Sun Feb 09 09:05:48 CST 2025