Поиск текстового значения в таблицах схемы Oracle RDBMS

1. Создать следующую функцию:
   

   sql> CREATE OR REPLACE FUNCTION FIND_STR_VALUE(  
       SCHEMA_NAME IN VARCHAR2 ,  
       FIND_STR    IN VARCHAR2 )  
      RETURN VARCHAR2  
     AS  
      match_count   INTEGER;  
      query_str     VARCHAR2(300);  
      result_string VARCHAR2(32767);  
     BEGIN  
      result_string:='TABLE_NAME COLUMN_NAME COUNT'||chr(13)||chr(10);  
      FOR t IN  
      (SELECT table_name, column_name, owner  
      FROM all_tab_columns a  
      WHERE a.owner=SCHEMA_NAME AND 
            a.data_type IN ( 'VARCHAR2', 'NVARCHAR2','VARCHAR','CHAR','NCHAR'))  
      LOOP  
       match_count := 0;  
       query_str  := 'SELECT COUNT(*) FROM '||t.owner||'.' || t.table_name || 
                     ' WHERE to_char(' || t.column_name || ') LIKE :1';  
       EXECUTE IMMEDIATE query_str INTO match_count USING '%'||FIND_STR||'%';  
       IF match_count > 0 THEN  
        result_string:=result_string|| t.table_name ||' '||t.column_name||
                       ' '||match_count||chr(13)||chr(10);  
       END IF;  
      END LOOP;  
      RETURN result_string;  
     END FIND_STR_VALUE;  
   

2. Запустить выполнение функции (первый параметр имя схемы, второй - строка для поиска):
   

   sql> SELECT find_str_value('APEX_040100','welcome') FROM dual; 
   

3. Пример выполненного запроса:
   

   TABLE_NAME | COLUMN_NAME | COUNT  
   WWV_FLOW_STEP_ITEMS | ITEM_DEFAULT | 1
   WWV_FLOW_STEP_ITEM_HELP | HELP_TEXT | 2 
   APEX_APPLICATION_PAGE_DB_ITEMS | HELP_TEXT | 1 
   APEX_APPLICATION_PAGE_ITEMS | ITEM_HELP_TEXT | 2  
   APEX_APPLICATION_PAGE_ITEMS | COMPONENT_SIGNATURE | 1  
   APEX_APPLICATION_PAGE_ITEMS | ITEM_DEFAULT | 1
   

Заметки по Oracle RDBMS

Подключение к СУБД через sqlplus с использованием Easy connnect:

 $ sqlplus userName/userPass@serverHost:serverPort/serviceName  

Просмотр информации о свободном пространстве в табличных пространствах:

 SELECT /* + RULE */ df.tablespace_name "Tablespace",  
     df.bytes / (1024 * 1024) "Size (MB)",  
     SUM(fs.bytes) / (1024 * 1024) "Free (MB)",  
     Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",  
     Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"  
  FROM dba_free_space fs,  
     (SELECT tablespace_name,SUM(bytes) bytes  
      FROM dba_data_files  
      GROUP BY tablespace_name) df  
  WHERE fs.tablespace_name (+) = df.tablespace_name  
  GROUP BY df.tablespace_name,df.bytes  
 UNION ALL  
 SELECT /* + RULE */ df.tablespace_name "Tablespace",  
     fs.bytes / (1024 * 1024) "Size (MB)",  
     SUM(df.bytes_free) / (1024 * 1024) "Free (MB)",  
     Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1) "% Free",  
     Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes) "% Used"  
  FROM dba_temp_files fs,  
     (SELECT tablespace_name,bytes_free,bytes_used  
      FROM v$temp_space_header  
      GROUP BY tablespace_name,bytes_free,bytes_used) df  
  WHERE fs.tablespace_name (+) = df.tablespace_name  
  GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used  
  ORDER BY 4 DESC;  

'Завершить' все неактивные сессии:

 begin     
   -- ALTER SYSTEM ENABLE RESTRICTED SESSION;  
   for x in (   
       select Sid, Serial#, machine, program   
       from v$session   
       where  status='INACTIVE'   
     ) loop   
     execute immediate 'Alter System Kill Session '''|| x.Sid   
            || ',' || x.Serial# || ''' IMMEDIATE';   
   end loop;   
 end;   

Сделать бессрочным срок действия пароля пользователя:

 ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;  

Увеличить размер табличного пространства:

 ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;  

Поиск дубликатов в таблице:

 SELECT column_name, COUNT(column_name)
 FROM table_name
 GROUP BY column_name
 HAVING COUNT (column_name) > 1

Создание DBLink с использованием Easy connnect:

CREATE PUBLIC DATABASE LINK dbLinkName
   CONNECT TO userName identified by userPassword
   USING '(DESCRIPTION =    
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = portNumber))    
    (CONNECT_DATA =      
    (SERVICE_NAME = serviceName)))';