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      :-)