SCRIPT:To get the % useage of all available open cursor

---To get the % useage of all available open cursor

SELECT 'session_cached_cursors' parameter, LPAD(value, 5) value,
DECODE(value, 0, ' n/a', to_char(100 * used / value, '990') || '%' ) usage
FROM ( SELECT MAX(s.value) used
FROM v$statname n, v$sesstat s
WHERE
n.name = 'session cursor cache count' and
s.statistic# = n.statistic# ),
(SELECT value
FROM v$parameter
WHERE
name = 'session_cached_cursors' )
UNION ALL
SELECT 'open_cursors', LPAD(value, 5), to_char(100 * used / value, '990') || '%'
FROM
(SELECT MAX(sum(s.value)) used
FROM v$statname n, v$sesstat s
WHERE
n.name in ('opened cursors current', 'session cursor cache count') and s.statistic#=n.statistic#
GROUP BY
s.sid
),
(SELECT value
FROM v$parameter
WHERE
name = 'open_cursors' )
/

SCRIPT:Total Open Cursors By username & HostMachine

http://www.fasttrackdba.com/

Script: Sql to get the session info in database

http://www.fasttrackdba.com/

Script:Query to get the used space of datafile

http://www.fasttrackdba.com/

DBFile Sequential and Scattered Reads

http://www.fasttrackdba.com/

HighWaterMark:Script: db_usage_highwatermark

http://www.fasttrackdba.com/

Script: :session_waits.sql

http://www.fasttrackdba.com/

Script: jobs_running.sql

\http://www.fasttrackdba.com/

Script:Oracle Recovery Manager Scripts

http://www.fasttrackdba.com/

Basic Oracle Database: Basic Linux Commands

Basic Oracle Database: Basic Linux Commands

Basic Oracle Database: Tablespace Data Dictionary Views

Basic Oracle Database: Tablespace Data Dictionary Views

Tablespace Data Dictionary Views

http://www.fasttrackdba.com/

Transportable Tablespaces

http://www.fasttrackdba.com/

How to find out what ports are open on your Linux computer

http://www.fasttrackdba.com/

Performing Complete Database Recovery

http://www.fasttrackdba.com/

Script:Sql's for Temporary tablespace mining

Sql to monitoring the temporary tablespace :
select segtype, extents, blocks
from v$tempseg_usage
where tablespace = 'TEMP';

To see the default temporary tablespace for a database, execute the following query:

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Command To resize TEMPFILE:

ALTER DATABASE TEMPFILE 'temp01.dbf' RESIZE 100M;

Command to adding another sparse file:

ALTER TABLESPACE TEMP ADD TEMPFILE 'temp02.dbf' SIZE 100M;

TEMP tablespace resize Command:

ALTER DATABASE TEMPFILE '/u02/oracle/data/oradata/temp02.dbf' RESIZE 100M;

To see the default temporary tablespace for a database, execute the following query:

SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'

Query To Create Temporary tablespace:

CREATE TEMPORARY TABLESPACE temp01
TEMPFILE ‘/u02/xxx/xxx/temp01.dbs’ SIZE 500M;