How to query all tables in oracle

Many new users may not know how to query all tables and data in oracle. The following describes how to query all tables in oracle for your reference.

 
 

  
  
SELECT t. data_type
  
  
FROM user_tab_columns t
  
  
Group by t. data_type;
  
  
-- Created on 2010-8-26 by RONGZHI_LI
  
  
DECLARE
  
  
CURSOR ot (namelike IN VARCHAR2)
  
  
IS
  
  
(SELECT owner AS uname,
  
  
Object_name AS tname
  
  
FROM all_objects
  
  
WHERE object_type = 'table'
  
  
AND owner LIKE namelike
  
  
);
  
  
CURSOR tc (username IN all_tab_columns.owner % TYPE, tablename IN all_tab_columns.table_name % type)
  
  
IS
  
  
(SELECT t. owner as uname,
  
  
T. table_name AS tab,
  
  
T. column_name AS col,
  
  
T. data_type data
  
  
FROM all_tab_columns t
  
  
WHERE t. owner = username
  
  
AND t. table_name = tablename
  
  
);
  
  
Sqlstr VARCHAR2 (200 );
  
  
Findsqlstr VARCHAR2 (200 );
  
  
Foundcount NUMBER;
  
  
Data_type VARCHAR2 (100): = '';
  
  
Logic_symbol VARCHAR2 (10): = ';
  
  
Data_value VARCHAR2 (100 );
  
  
-- Set it by yourself
  
  
Name_like VARCHAR2 (100): = '% HS _ % ';
  
  
Find_type VARCHAR2 (100): = 'varchar2'; -- 'varchar2'; number
  
  
Find_value VARCHAR2 (100): = 'icbc ';
  
  
Use_like BOOLEAN: = true;
  
  
BEGIN
  
  
Data_type: = upper (find_type );
  
  
Data_value: = find_value; -- default value first
 
 

-- 1. Determine whether the type is a string

 
 

  
  
IF data_type = 'varchar2' THEN -- special character string judgment
  
  
Data_value: = ''' | find_value | '''; -- Note: the pl/SQL display ''is''
  
  
End if;
 
 

-- 2. determine whether to use like. This should be compatible with the varchar2 type.

 
 

  
  
IF use_like THEN
  
  
Logic_symbol: = 'like ';
  
  
Data_value: = ''' % '| find_value |' % '''; -- Note: pl/SQL displays ''''
  
  
End if;
  
  
--
  
  
FOR aot IN ot (name_like)
  
  
LOOP
  
  
FOR atc IN tc (aot. uname, aot. tname)
  
  
LOOP
  
  
Foundcount: = 0;
  
  
IF atc. DATA = data_type THEN
  
  
Sqlstr: = 'select count (*) from' | atc. uname | '.' | atc. tab | 'where' | atc. col
  
  
| Logic_symbol | data_value;
  
  
-- Dbms_output.put_line (sqlstr); -- used for debug
  
  
EXECUTE immediate sqlstr INTO foundcount;
  
  
IF foundcount> 0 THEN
  
  
Findsqlstr: = 'select * from' | atc. uname | '.' | atc. tab | 'where' | atc. col |
  
  
Logic_symbol | data_value | '; --' | foundcount;
  
  
Dbms_output.put_line (findsqlstr );
  
  
End if;
  
  
End if;
  
  
End loop;
  
  
End loop;
  
  
Dbms_output.put_line ('end of lookup ');
  
  
END;
 
 

The preceding describes how to query all tables in oracle.

Oracle Stored Procedure debugging method

Learn about the physical structure of Oracle

Setting of Oracle automatic archiving Mode

Provides you with an in-depth understanding of the Oracle archiving mode.

How to delete ORACLE archive logs