Accessible Cursors

SELECT oc.user_name, st.sql_text
  FROM v$open_cursor oc,v$sqltext st
 WHERE oc.address    = st.address
      AND oc.hash_value = st.hash_value
 ORDER BY oc.user_name, st.piece
 
For performance reasons, pl/sql cursors are cached for future reuse. So once you use them up they will be added to LRU list of cursor table. Hence you can see them in v$open_cursor.          
Actual Open Cursors
select sum(a.value), b.name
  from v$sesstat a,v$statname b
 where a.statistic# = b.statistic#
   and b.name = ‘opened cursors current’
 group by b.name

1. Enable tracing and set the maxiumum dump file size to unlimited:

SQL> ALTER SYSTEM SET timed_statistics = true;
SQL> ALTER SYSTEM SET max_dump_file_size = unlimited;

2. find sid and serial for session you want to trace:

SQL> SELECT sid, serial#, username from v$session;

3. start tracing:

SQL> execute dbms_system.set_sql_trace_in_session(sid, serial, true);

Now, all query and operation from the session are traced.
4. stop tracing:

SQL> execute dbms_system.set_sql_trace_in_session(sid, serial, false);

Read the trace file:
You can find trace files in user dump file destination (UDUMP):

SQL> SHOW parameter user_dump_dest
 
NAME …

ALTER tablespace TABLESPACE ADD datafile ‘/path/datafile1.dbf’ size 512M;

Some tablespaces can be autoextensible, in this case, when necessary, Oracle automatically allocate new space for the tablespace; so you have not to add space to tablespaces. Use this query to see list of tablespaces with used and free space and to see if each tablespace is autoextensible:

SELECT
a.file_id,
a.tablespace_name,
trunc(decode(a.autoextensible,’YES’,a.maxsize-a.bytes+b.free,’NO’,b.free)/1024/1024) free_mb,
trunc(a.bytes/1024/1024) size_mb,
trunc(a.maxsize/1024/1024) maxsize_mb,
a.autoextensible ae,
trunc(decode(a.autoextensible,’YES’,(a.maxsize-a.bytes+b.free)/a.maxsize*100,’NO’,b.free/a.maxsize*100)) free_pct
FROM
(SELECT
file_id,
tablespace_name,
autoextensible,
bytes,
…

Enlarge a datafile

ALTER DATABASE datafile ‘/path/datafile.dbf’ resize 1024M;

This query shows all invalid objects in the Oracle database:

SELECT owner, object_name, object_type, STATUS
FROM dba_objects WHERE STATUS=’INVALID’ ORDER BY owner DESC;

Next is a metaquery that generates all statements to recompile invalid objects:

SELECT ‘ALTER ‘||decode(owner,’PUBLIC’,'PUBLIC’)||’ ‘||
decode(object_type,’PACKAGE BODY’,'PACKAGE’,'TYPE BODY’,'TYPE’,object_type)||’ ‘||
decode(owner,’PUBLIC’,NULL,owner||’.')||’”‘||object_name||’” COMPILE ‘||
decode(object_type,’PACKAGE BODY’,'BODY;’,'TYPE BODY’,'BODY;’,';’)
FROM dba_objects WHERE STATUS=’INVALID’ ORDER BY owner DESC;