Database SQL Commands

Query to find size of table:
*******************
SELECT A.TABLE_NAME,A.NUM_ROWS,ROUND(SUM(B.BYTES)/1024/1024/1024,2) table_size FROM ALL_TABLES A, (SELECT SEGMENT_NAME , bytes
 from user_Segments
where segment_name = 'PSAPMSGPUBDATA' ) B
WHERE A.TABLE_NAME = B.SEGMENT_NAME AND A.TABLE_NAME = 'PSAPMSGPUBDATA'
GROUP BY A.TABLE_NAME,A.NUM_ROWS;




Query to find indexe names of a table:
****************************
SQL> select INDEX_NAME,UNIQUENESS from IND where TABLE_NAME='PS_TL_PAYABLE_TIME';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
PS_TL_PAYABLE_TIME             UNIQUE
PSATL_PAYABLE_TIME             NONUNIQUE
PSBTL_PAYABLE_TIME             NONUNIQUE
PSCTL_PAYABLE_TIME             NONUNIQUE




Query to take backup of a table in database level:
*************************************
create table PS_W2_COMPANY_BKP AS select * from PS_W2_COMPANY;




Query to check Database size:
***********************
select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes)/1024/1024/1024 data_size
         from dba_data_files ) a,
     ( select nvl(sum(bytes)/1024/1024/1024,0) temp_size
         from dba_temp_files ) b,
     ( select sum(bytes)/1024/1024/1024 redo_size
         from sys.v_$log ) c;


Query to find bit version of ORACLE:
*****************************
1. select length(addr)*4 || '-bits' word_length from v$process where ROWNUM =1;


2. Can also find whether 32 bit or 64 bit Oracle is installed by  viewing  file :oraclehomeproperties.xml
   Location @ ORACLE_HOME\inventory\ContentsXML\oraclehomeproperties.xml

   Example:
   For 32 bit Oracle ,oraclehomeproperties.xml will have following entry :


   <ARU_ID>912</ARU_ID>
      <ARU_ID_DESCRIPTION>Microsoft Windows (32-bit)
..
..
   <PROPERTY NAME="ARCHITECTURE" VAL="32"/>



    For 64 bit Oracle  ,oraclehomeproperties.xml will have following entry :


    <ARU_ID>233</ARU_ID>
      <ARU_ID_DESCRIPTION>Microsoft Windows (64-bit AMD)
..
..
    <PROPERTY NAME="ARCHITECTURE" VAL="64"/>

3. 32-bit: check the oracle_home where you installed the oracle client software, if there is only "lib" directory and no lib32    directory

    64 bit: check the oracle_home where you installed the oracle client software, if there is "lib" directory and also "lib32"     directory

4. Open command prompt and do sqlplus

   Then goto task manager and check the process to confirm whether it's 32 bit or 64 bit (32 bit has a *32 next to the process name).