Database properties views

Below are some query that will help you to check current database properties:

SQL>SELECT * FROM DATABASE_PROPERTIES;

SQL> SELECT  * FROM DATABASE_SUMMARY;

If you want the full version information for your DB then:

SQL>SELECT * FROM v$version;

If you want your DB parameters then:

SQL>SELECT * FROM v$parameter;

If you want more information about your DB instance then:

SQL>SELECT * FROM v$database;

SQL>SELECT * FROM v$instance;

If you want the "size" of your database then this will give you a close enough calculation:

SQL>SELECT SUM(bytes / (1024*1024)) "DB Size in MB" FROM dba_data_files;

ORA-02391:exceeded simultaneous SESSIONS_PER_USER limit

ORA-02391 is user session error. It means you need to kill some user session if its not required or you need to increase sessions per use limit. Killing sessions can be very destructive if you kill the wrong session so be careful when killing session. for that first we need to identify that sessions for that query is given below. . SQL>SELECT s.inst_id, s.sid,s.serial#,p.spid,s.username,s.program FROM   gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE  s.type != 'BACKGROUND'; The basic syntax for killing sessions is given below. SQL>ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' immediate; if you want to increase user session process are given below: 1) SELECT name, value FROM gv$parameter WHERE name = 'resource_limit'; 2) ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH; 3) SELECT name, value FROM gv$parameter WHERE name = 'resource_limit'; 4) ALTER PROFILE <profile name>  LIMIT sessions_per_user 80;