Script to compile all invalid objects

The following script compiles all invalid objects in the schema. Login through SQL Plus and execute this file. In case all objects are not compiled even after the execution, execute the same file once or twice more. This is to ensure that all the dependent objects are compiled.

compile.sql
set echo off

set feed off
set sqlp ''
set head off
set pages 0
spool compile_objects.sql
select 'ALTER '||
DECODE(object_type, 'VIEW', 'VIEW', 'PACKAGE') || ' '||
LOWER(object_name)|| ' COMPILE '||
DECODE(object_type, 'PACKAGE', 'SPECIFICATION', 'PACKAGE BODY', 'BODY', '')|| ' ; ' from user_objects
where status = 'INVALID'
order by DECODE(object_type, 'VIEW', 1, 'PACKAGE', 2, 'PACKAGE BODY', 3), object_name ;
spool off
set echo on
set feed on
set sqlp 'SQL>'
set head on

Once the compile.sql is executed, execute the compile_objects.sql generated by compile.sql

Script for getting Oracle table size

There is no oracle defined function for getting size of a table. After all if it is easy with one simple query who will require a function. Isn't it?

Anyway you can choose to save this query as a function for easy retrieval.

select
segment_name table_name,
sum(bytes)/(1024*1024) table_size_meg
from user_extents
where segment_type='TABLE'
and segment_name = '&table_name'
group by segment_name;

Read more on what all to remember while getting the size of a table. Click here

Create your own function for the purpose:

CREATE OR REPLACE FUNCTION get_table_size
(t_table_name VARCHAR2)RETURN NUMBER IS
l_size NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024)
INTO l_size
FROM user_extents
WHERE segment_type='TABLE'
AND segment_name = t_table_name;

RETURN l_size;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/

Example:
SELECT get_table_size('EMP') Table_Size from dual;

Result:
Table_Size
0.0625

LONG to BLOB Migration

In release 8.1, a new SQL function, TO_LOB, copies data from a LONG column in a table to a LOB column. The datatype of the LONG and LOB must correspond for a successful copy. For example, LONG RAW data must be copied to BLOB data, and LONG data must be copied to CLOB data. In the next example we show how to migrate a table with one LONG to a CLOB datatype.

Create the LOB Tablespace

CREATE TABLESPACE lob1
DATAFILE '/lh4/lob1.dbf' SIZE 2048064K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
PERMANENT
ONLINE;

Disable temporarily all Foreign Keys

set feed off;
spool gen_dis_cons.sql;
SELECT 'ALTER TABLE ' table_name
' DISABLE CONSTRAINT ' constraint_name ';'
FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%'
/
spool off;
set feed on;
@gen_dis_cons.sql;

Convert LONG to LOB in temporary Table

Create a temporary table with converted BLOB field.

CREATE TABLE lob_tmp
TABLESPACE tab
AS SELECT id, TO_LOB(bdata) bdata FROM document;

Drop and Rename Tables

DROP TABLE document;
RENAME lob_tmp TO document;

Create the necessary Constraints and enable the Foreign Keys again

set feed off;
set heading off;
spool gen_ena_cons.sql;
SELECT 'ALTER TABLE ' table_name
' ENABLE CONSTRAINT ' constraint_name ';'
FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%'
/

spool off;
set feed on;
@gen_ena_cons.sql;

Courtesy: akadia

Script to know which tablespace are full

This script lists the tablespace which cannot extent causing tablespace full.

select a.owner||'.'||a.segment_name "Segment Name",
a.segment_type "Segment Type",
a.bytes/1024/1024 "Size(MB)",
a.next_extent/1024/1024 "Next Extent",
a.tablespace_name "Tablespace Name"
from sys.dba_segments a
where a.tablespace_name not like 'T%MP%' -- Exclude TEMP tablespaces
and next_extent * 1 > ( -- Cannot extend 1x, can change to 2x...
select max(b.bytes)
from dba_free_space b
where a.tablespace_name = b.tablespace_name)
order by 3 desc;