Estimating & Projecting the size of a table in Oracle

There are various method of estimating and projecting the size of a table . The best way to estimate database size is to load some sample data into our tables and then calculate statistics on those tables. Query DBA_TABLES for the AVG_ROW_LEN to get an idea of the average number of bytes for each row. We can use Tom Kyte’s (asktom.com) show_space code to help us with the #of blocks evaluation or just use a plain simple technique shown below.  Many people just use the average rowlength (avg_row_len column) in order to as certain the size after doing a CTAS (Create Table AS)…however, that is not accurate as we will show below:

Example : 

SQL> create table test as select * from all_objects ;
Table created.

SQL>Exec dbms_stats.gather_table_stats(user,'TEST') ;

SQL>select num_rows ,blocks,empty_blocks.avg_row_len from user_tables where table_name='TEST';
NUM_ROWS        BLOCKS       EMPTY_BLOCKS        AVG_ROW_LEN
----------              ------------     -------------------         -------------------
183546               2694                    0                           98

So, the average rowlength is being reported as 98.  

The “right” and the only sure-shot way of calculating the size would be to calculate using these steps:


1.) We check on the existing schemas and that will give us the tables filled with representative data (say expected volume for one of the schemas for a large table is say 1 million rows – we check the # of blocks for n number of rows).
2.) Collect DBMS_STATS.
3.) Check the number of blocks.
4.) Then multiply by the multiplying factor i.e. if we estimated for say 1% of the actual requirement, multiply by 100 .
Example (Same table from above):

SQL> select extent_id, bytes, blocks  from user_extents   where segment_name = ‘TEST’  
                    and segment_type = ‘TABLE’ ;
EXTENT_ID  BYTES       BLOCKS
-------------   --------      -----------
       330      65536          8
       331      65536          8
       332      65536          8
       333      65536          8
       334      65536          8
                  -----
                  -----
       667      65536          8
       ------                    --------
Total (sumof block )      2696

SQL> select blocks, empty_blocks, avg_space, num_freelist_blocks  from user_tables  
                where table_name = ‘TEST’ ;
BLOCKS       EMPTY_BLOCKS     AVG_SPACE       NUM_FREELIST_BLOCKS
--------         ------------------       ---------------      ------------------------------
2694                   0                           0                                 0
So :
1.)  We have 2696 blocks allocated to the table TEST.
2.)  0 blocks are empty (of course – in this example that is bound to happen – not in reality though).
.3)  2694 blocks contain data (the other 2 are used by the system).
4.)  Average of 0k is free on each block used.
So,
1.) Our table TEST consumes 2694 blocks of storage in total for 183546 records.
2.) Out of this : 2694 * 8k blocksize – (2694 * 0k free) = 21552k is used for our data.

The calculation from the average row-length would have yielded : 183546 * 98 = ~17566k (see the difference ?).

Also, now that we have the calculation, if the actual table TEST needs to be sized for say 10 million records, then we use the multiplying factor for it :

183546 records take —> 21552 k

10 million will take —-> (21552k * 10 million) / 183546

That way, we will be assured that the data calculations are correct.  Likewise for the indexes.  Oracle Guru Tom Kyte has a lot of very good examples on his site that we should read before we embark on our sizing calculators for an Oracle Schema.

Reference :  http://decipherinfosys.wordpress.com

Enjoy    :-)

How to find startup & shutdown time of Oracle Database

Sometimes, we have to determine the startup and shutdown history of a database . There is no any data-dictionary  tables which contains the history of startup and shutdown time . Sometimes a system administrator reboot server in such cases we can determines the startup and shutdown time by checking the alert logfile. Since alert logfile keeps on increasing and we manages the alert logfile either by truncate or deleting its contains . 

Instead of depending on alert logfile , we can create table which contains the history of startup and shutdown by using the triggers . Here we will create two triggers i.e, first trigger will fired once the database is startup and second trigger is fired when database is shutdown . Let's have a  look . 

1.) Create a table to store history
SQL> create table db_history ( time date , event  varchar2(12)) ;
Table created.

2.) Create trigger for catching startup time 
SQL>create or replace trigger dbhist_start_trigr
after startup on database 
begin
insert into db_history values (sysdate , 'StartUp' ) ;
end ; 
/
Trigger created.

3.) Create Trigger to catch shutdown time 
SQL> create or replace trigger dbhist_shut_trigr
before shutdown on database
begin
insert into db_history values (sysdate, 'ShutDown' ) ;
end;
/
Trigger created.


Enjoy      :-)

Estimate Oracle Database Size

Oracle database is consists of datafiles, controlfiles and redolog files . Therefore , the size of oracle database can be calculated by adding above files. The below script will estimate the oracle database size .

SQL> select a.datafile_size + b.temp_size + c.redo_size + d.controlfile_size  "Total_size in GB"

from ( select sum(bytes)/1024/1024/1024  as datafile_size 

from dba_data_files) a,

( select nvl(sum(bytes),0)/1024/1024/1024 as temp_size 

from dba_temp_files ) b,

( select sum(bytes)/1024/1024/1024 as  redo_size

from sys.v_$log ) c,

( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 as  controlfile_size 

from v$controlfile) d ; 
output :

Total_size in GB
----------------
       1.9475708


Enjoy     :-) 

Move all the Indexes of Schema to Different Tablespace

Once my friend ask me  " Is it beneficial to move all indexes of schema to other tablespace  " .
The answered depends on what you're trying to accomplish.There would be no performance benefit to doing this. There would almost certainly be no reliability/ recoverability benefit. There may be some benefit to the DBA's sense of organization but it's exceptionally unlikely that there will be any practical benefits.

If he really need that then he may do this :

BEGIN
  FOR idx IN (SELECT * FROM dba_indexes WHERE owner = <<schema name>> AND tablespace_name = <<old tablespace name>>)
  LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.owner || '.' || idx.index_name || ' REBUILD TABLESPACE <<new tablespace name>>';
  END LOOP;
END;


Note:  If the Database is in ARCHIVELOG mode it may generate a lot of Archived Logs.

Login in User Schemas Without Having Password

Sometimes, we need to sign-on as a specific user to understand the exact nature of their problem.While it is easy to alter the user ID to make a new password, this is an inconvenience to the end-user because they have to re-set a new password .  However, as DBA we can extract the encrypted password from the dba_users views, save it, and re-set the password after we have finished with testing .

For example, assume that we need to sign-on as HARRY user and test their Oracle privileges:

We perform the following steps:


1.) Extract the encrypted password 
SQL> select 'alter user "'||username||'" identified by values  '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password  from  dba_users where username = 'HARRY';
OLD_PASSWORD
--------------------------------------------------------------------
alter user "HARRY" identified by values '15EC3EC6EAF863C'  ;


2.) Change HARRY’s password and sign-on for testing and perform all testing 
SQL> alter user HARRY identified by harry;
User altered .
SQL> conn harry/harry@noida
Connected

3.)  Reset the same Old Password 
When we have completed the testing we can set-back the original encrypted password using the output from the below query as 
SQL> alter user "HARRY" identified by values '15EC3EC6EAF863C' ; 




Enjoy      :-)