Oracle Part

Process Increase due to error sometimes : 

SQL> connect / as sysdba
ERROR:
ORA-00020: maximum number of processes (300) exceeded

or  java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

Steps to increase the number for processes/sessions at database level in Oracle is as follows

  1.  Log on to the database as system administrator

 In SQL Plus enter: "connect / as sysdba"

  2. Set the number of processes to be desired value 

In SQL Plus "alter system set processes= scope=spfile"

  3. Unset the number of sessions to remove the default session limit

In SQLPlus "alter system reset sessions scope=spfile sid='*' "

  4. Shutdown the database

In SQL Plus "shutdown immediate"

  5. Startup the database 

In SQL Plus "startup"

   6. Check the changes have taken effect.

In SQL Plus "show parameter sessions" and "show parameter processes"

SELECT USERNAME, 
       TERMINAL, 
       PROGRAM, 
       SQL_ID, 
       LOGON_TIME, 
       ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON, 
       ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL  
  From v$session 
 WHERE STATUS='ACTIVE' 
   AND USERNAME IS NOT NULL
ORDER BY MINUTES_LOGGED_ON DESC;

Long Running session

Status of Long Performing Operations Such as Gather Stats / Import/Export database

SELECT ROUND(sofar/totalwork*100,2) as PercentDone, 
v$session_longops.*
FROM v$session_longops
WHERE sofar <> totalwork
ORDER BY target, sid;

-- Query to fetch Used Percentage for Tablespaces
select Tablespace_name, Used_space, Tablespace_size, ROUND(Used_Percent,2) as Used_Percent from dba_tablespace_usage_metrics order by used_percent desc;

-- Query to fetch Used Space by each data file
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, ROUND (dd.bytes/1024/1024) TABLESPACE_MB, SUM (fs.bytes/1024/1024) MB_FREE, MAX(fs.bytes)/1024/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024/1024
ORDER BY dd.tablespace_name, dd.file_name;

-- List tablespaces, size, free space, and percent free
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,
ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;

-- Query to fetch Import Status in Percentage
SELECT ROUND(sofar/totalwork*100,2) as PercentDone, 
v$session_longops.*
FROM v$session_longops
WHERE sofar <> totalwork
ORDER BY target, sid;

select username from dba_users order by Username;
select role from dba_roles order by role;
select * from dba_data_files;
select * from dba_temp_files;
select * from dba_directories;





-- Query to fetch Drop Status


select count(*) , owner from dba_objects group by owner