Home » SQL & PL/SQL » SQL & PL/SQL » detailed datafile size
detailed datafile size [message #272334] Thu, 04 October 2007 11:01 Go to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Hello
i was tryint to determine the datafile size and (with df-h) i can see 1gb aprox
but when i do the sum (blocks*8192)1024/1024 i get less than the datafile real size

when i get the size using the table
user_extent i get the real size

but why i cant determine de table_size using user_tables or all_tables?
thnx in advance
Re: detailed datafile size [message #272336 is a reply to message #272334] Thu, 04 October 2007 11:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste what you did.
Don't forget to post your Oracle version (4 decimals).
Don't forget to format.

Do we have to repeat this to you?

Regards
Michel
Re: detailed datafile size [message #272337 is a reply to message #272336] Thu, 04 October 2007 11:16 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

sorry michael i was not puting code (that's why i didn't put the

i was asking about a theorical thing
why, using the user_table view i can't get the entire datafile size and using the user_extent i did it?

if you need i can put the entire code sorry and thnx for your help as ever Very Happy
Re: detailed datafile size [message #272339 is a reply to message #272337] Thu, 04 October 2007 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why, using the user_table view i can't get the entire datafile size

Because there is no size in user_tables.

Regards
Michel
Re: detailed datafile size [message #272341 is a reply to message #272339] Thu, 04 October 2007 11:34 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

but in the user_tables view i can see
SQL> desc user_tables;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(30)
 IOT_NAME                                           VARCHAR2(30)
 STATUS                                             VARCHAR2(8)
 PCT_FREE                                           NUMBER
 PCT_USED                                           NUMBER
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 LOGGING                                            VARCHAR2(3)
 BACKED_UP                                          VARCHAR2(1)
 NUM_ROWS                                           NUMBER
 BLOCKS                                             NUMBER
 EMPTY_BLOCKS                                       NUMBER
 AVG_SPACE                                          NUMBER
 CHAIN_CNT                                          NUMBER
 AVG_ROW_LEN                                        NUMBER
 AVG_SPACE_FREELIST_BLOCKS                          NUMBER
 NUM_FREELIST_BLOCKS                                NUMBER
 DEGREE                                             VARCHAR2(30)
 INSTANCES                                          VARCHAR2(30)
 CACHE                                              VARCHAR2(15)
 TABLE_LOCK                                         VARCHAR2(8)
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 PARTITIONED                                        VARCHAR2(3)
 IOT_TYPE                                           VARCHAR2(12)
 TEMPORARY                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NESTED                                             VARCHAR2(3)
 BUFFER_POOL                                        VARCHAR2(7)
 ROW_MOVEMENT                                       VARCHAR2(8)
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 SKIP_CORRUPT                                       VARCHAR2(8)
 MONITORING                                         VARCHAR2(3)
 CLUSTER_OWNER                                      VARCHAR2(30)
 DEPENDENCIES                                       VARCHAR2(8)
 COMPRESSION                                        VARCHAR2(8)
 DROPPED                                            VARCHAR2(3)

the blocks column
and using
select table_name, (blocks*8192)/1024/1024 from user_tables;

TABLE_NAME                     (BLOCKS*8192)/1024/1024
------------------------------ -----------------------
KI_OBJECT_PROPERTY                          1123.22656
KI_TEMPORARY                                  .1015625
KL_LOCK                                       .0390625
KR_RESOURCE                                          0
KR_RESOURCE_CHILD                                    0
KR_RESOURCE_CONTENT                                  0
KR_RESOURCE_LABEL                                    0
KR_RESOURCE_PROPERTY                                 0
KR_RESOURCE_PROPERTY_LOB                             0
KR_RESOURCE_REVISION_HISTORY                         0
KR_RESOURCE_WORKING                                  0

TABLE_NAME                     (BLOCKS*8192)/1024/1024
------------------------------ -----------------------
KV_RESOURCE                                          0
KV_RESOURCE_CHILD                                    0
KV_RESOURCE_CONTENT                                  0
KV_RESOURCE_LABEL                                    0
KV_RESOURCE_PROPERTY                                 0
KV_RESOURCE_PROPERTY_LOB                             0
KV_RESOURCE_WORKING                                  0
K_RESOURCE                                           0
K_RESOURCE_CHILD                                     0
K_RESOURCE_CONTENT                                   0
K_RESOURCE_PROPERTY                                  0

TABLE_NAME                     (BLOCKS*8192)/1024/1024
------------------------------ -----------------------
K_RESOURCE_PROPERTY_LOB                              0
K_USER                                               0
K_VERSION                                     .0390625
KA_INTERCEPTOR_EVENT                         10.734375
KI_CLASS                                      .0390625
KI_CLASS_INHERITANCE                          .1015625
KI_LINK                                       .0390625
KI_LINK_INHERITANCE                           .0390625
KI_OBJECT                                   403.734375
KI_OBJECT_HREF_PROPERTY                     341.984375
KI_OBJECT_INHERITANCE                       198.460938

TABLE_NAME                     (BLOCKS*8192)/1024/1024
------------------------------ -----------------------
KI_OBJECT_MAIN_PROPERTY                              0

34 rows selected.

can i determine the size in mb?
maybe i am missing something :S
Re: detailed datafile size [message #272344 is a reply to message #272341] Thu, 04 October 2007 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It was a rhetoric answer. Very Happy
And you don't need to post all stuff just the interesting lines.

What is blocks in user_tables?
What is it in user_segments or user_extents?
What in the case of partitioned tables?
What if the tablespace/file does contain something that is not a table?
...

Regards
Michel
Re: detailed datafile size [message #272348 is a reply to message #272334] Thu, 04 October 2007 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
What if size changed after LAST_ANALYZED was updated?
Re: detailed datafile size [message #272589 is a reply to message #272344] Fri, 05 October 2007 11:38 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Michel Cadot wrote on Thu, 04 October 2007 11:45
It was a rhetoric answer. Very Happy
And you don't need to post all stuff just the interesting lines.

What is blocks in user_tables?
What is it in user_segments or user_extents?
What in the case of partitioned tables?
What if the tablespace/file does contain something that is not a table?
...

Regards
Michel


sorry michael yesterday i was not at the office and that's why i didn't response you
1.- blocks in user_tables are how many blocks the table needs
2.- there are indexes i mean (but i am moving them into another tablespace
3.- Partitioned tables i think that does matter because i can get all blocks that needs partitioned or not using user_tables view
4.- there are indexes... but yes, i am moving them into another tablespace.

Btw, when you create a datafile, you put a initial size
alter tablespace test 
   add datafile '/u03/oradata/test1.dbf' 
   size 100M 
   autoextend on 
   next 50M 
   maxsize 300M;

but if you only create a table with few rows (only text) maybe you use few blocks
how can i get the real size of the information stored into the datafile?

Thnx in advance

Re: detailed datafile size [message #272591 is a reply to message #272589] Fri, 05 October 2007 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
1.- blocks in user_tables are how many blocks the table needs

Wrong it is the number of blocks used by the table as it is reported last time you gather statistics with the parameter you gave. So it may be not accurate and not up to date.

Quote:
how can i get the real size of the information stored into the datafile?

I don't understand the question. Precise what you have in mind. What is "real size"?
Query dba_extents you then get a map of what is used in each data file.

Regards
Michel
Re: detailed datafile size [message #272607 is a reply to message #272591] Fri, 05 October 2007 13:01 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

when you create a datafile, you define the initial size
Oracle reserv in the filesystem that size to the datafile but the file is empty.
After that, you store objects inside (tables, views, indexes) but there are not too much objects to need of an extention.

My question is to know the real occuped size in the datafile.
i don't know how to explain it, sorry but my english is not TOO good Sad


pd: now i am using the dba_extents
Re: detailed datafile size [message #272613 is a reply to message #272607] Fri, 05 October 2007 13:26 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Oracle reserv in the filesystem that size to the datafile but the file is empty.

Wrong, the file is filled with blocks and some are already used like file header, allocation bitmap.

You didn't define "real size". They are many ways to calculate sizes depending on what you want.
Explain why dba_datafiles+dba_extents do not fit your requirement.

Regards
Michel
Previous Topic: Fetch out of sequence error.
Next Topic: Using a RECORD as an IN parameter in a FUNCTION
Goto Forum:
  


Current Time: Sat Dec 10 20:17:49 CST 2016

Total time taken to generate the page: 0.14398 seconds