Oracle: Recompile Invalid Objects

Question: 

I  have packages and package bodies going invalid when I make schema changes.  How do I recompile invalid objects?

Answer: 

The Oracle database will invalidate objects if a dependent object is changed. If I rebuild a table, the indexes on that table will become invalid because they use the table's rowids and rebuilding the table changes a row's rowid. It is the same with objects like packages, procedures and functions.

In a development environment with lots of users working on the same objects this can become aggravating. Just remember that someone caused the database to invalidate the object. You can control this by controlling who changes objects in the database, or splitting the development into multiple schemas so that one section does not cause another's objects to become invalid.

You can run this query to find invalid objects, which may cause the ORA-06508 error:
select 
   comp_id, 
   comp_name, 
   version, 
   status, 
   namespace, 
   schema 
from 
   dba_registry;

You can invoke the utl_recomp package to recompile invalid objects:

EXEC UTL_RECOMP.recomp_serial('schema name');

Here is a script to recompile invalid PL/SQL packages, stored procedures, functions and package bodies.  You may need to this script more than once for dependencies, if you get errors from the script.

invalid.sql

Set heading off;
set feedback off;
set echo off;
Set lines 999;

Spool run_invalid.sql

select
   'ALTER ' || OBJECT_TYPE || ' ' ||
   OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
   dba_objects
where
   status = 'INVALID'
and
   object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;

spool off;

set heading on;
set feedback on;
set echo on;

@run_invalid.sql

How to drop USERS tables including its datafiles

DROP USER OE CASCADE;
SELECT * FROM Dba_Data_Files;
SELECT * FROM dba_tablespaces;
CREATE TABLESPACE def
   DATAFILE 'D:\APP\ORACLE\PRODUCT\10.2.0\ORADATA\DBWARE\def01.DBF'
   SIZE 100M AUTOEXTEND ON
ALTER DATABASE DEFAULT TABLESPACE def;
ALTER TABLESPACE USERS OFFLINE;
drop tablespace users including contents and datafiles;

SELECT  'DROP '||TABLE_TYPE||' ' ||TABLE_NAME||' ;'  FROM dba_tables
WHERE TABLESPACE_NAME='USERS';

select t.owner, t.table_name, count(*)
from dba_tables t
join (
  select s.owner, s.segment_name, s.tablespace_name from dba_tables t join dba_segments s on s.owner = t.owner and s.segment_name = t.TABLE_NAME and s.segment_type = 'LOBSEGMENT' group by s.owner, s.segment_name, s.tablespace_name
    ) l
on t.owner = l.owner
and t.table_name = l.segment_name
group by t.owner, t.table_name
having count(*) > 1 ;


select * from dba_segments
where tablespace_name ='USERS'
and segment_type like '%LOB%';



ALTER USER oe IDENTIFIED BY oe ACCOUNT UNLOCK;


SELECT 'DROP '||TABLE_TYPE||' ' ||TABLE_NAME||' ;' from cat ORDER BY TABLE_TYPE;




declare
v_str varchar2(4000);
new_ts varchar2(100):='def';
BEGIN
for c1 in(select table_name, column_name, segment_name from user_lobs where segment_name IN(select segment_name from user_segments where tablespace_name='USERS'))
LOOP
v_str := 'alter table '||c1.table_name||' move tablespace '||new_ts||' lob('||c1.column_name||') store as (tablespace '||new_ts||')';
dbms_output.put_line(v_str);
END LOOP;
END;
/

Oracle PL/SQL: Checking Alpha Numeric

  DECLARE
    CATLG VARCHAR2(50):='&VAL';
    ALPHA NUMBER:=0;
    NUMER NUMBER:=0;
    LEN NUMBER:= LENGTH(CATLG);
  BEGIN
    FOR I IN 1..LEN LOOP
      IF ASCII(SUBSTR(UPPER(CATLG),I,1)) BETWEEN 65 AND 90 THEN
        ALPHA:= ALPHA+1;
      END IF;
      IF SUBSTR(CATLG,I,1) IN ('0','1','2','3','4','5','6','7,','8','9') THEN
        NUMER:= NUMER+1;
      END IF;
    END LOOP;
    IF ALPHA = 0 THEN
      DBMS_OUTPUT.PUT_LINE 
('Alpha Numeric Mandatory Atleast single character required!');
      
    ELSIF NUMER = 0 THEN
      DBMS_OUTPUT.PUT_LINE 
('Alpha Numeric Mandatory Atleast single number required!');
      
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Alpha Numeric String');
    END IF;  
  END;  

Oracle SQL: How to convert comma separated row into column

SQL> WITH T AS
  2   (SELECT 'ABCD,123,DEFOIFCD,87765' AS STR FROM DUAL)
  3  SELECT LEVEL AS N, REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL) AS VAL
  4    FROM T
  5  CONNECT BY REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL) IS NOT NULL
  6  /

         N       VAL
---------- -----------------------
         1       ABCD
         2       123
         3       DEFOIFCD
         4       87765

Apart from Regular Expressions, a few other alternatives are using:

• XMLTable
• MODEL clause

Setup

SQL> CREATE TABLE t (
  2    ID          NUMBER GENERATED ALWAYS AS IDENTITY,
  3    text        VARCHAR2(100)
  4  );

Table created.

SQL>
SQL> INSERT INTO t (text) VALUES ('word1, word2, word3');

1 row created.

SQL> INSERT INTO t (text) VALUES ('word4, word5, word6');

1 row created.

SQL> INSERT INTO t (text) VALUES ('word7, word8, word9');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT * FROM t;

        ID TEXT
---------- ----------------------------------------------
         1 word1, word2, word3
         2 word4, word5, word6
         3 word7, word8, word9

SQL>

There is a huge difference between the below two: splitting a single delimited string splitting delimited strings for multiple rows in a table. If you do not restrict the rows, then the CONNECT BY clause would produce multiple rows and will not give the desired output. For single delimited string, look at Split single comma delimited string into rows For splitting delimited strings in a table, look at Split comma delimited strings in a table Apart from Regular Expressions, a few other alternatives are using: XMLTable MODEL clause Setup SQL> CREATE TABLE t ( 2 ID NUMBER GENERATED ALWAYS AS IDENTITY, 3 text VARCHAR2(100) 4 ); Table created. SQL> SQL> INSERT INTO t (text) VALUES ('word1, word2, word3'); 1 row created. SQL> INSERT INTO t (text) VALUES ('word4, word5, word6'); 1 row created. SQL> INSERT INTO t (text) VALUES ('word7, word8, word9'); 1 row created. SQL> COMMIT; Commit complete. SQL> SQL> SELECT * FROM t; ID TEXT ---------- ---------------------------------------------- 1 word1, word2, word3 2 word4, word5, word6 3 word7, word8, word9 SQL> Using XMLTABLE: SQL> SELECT id, 2 trim(COLUMN_VALUE) text 3 FROM t, 4 xmltable(('"' 5 || REPLACE(text, ',', '","') 6 || '"')) 7 / ID TEXT ---------- ------------------------ 1 word1 1 word2 1 word3 2 word4 2 word5 2 word6 3 word7 3 word8 3 word9 9 rows selected. SQL> Using MODEL clause: SQL> WITH 2 model_param AS 3 ( 4 SELECT id, 5 text AS orig_str , 6 ',' 7 || text 8 || ',' AS mod_str , 9 1 AS start_pos , 10 Length(text) AS end_pos , 11 (Length(text) - Length(Replace(text, ','))) + 1 AS element_count , 12 0 AS element_no , 13 ROWNUM AS rn 14 FROM t ) 15 SELECT id, 16 trim(Substr(mod_str, start_pos, end_pos-start_pos)) text 17 FROM ( 18 SELECT * 19 FROM model_param MODEL PARTITION BY (id, rn, orig_str, mod_str) 20 DIMENSION BY (element_no) 21 MEASURES (start_pos, end_pos, element_count) 22 RULES ITERATE (2000) 23 UNTIL (ITERATION_NUMBER+1 = element_count[0]) 24 ( start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, cv(element_no)) + 1, 25 end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1, cv(element_no) + 1) ) 26 ) 27 WHERE element_no != 0 28 ORDER BY mod_str , 29 element_no 30 / ID TEXT ---------- -------------------------------------------------- 1 word1 1 word2 1 word3 2 word4 2 word5 2 word6 3 word7 3 word8 3 word9 9 rows selected. SQL>