Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to check if a database object in Oracle is a table or view

Tags:

sql

oracle

views

I have list of object names from which i have to find out whether the object is a table or view. For this reason i have to query and check in all_tables and all_views and confirm whether the object is table or view. I am using below queries and its working. But as i have huge list of object names I want to perform this in single query and check whether the object is table or view and also the owner of the object.

select * from ALL_views where view_name like '%INSTANCE%'

select * from all_tables where table_name like '%INSTANCE%'
like image 370
Andrew Avatar asked Aug 06 '15 10:08

Andrew


People also ask

How can you tell if a table is a view?

Check if an Object is a Table, View, or Stored Procedure in SQL Server using the OBJECTPROPERTY() Function. In SQL Server you can use the OBJECTPROPERTY() function to check an object's type. More specifically, you can check whether it is or isn't a specific type.

Is view an object in Oracle?

Oracle provides object views as an extension of the basic relational view mechanism. By using object views, you can create virtual object tables from data - of either built-in or user-defined types - stored in the columns of relational or object tables in the database.

Is view a DB object?

View – This database object is used to create a view in database. A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables.

How to check if a table is being used in Oracle?

How to check if a table is being used in oracle ? Goal is to fetch the query's which are using 'ABC' table before we drop 'ABC' Table. Analysis is required to know when was table last used and what queries are hitting on ' ABC' table before we plan to drop it. I wrote this below query which is functional but seems that it is not accurate.

What does it mean to verify a view in SQL?

Sometimes while working in SQL we often forget the names of the view or sequence or index or synonyms or trigger we earlier created. Also it may happen that we want to verify them in future. Verifying means that we are checking for all the present database object or Trigger in that particular schema.

How do I track suspicious activities in Oracle Database?

Audit records can be stored in either a data dictionary table, called the database audit trail, or in operating system files, called an operating system audit trail. Oracle Database also provides a set of data dictionary views that you can use to track suspicious activities. See Oracle Database Security Guide for more information about these views.

How do I enable auditing for SELECT statements in Oracle Database?

Restart the Oracle Database instance. At this point, you can check the AUDIT_TRAIL setting by entering the following command: Next, enable auditing for SELECT statements on the OE.CUSTOMERS table. Ensure that the sample user sec_admin exists. Log on as SYSTEM, and then from the Database Control home page, click Server to display the Server subpage.


2 Answers

select *
  from all_objects 
 where object_name like '%INSTANCE%'

There is an OBJECT_TYPE column in there.

like image 196
MarioAna Avatar answered Oct 19 '22 19:10

MarioAna


How about using all_objects instead?

E.g.:

select owner,
       object_name,
       object_type
from   all_objects
where  object_type in ('TABLE', 'VIEW')
and    object_name in (....);
like image 30
Boneist Avatar answered Oct 19 '22 20:10

Boneist