ArcSight: Oracle DB – Useful Commands

Log in to sqlplus

To log in to sqlplus you need to SSH to the Oracle server as root and run the following commands:

root# su oracle
oracle$ sqlplus / as sysdba

Query – Explore ArcSight database

oracle$ sqlplus / as sysdba
-- Assumption1: ArcSight DB Name is "arcsight"
-- Assumption2: ArcSight DB Username is "arcsight"
SQL> connect arcsight@arcsight
SQL> select * from tab where TNAME LIKE 'ARC_EVENT%';
SQL> select column_name from all_tab_columns where table_name = 'ARC_EVENT_GEO_LOCATION';

Query – Database start time

select	to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from	v$instance
/

Query – Database size  and available free space

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select	round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,	round(sum(used.bytes) / 1024 / 1024 / 1024 ) - 
	round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,	round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select	bytes
	from	v$datafile
	union	all
	select	bytes
	from 	v$tempfile
	union 	all
	select 	bytes
	from 	v$log) used
,	(select sum(bytes) as p
	from dba_free_space) free
group by free.p
/

 Query – Available space on each tablespace

set linesize 150
column tablespace_name format a20 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total| Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(MB)'
column Pct_Free format 999.99 heading '% Free'
column Chunks_Free format 9999 heading 'No Of Ext.'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
set echo off
PROMPT  FREE SPACE AVAILABLE IN TABLESPACES
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
     sum(a.sumb/1048576) Tot_Free,
     sum(a.sumb)*100/sum(a.tots) Pct_Free,
     sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
from
     (select tablespace_name,0 tots,sum(bytes) sumb,
             max(bytes) largest,count(*) chunks
      from dba_free_space a
      group by tablespace_name
     union
      select tablespace_name,sum(bytes) tots,0,0,0
      from dba_data_files
      group by tablespace_name) a
group by a.tablespace_name order by pct_free;

Query – Delete archive logs

oracle$ rman TARGET /
RMAN> delete archivelog all;