Oracle Data Dictionary

Could anyone shed some light on the following, im not very experienced with oracle and not too sure of the purpose of the data dictionary, so any usefull links would be much appreciated.

The ORACLE data dictionary view USER_OBJECTS  includes the columns

OBJECT_NAME       VARCHAR2(128)
OBJECT_TYPE         VARCHAR2(18)

OBJECT_TYPE may be ‘TABLE’,’ VIEW’, ‘INDEX’, ‘PROCEDURE’, ‘SYNONYM’ etc.

(i)firstly i need to use the technique of generating SQL from SQL, how do I write code that would drop all the views whose names begin with the string ‘TMP’, do you have to refer to a view differently to a table ?

(ii) secondly, amend this code so that all  tables and views are dropped whose name begins with the string ‘TMP’.  Bearing in mind that it is not possible to drop a table containing a primary key that is referred to by a foreign key in another table, would i just use cascase constraints here?

Many Thanks
LVL 2
generaliAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
Personally, I would not use USER_OBJECTS.

I would user USER_VIEWS and USER_TABLES.  Makes it a little easier.

begin
  for rec in (select 'drop view "' || view_name || '"' stmt from user_views where view_name like 'TMP%' union all select 'drop table "' || table_name ||'" cascade constraints' stmt from user_tables where table_name like 'TMP%') loop
    execute immediate rec.stmt;
  end loop;
end;
/

If you just want the statements:

select 'drop view "' || view_name || '";'
  from user_views
 where view_name like 'TMP%'
union all
select 'drop table "' || table_name ||'" cascade constraints;'
  from user_tables
 where table_name like 'TMP%'
;

The double quotes (") are used in case there are mixed case table/view names, or table/view names that are reserved words.
0
 
actonwangCommented:
You can use simple pl/sql block to do it:

drop views:
//////////////////////////

begin
    for rec in (select object_name from user_objects where object_type = 'VIEW' and object_name like 'TMP%')
    loop
        execute immediate 'drop view ' || rec.object_name;
    end loop;
end;
/

drop tables:
////////////////////////

begin
    for rec in (select object_name from user_objects where object_type = 'TABLE' and object_name like 'TMP%')
    loop
        execute immediate 'drop view ' || rec.object_name || ' cascade constraint';
    end loop;
end;
/


acton
0
 
awking00Commented:
for i)

declare

sql_stmnt VARCHAR2(255);

begin
for v in
(select view_name
 from user_views
 where view_name like 'TMP%')
loop
sql_stmnt := 'DROP VIEW '||v.view_name;
execute immediate sql_stmnt;
end loop;

end;

for ii)

declare

sql_stmnt VARCHAR2(255);
v_exist   NUMBER;

begin
for t in
(select table_name
 from user_tables
 where table_name like 'TMP%')
loop
 select count(*)
 into v_exist
 from user_constraints
 where table_name = t.table_name;

 if v_exist > 0 then
 for c in
 (select constraint_name
  from user_constraints
  where table_name = t.table_name)
 loop
 sql_stmnt := 'alter table '||t.table_name||' drop constraint '||c.constraint_name;
 execute immediate sql_stmnt;
 end loop;
 end if;

sql_stmnt := 'drop table '||t.table_name;
execute immediate sql_stmnt;
end loop;
end;
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
actonwangCommented:
user_objects is sufficient for those tasks unless you want more details other than name.
0
 
generaliAuthor Commented:
is sql from sql pl/sql?

also what does union all do?
0
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
You can use either simple SQL statements, like johnsone's second example, which will build the SQL statements you need to execute, based on an SQL query.  Or, you can use PL\SQL as a couple people have suggested.  Either way can work.  Personally, I usually prefer simple SQL to PL\SQL for DDL commands (like: drop view) etc, since PL\SQL is optimized for queries and DML commands (insert, update, delete) and can only do DDL commands via the "execute immediate" syntax.

"What does union all do"?

The "union" or "union all" operator is a way to combine the results of two separate queries into one set of results.  "Union all" is faster but can return duplicates in some cases (not in this case though). "Union" will do a sort to prevent duplicates, so it is slower, and not needed in your case.
0
All Courses

From novice to tech pro — start learning today.