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%'
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.
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.
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 ? 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.
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.
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.
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.
select *
from all_objects
where object_name like '%INSTANCE%'
There is an OBJECT_TYPE column in there.
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 (....);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With