Dev: Find string in table

--
-- Sometimes it is needed to find column/rowid in table with value from screen of web --application etc. That procedure helps to do that, example:
--09:09:43 SQL> create table test as select * from dba_objects;
--...
--09:10:53 SQL> /
--Enter value for table_name: test
--Enter value for string: DBA_OBJECTS
--GENERATED
--OBJECT_NAME
--rowid: AAAMvZAABAAAPk+AAj in SYS.TEST <<-- finding only first occurence
-- Should not be used as is for partitioned tables
--
set serveroutput on
declare
procedure find_string_in_table(val varchar2)
is
v_where Varchar2(32767);
type rc is ref cursor;
c rc;
v_rowid rowid;
begin
for r in (
select
t.owner||'.'||t.table_name table_name, t.column_name
from
dba_tab_cols t
where t.table_name = upper(trim('&table_name'))
and t.data_type like '%CHAR%'
order by t.column_name) loop
dbms_output.put_line(r.column_name);
v_where := ' where ' || r.column_name || ' like ''%' || val || '%''';

open c for 'select rowid from ' || r.table_name || ' ' || v_where;

fetch c into v_rowid;
loop
fetch c into v_rowid;
exit when c%notfound;
dbms_output.put_line(' rowid: ' || v_rowid || ' in ' || r.table_name);
return;
end loop;
end loop;
end;
begin
find_string_in_table('&string');
end;
/

General:10g Soft limit - MAXDATAFILES, MALOGFILES, MAXINSTANCES, MAXLOGMEMBERS

-- no need to re-create controlfile
http://laurentschneider.blogspot.com/2006/01/change-maxdatafiles-malogfiles.html

Capacity: 10g: drop datafile

--
-- Drop empty datafile (not one only one and not first in tablespace)
--
alter tablespace ft add datafile 'C:\PROJECT\OCP\FT.DBF1' size 1m;
Tablespace altered.
alter tablespace ft drop datafile 'C:\PROJECT\OCP\FT.DBF1';
Tablespace altered
--
-- but if datafile not empty
--
alter database datafile 'C:\PROJECT\OCP\FT.DBF1' resize 10m;
Database altered.
alter table test.t_all allocate extent (size 1k datafile 'C:\PROJECT\OCP\FT.DBF1');
Table altered.
--
alter tablespace ft drop datafile 'C:\PROJECT\OCP\FT.DBF1';
alter tablespace ft drop datafile 'C:\PROJECT\OCP\FT.DBF1'
*
ERROR at line 1:
ORA-03262: the file is non-empty
--
-- and for first file
--
alter tablespace ft drop datafile 'C:\PROJECT\OCP\FT.DBF';
alter tablespace ft drop datafile 'C:\PROJECT\OCP\FT.DBF'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace FT

Capacity: 10g Segment growth prediction

select * from
table(dbms_space.OBJECT_GROWTH_TREND
('TEST','TEST','TABLE'));

--
-- set serveroutput off;
-- if - EXCEPTION in chrow processing - code: -14551 msg: ORA-14551: cannot perform a
-- DML operation inside a query
--

General: Oracle parameters

-- Session
v$parameter
-- Current system (for new sessions)
v$system_parameter
-- Current spfile
v$spparameter

rman: controlfile autobackup default location

oracle_home/dbs(database on win)

Recover: check object for curruption

07:31:25 19 4294967295 SYS@orcl> exec dbms_repair.admin_tables(table_type => dbms_repair.repair_table, action => dbms_repair.create_action);

PL/SQL procedure successfully completed.

07:32:33 19 4294967295 SYS@orcl> exec dbms_repair.check_object('SYS', 'FT', CORRUPT_COUNT => :v);

PL/SQL procedure successfully completed.


V
------------------------------------
.0