Thursday, April 9, 2009

How to move/rename a datafile while database is online but tablespace is required to be taken offline

1-) First take tablespace offline to which datafile belongs:

ALTER TABLESPACE MYTABLESPACE OFFLINE NORMAL;

2-) Move datafile to new location

mv /datadir/datafile.dbf /newdatadir/datafile.dbf

3-) Rename datafile name in database

ALTER TABLESPACE MYTABLESPACE RENAME DATAFILE '/datadir/datafile.dbf' TO '/newdatadir/datafile.dbf';

4-) Bring tablespace online:

ALTER TABLESPACE MYTABLESPACE ONLINE;

Wednesday, April 1, 2009

Searching dba_views giving text condition

If you try to run a query like this:

SQL>select * from dba_views where text like '%some text here%';

following error occurs:

select * from dba_views where text like '%some text here%'
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

You can use following function to search dba_views giving text condition:

create or replace
FUNCTION SEARCH_VIEW_TEXT( in_owner varchar,in_view_name varchar, in_search_str
varchar)
RETURN INTEGER IS
w_crs integer;
r_val integer;
num_bytes INTEGER := 32000;
offset INTEGER := 0;
out_val VARCHAR2(32000);
out_length INTEGER;
begin
w_crs := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(w_crs, 'select text from dba_views where owner=:OWNR and view_name=:VNAME ',dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(w_crs, ':OWNR', in_owner);
DBMS_SQL.BIND_VARIABLE(w_crs, ':VNAME', in_view_name);
DBMS_SQL.DEFINE_COLUMN_LONG(w_crs,1);
r_val := DBMS_SQL.EXECUTE(w_crs);
r_val := DBMS_SQL.FETCH_ROWS(w_crs);
LOOP
DBMS_SQL.COLUMN_VALUE_LONG(w_crs, 1, num_bytes, offset, out_val, out_length);
IF (INSTR(UPPER(out_val),upper(in_search_str))>0) THEN
DBMS_SQL.CLOSE_CURSOR(w_crs);
RETURN 1;
END IF;
offset := offset+num_bytes;
IF out_length < num_bytes THEN
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(w_crs);
RETURN 0;

EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(w_crs) then
DBMS_SQL.CLOSE_CURSOR(w_crs);
END IF;
RAISE;
END;

SQL> select * from dba_views where owner not in ('SYS','SYSTEM') and SEARCH_VIEW_TEXT(owner, view_name, 'some text here') = 1