Enumerate Oracle tables in PLSQL

oracleplsql

How do you run a query to list all tables, or all tables where tableName like '%abc%'.

Just poking around, I tried the following with no success:

select * from SYS.TAB$
select * from SYS.OBJ$ where Name like '%abc%'

I'm trying to re-learn in Oracle some basic things I know in MSSQL.

Best Answer

Depending on your privilege level, you'd want to use DBA_TABLES, ALL_TABLES, or USER_TABLES. There is a StackOverflow thread that walks through this in some detail.

By default, Oracle identifiers are going to be stored in upper case in the data dictionary and queries are case-sensitive. Most likely, if you have a table whose name contains the string "abc", you'd need to search in upper case, i.e.

SELECT *
  FROM dba_tables
 WHERE table_name LIKE '%ABC%'

Related Solutions

Alter in PL SQL Loop

Figured it out. I saw documentation about the using keyword which apparently wasn't what I needed. Instead I needed to use string concatenation ||.

Here's the working version:

SET SERVEROUTPUT ON
declare

begin
    for rec in (select distinct Profile from dba_users) loop
     EXECUTE IMMEDIATE 'alter profile ' || rec.PROFILE || ' LIMIT PASSWORD_LIFE_TIME UNLIMITED';
     EXECUTE IMMEDIATE 'alter profile ' || rec.PROFILE || ' LIMIT PASSWORD_REUSE_MAX UNLIMITED';
    end loop;
end;
/

Oracle 10XE: Stop schemas seeing the all_users table

You have a few options, some of which will make your database unsupportable by Oracle (if you care!) as you need to fiddle with SYS owned objects.

The first option is to revoke select privs from everyone:

revoke select on all_users from public;

Then grant it back to certain users, plus all other internal Oracle users.

The 2nd option is to look at the source for the view, drop it, then recreate an altered version that supports your needs.

Another option that would trick casual users would be to create a table called ALL_USERS in each schema. Unless the logged in person explicitly qualified ALL_USERS (ie: SYS.ALL_USERS) they would get the resultset from the table in their own schema. This trick could also be done with a synonym pointing to a view that does:

select * from all_users where username = (SELECT sys_context('USERENV', 'SESSION_USER') from DUAL);

Not much else you can do with XE I'm afraid.

Best Answer

Related Solutions

Alter in PL SQL Loop

Oracle 10XE: Stop schemas seeing the all_users table

Related Question