+15146131498

Oracle DBA Support info@awator.net

Oracle Tips

Query datafiles in ORACLE

select Start_Date, Start_Time, Num_Logs, Round(Num_Logs * (Vl.Bytes / (1024 * 1024)), 2) AS Mbytes, Vdb.NAME AS Dbname
FROM  (SELECT To_Char(Vlh.First_Time, ‘YYYY-MM-DD’) AS Start_Date, To_Char(Vlh.First_Time, ‘HH24’) || ‘:00’ AS Start_Time, COUNT(Vlh.Thread#) Num_Logs
FROM  V$log_History Vlh
GROUP BY To_Char(Vlh.First_Time, ‘YYYY-MM-DD’), To_Char(Vlh.First_Time, ‘HH24’) || ‘:00’) Log_Hist, V$log Vl, V$database Vdb
WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date, Log_Hist.Start_Time;

===============================================================================

Temporary Tablespace Enhancements in Oracle Database 11g

Oracle 11g has a new view called DBA_TEMP_FREE_SPACE that displays information about temporary tablespace usage.
SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
—————————— ————— ————— ———-
TEMP                                  56623104        56623104   55574528

1 row selected.

Armed with this information, you can perform an online shrink of a temporary tablespace using the ALTER TABLESPACE command.
SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 40M;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
—————————— ————— ————— ———-
TEMP                                  42991616         1048576   41943040

1 row selected.

The shrink can also be directed to a specific tempfile using the TEMPFILE clause.
SQL> ALTER TABLESPACE temp SHRINK TEMPFILE ‘c:\app\oracle\oradata\vovo\temp01.dbf’ KEEP 30M;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
—————————— ————— ————— ———-
TEMP                                  31522816           65536   31457280

1 row selected.
The KEEP clause specifies the minimum size of the tablespace or tempfile. If this is omitted, the database will shrink the tablespace or tempfile to the smallest possible size.
SQL> ALTER TABLESPACE temp SHRINK SPACE;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
—————————— ————— ————— ———-
TEMP                                   1114112           65536    1048576

1 row selected.

 

 

For More information

contact info@awator.net

SOCIAL LINK

Copyright

Copyright (c) 1998-2014