1 2 Previous Next 18 Replies Latest reply: Aug 25, 2011 12:23 PM by jgarry RSS

    block size in oracle

    782143
      HI all,

      I need to know the block size in oracle. How can I do that???
      I do not have access to dba tables.

      Thanks,
      Sonika
        • 1. Re: block size in oracle
          Aman....
          Sonika wrote:
          HI all,

          I need to know the block size in oracle. How can I do that???
          I do not have access to dba tables.

          Thanks,
          Sonika
          If you don't have access to the dba tables, why do you want to know it in the first place? Whats the actual business requirement for you to know it?

          Aman....
          • 2. Re: block size in oracle
            mseberg
            Ask your DBA to :



            SQL> show parameter block_size


            This is not a wiseguy remark. Ask your DBA is my answer. If you cannot get this information you have to start thinking about what your time is worth.


            Best Regards

            mseberg
            • 3. Re: block size in oracle
              782143
              I need it to calculate the actual bytes occupied by a table. I am able to get the blocks occupied by querying all_tables. Now I need the size of the block
              • 4. Re: block size in oracle
                BPeaslandDBA
                Query BYTES from ALL_SEGMENTS instead. :)

                Cheers,
                Brian
                • 5. Re: block size in oracle
                  jgarry
                  Odd, I never noticed before. I have dba_segments and user_segments, but no all_segments. 10.2.0.4, checked as sys, system and an overqualified user, doing a desc all_segments and looking in dba_views for '%SEGMENT%'. It's not in the 11.2 db reference, either.
                  • 6. Re: block size in oracle
                    mseberg
                    Thanks! You are correct.

                    I assumed if there was a DBA_ there must be an ALL_

                    Checked the oldest database I can find and : ( as sysdba )
                    Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
                    JServer Release 9.2.0.8.0 - Production
                    
                    SQL> desc all_segments;
                    ERROR:
                    ORA-04043: object all_segments does not exist
                    Best Regards

                    mseberg
                    • 7. Re: block size in oracle
                      jgarry
                      Poking about on the 'tubes, I see references to it not being there in 8i. It's starting to sound familiar, though.
                      • 8. Re: block size in oracle
                        884181
                        you can find the parameter of block size in init file.
                        • 9. Re: block size in oracle
                          AronTunzi
                          You can get better information using package DBMS_SPACE:

                          http://www.arontunzi.com/cms/index.php?option=com_content&view=article&id=92:tables-space-usage-with-dbmsspace&catid=43:monitoring-space-usage&Itemid=29
                          http://www.arontunzi.com/cms/index.php?option=com_content&view=article&id=83:estimating-space-requirements-for-tables&catid=43:monitoring-space-usage&Itemid=29

                          Regards
                          Aron
                          • 10. Re: block size in oracle
                            Aman....
                            tinghairen wrote:
                            you can find the parameter of block size in init file.
                            Which would be on the server side and I doubt OP has access to it.

                            Aman....
                            • 11. Re: block size in oracle
                              Aman....
                              Sonika wrote:
                              I need it to calculate the actual bytes occupied by a table. I am able to get the blocks occupied by querying all_tables. Now I need the size of the block
                              What's the requirement being a developer(assumption) to know the size of the table? What exactly you are willing to do , if you can tell us that, it would be easier to help.

                              Aman....
                              • 12. Re: block size in oracle
                                878302
                                you can check the v$parameter view.
                                Lists the name-value pairs of the init.ora file (or their default, if not in the init.ora). For example, if you need to know what your block size is:
                                select value from v$parameter where name = 'db_block_size'
                                • 13. Re: block size in oracle
                                  Mahesh Menon
                                  Hi,

                                  The below will show the block sizes used in the database for your application schema.

                                  SQL> select distinct bytes/blocks "BLOCK SIZE in BYTES" from user_segments;

                                  SQL> select distinct bytes/blocks/1024 "BLOCK SIZE in KILOBYTES" from user_segments;

                                  HTH,
                                  Regards,
                                  Mahesh.
                                  • 14. Re: block size in oracle
                                    Mahesh Menon
                                    I need it to calculate the actual bytes occupied by a table. I am able to get the blocks occupied by querying all_tables. Now I need the size of the block
                                    10:27:45 SQL> select distinct bytes/blocks/1024 "BLOCK SIZE in KILOBYTES" from user_segments where segment_name='ABC' and segment_type='TABLE';
                                    
                                    BLOCK SIZE in KILOBYTES
                                    -----------------------
                                                          8
                                    1 2 Previous Next