SP2-0308: cannot close spool file

SP2-0308: cannot close spool file

This Error popup due to disk partition is going to full so to remove this error delete something from your hard disk partition..

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;

Structure of dba_errors

Name                                                        Null?                            Type
 -----------------------------------                               --------                  ----------------------------
 OWNER                                                   NOT NULL               VARCHAR2(30)
 NAME                                                      NOT NULL              VARCHAR2(30)
 TYPE                                                                                          VARCHAR2(12)
 SEQUENCE                                             NOT NULL                NUMBER
 LINE                                                         NOT NULL                NUMBER
 POSITION                                               NOT NULL                NUMBER
 TEXT                                                       NOT NULL               VARCHAR2(4000)
 ATTRIBUTE                                                                              VARCHAR2(9)
 MESSAGE_NUMBER                                                               NUMBER

Structure of dba_tablespace

Name                                                                   Null?                          Type
 -----------------------------------------                             --------                     ----------------------------
 TABLESPACE_NAME                                    NOT NULL              VARCHAR2(30)
 BLOCK_SIZE                                                  NOT NULL               NUMBER
 INITIAL_EXTENT                                                                             NUMBER
 NEXT_EXTENT                                                                                 NUMBER
 MIN_EXTENTS                                               NOT NULL               NUMBER
 MAX_EXTENTS                                                                                NUMBER
 PCT_INCREASE                                                                                 NUMBER
 MIN_EXTLEN                                                                                     NUMBER
 STATUS                                                                                              VARCHAR2(9)
 CONTENTS                                                                                        VARCHAR2(9)
 LOGGING                                                                                           VARCHAR2(9)
 FORCE_LOGGING                                                                             VARCHAR2(3)
 EXTENT_MANAGEMENT                                                                VARCHAR2(10)
 ALLOCATION_TYPE                                                                         VARCHAR2(9)
 PLUGGED_IN                                                                                     VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                                              VARCHAR2(6)
 DEF_TAB_COMPRESSION                                                               VARCHAR2(8)
 RETENTION                                                                                       VARCHAR2(11)
 BIGFILE                                                                                                VARCHAR2(3)

Structure of dba_users

Name                                                          Null?                Type
 -----------------------------------------                           --------           ----------------------------
 USERNAME                                              NOT NULL     VARCHAR2(30)
 USER_ID                                                   NOT NULL       NUMBER
 PASSWORD                                                                        VARCHAR2(30)
 ACCOUNT_STATUS                                NOT NULL       VARCHAR2(32)
 LOCK_DATE                                                                       DATE
 EXPIRY_DATE                                                                    DATE
 DEFAULT_TABLESPACE                        NOT NULL       VARCHAR2(30)
 TEMPORARY_TABLESPACE                 NOT NULL        VARCHAR2(30)
 CREATED                                                  NOT NULL          DATE
 PROFILE                                                   NOT NULL         VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                              VARCHAR2(30)
 EXTERNAL_NAME                                                              VARCHAR2(4000)

Structure Of dba_indexes

Name                                                           Null?                                  Type
 -----------------------------------------                        --------                               ----------------------------
 OWNER                                                  NOT NULL                       VARCHAR2(30)
 INDEX_NAME                                       NOT NULL                        VARCHAR2(30)
 INDEX_TYPE                                                                                     VARCHAR2(27)
 TABLE_OWNER                                    NOT NULL                        VARCHAR2(30)
 TABLE_NAME                                       NOT NULL                        VARCHAR2(30)
 TABLE_TYPE                                                                                    VARCHAR2(11)
 UNIQUENESS                                                                                    VARCHAR2 (9)
 COMPRESSION                                                                                  VARCHAR2(8)
 PREFIX_LENGTH                                                                               NUMBER
 TABLESPACE_NAME                                                                       VARCHAR2(30)
 INI_TRANS                                                                                          NUMBER
 MAX_TRANS                                                                                     NUMBER
 INITIAL_EXTENT                                                                            NUMBER
 NEXT_EXTENT                                                                                NUMBER
 MIN_EXTENTS                                                                                 NUMBER
 MAX_EXTENTS                                                                               NUMBER
 PCT_INCREASE                                                                               NUMBER
 PCT_THRESHOLD                                                                           NUMBER
 INCLUDE_COLUMN                                                                       NUMBER
 FREELISTS                                                                                       NUMBER
 FREELIST_GROUPS                                                                       NUMBER
 PCT_FREE                                                                                        NUMBER
 LOGGING                                                                                        VARCHAR2(3)
 BLEVEL                                                                                            NUMBER
 LEAF_BLOCKS                                                                               NUMBER
 DISTINCT_KEYS                                                                            NUMBER
 AVG_LEAF_BLOCKS_PER_KEY                                                 NUMBER
 AVG_DATA_BLOCKS_PER_KEY                                                NUMBER
 CLUSTERING_FACTOR                                                               NUMBER
 STATUS                                                                                           VARCHAR2(8)
 NUM_ROWS                                                                                  NUMBER
 SAMPLE_SIZE                                                                               NUMBER
 LAST_ANALYZED                                                                        DATE
 DEGREE                                                                                         VARCHAR2(40)
 INSTANCES                                                                                   VARCHAR2(40)
 PARTITIONED                                                                               VARCHAR2(3)
 TEMPORARY                                                                                VARCHAR2(1)
 GENERATED                                                                                 VARCHAR2(1)
 SECONDARY                                                                                 VARCHAR2(1)
 BUFFER_POOL                                                                             VARCHAR2(7)
 USER_STATS                                                                                 VARCHAR2(3)
 DURATION                                                                                   VARCHAR2(15)
 PCT_DIRECT_ACCESS                                                              NUMBER
 ITYP_OWNER                                                                             VARCHAR2(30)
 ITYP_NAME                                                                               VARCHAR2(30)
 PARAMETERS                                                                           VARCHAR2(1000)
 GLOBAL_STATS                                                                        VARCHAR2(3)
 DOMIDX_STATUS                                                                     VARCHAR2(12)
 DOMIDX_OPSTATUS                                                                VARCHAR2(6)
 FUNCIDX_STATUS                                                                   VARCHAR2(8)
 JOIN_INDEX                                                                               VARCHAR2(3)
 IOT_REDUNDANT_PKEY_ELIM                                            VARCHAR2(3)
 DROPPED                                                                                    VARCHAR2(3)