Useful Oracle SQL commands

Useful Oracle basic SQL commands:

1.Count no. of records in a table

     select count(*) from sapsr3.;

2. Count no. of sessions in db

SELECT inst_id, count(*) "DB Sessions" FROM gv$session  WHERE type = 'USER' GROUP BY ;

3.Check status of file in recovery

  select FILE#, STATUS from v$datafile where status like '%REC%';

4. Check status of file in Offline state

  select FILE#, STATUS from v$datafile where STATUS like 'OFFLINE';

5. Rebuild index online

  alter index "SAPSR3"."RSSTATMANREQMAP~PT" rebuild online;

6.Check last optimizer statistics run

  select TABLE_NAME, STATUS, LAST_ANALYZED from DBA_TABLES where TABLE_NAME='';

7. Determine file name from file id


  select FILE_NAME from dba_data_files where FILE_ID in (46,49,50,51,52,53,55,56,57,58);