Ask Anantha

Question: How to find any value in any column in a schema? I have been searching for this answer for long, and stumbled upon a beautiful response in Oracle Forums and tried the block and stunning to find the output much easily. This saved my day as I was searching for occurence of a value in entire schema. Thanks for pollywog for the posting. I will reproduce the anonymous block here for my readers: declare aCount pls_integer; begin for c in (select table_name, column_name  from all_tab_columns  where owner = 'OWNER'  and data_type = 'DATATYPE') loop execute immediate 'select count(*) from '||c.table_name||' where '||c.column_name||' = ''value_to_find'' ' into aCount; if aCount > 0 then dbms_output.put_line('found value_to_find in table '||c.table_name||', column '||c.column_name); end if; end loop; end; Just modify the following in the block: 1. OWNER (The schema in which find is requ...

Article and Script Courtesy :  Niels Teusink The Oracle  wrap  utility can be used to obfuscate PL/SQL code, to ensure it can't be easily read. Pete Finnigan described ( pdf ) the wrapping process for Oracle 9g, but for 10g and 11g it still remains a bit of a mystery. I decided to release my Python unwrapping utility (supports 10g and 11g). The unwrapping steps for 10g are nicely described in the  Oracle Hacker's Handbook , but the actual substitution table needed to decode the package is omitted. Nobody (as far as I know) has published it. A lot of people seem to know how to do it though, there is even an  online unwrapper  available (and I'm sure everyone seriously involved in Oracle security knows how to do it). A Russian-made closed source tool is also available, but tends to upset virus scanners. So to save everyone a couple of hours of figuring it out, here it is:  unwrap.py It's easy to use (I've used the wrapped procedure from  th...

The following SQL script will list out all users with its status: SELECT username, account_status FROM dba_users ;

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.sq...

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

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 Rena...

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;