Category How to check if a table is being used in oracle

How to check if a table is being used in oracle

By using our site, you acknowledge that you have read and understand our Cookie PolicyPrivacy Policyand our Terms of Service. Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up. I am an Oracle DBA. Application owner is asking me when was the last time Oracle database was accessed?

How do I find information about the DB usage timeline? I mean, I want to check whether it is being used by the application or not?

how to check if a table is being used in oracle

Unfortunately the application owner has no idea whether her application is used by the users or not. From DBA's side which data dictionary should I look into? Can ASH help? Take a look at it this thread on AskTom :. Auditing is the right answer, funny thing with auditing - we only really want it when it isn't on don't we :.

If you don't have any database internal mechanisms like auditing in place, you could also look at the listener log. What do you know about the application? Sometimes the easiest way is to grep listener.

Auditing is automatically turned on for logons and logoffs. You can query this DBA view and get when users logged on or off. This would tell you if somebody accessed the database.

Sign up to join this community. The best answers are voted up and rise to the top. Home Questions Tags Users Unanswered. Ask Question. Asked 5 years, 2 months ago. Active 5 years, 2 months ago. Viewed 8k times.

Jack says try topanswers. Pratik Mehta Pratik Mehta 11 1 1 silver badge 3 3 bronze badges.October 26, by Kenneth Fisher.

how to check if a table is being used in oracle

After some research I found the system table syscomments. This has a few problems. First ABC could be listed in the comments and not in any real code.

how to check if a table is being used in oracle

Microsoft added the system view sys. The code I used in SQL was as follows:. The field sys. This means that SQL stores long pieces of code in a single row instead multiple.

This DMV returns any object that references the object you pass it. While either of the older two pieces of code will still work there is a big advantage to using the DMV. Table1 and SchemaB. Table1 are two different objects after all. Next we need to look at any code that is referenced by the SP. Not a big deal in this example but in a multi page SP it could get a bit trickier.

The easiest way to get a list of all of the code called by an object or trigger is to use the system SP sys. Side note: there is also a system sp sys. Unfortunately I think object level is as far as you can get. You are commenting using your WordPress. You are commenting using your Google account. You are commenting using your Twitter account. You are commenting using your Facebook account. Notify me of new comments via email.

Notify me of new posts via email. This site uses Akismet to reduce spam. Learn how your comment data is processed. RSS - Posts.Even this approach has some pitfalls but should be good enough for most requirements. The approach is limited by the policies you set for AWR repository. So testtest and validate every approach as every application is different. Tagged: How to check whether oracle stored procedure is being usedHow to check whether Oracle table is being usedHow to check whether stored procedure is being usedHow to check whether table or index is being usedprocedureunsed procedure.

With this approach you are creating your own history tables manually. Thanks thats good info… I have another question, can we know how many times that object index being used by application in perticular time frame.

So test and validate every approach as every application is different.

Index Monitoring

Index Monitoring : Tried and Trusted method. You are commenting using your WordPress. You are commenting using your Google account.

how to check if a table is being used in oracle

You are commenting using your Twitter account. You are commenting using your Facebook account. Notify me of new comments via email. Notify me of new posts via email. TwelveC Oracle12c And Beyond. Most PopularOracle June 3, Comments: 8. So testtest and validate every approach as every application is different thanks.

Share this: Twitter Facebook. Like this: Like Loading Yusuf June 17, at am Reply. Thanksexcellent info. Any idea how to do this in a standard edition? TimPhillsen December 1, at pm Reply. Cool post as for me. Thnx a lot for posting that information. Tim Phillsen. Tommie Leonti December 9, at am Reply. I am continually looking online for posts that can benefit me. Lakshman September 6, at am Reply. Shan Nawaz September 12, at pm Reply. So test and validate every approach as every application is different 1.

Thanks Shan. Leave a Reply Cancel reply Enter your comment here Fill in your details below or click an icon to log in:. Email required Address never made public.

Name required.By using our site, you acknowledge that you have read and understand our Cookie PolicyPrivacy Policyand our Terms of Service.

How to find if Oracle Procedure/Table/Index is used?

The dark mode beta is finally here. Change your preferences any time. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. I'm also checking if this answer can help me in any way. Learn more.

How to identify all stored procedures refering a particular table in Oracle Ask Question. Asked 3 years, 1 month ago. Active 3 years, 1 month ago. Viewed 9k times. I will appreciate any assistance. Binyamin Regev Binyamin Regev 1 1 gold badge 10 10 silver badges 25 25 bronze badges. Active Oldest Votes. MT0 MT0 Also, it would be good if you use admin user login which. Normal user may not have access to list the dependencies.

This solution will not list the stored sub programs where the table is referred in dynamic SQL. Sign up or log in Sign up using Google. Sign up using Facebook.

Sign up using Email and Password. Post as a guest Name. Email Required, but never shown.If nobody is using a table, do we still need it? Of course Oracle tracks the tables in use. This view contains a lot of information about any all of the different segments in your Oracle database. A [segment][seg] is the set of extents data blocks allocated to a single database object.

This approach will show us information about reads, writes, and segment scans full table scans since the dynamic view was last cleared. Typically, this is going to show us information since the database was last started. In order to determine which queries are hitting which tables, we can start sampling the current waits in the system. For disk related waits, we only need the p1 and p2 waits.

These refer to the file and block number that are a part of the wait. The problem with this approach, though, is that it will only catch the queries that are running at the moment you sample. With enough samples, this can be effective, but sampling the system is going to put a small load on it. Active Session History samples data over time and lets us know which queries have been running inside a given time window.

You can use this query as a basis to help you isolate who is using which tables and how frequently the queries are being run. So, there you have it — three ways to find some version of the truth. A very quick google search turns up this article from Tom Kyte When was the last time, a table was accessed? Thanks for your response. I have already gone across what Tom suggest. It is mainly related with the auditing stuff.

Even in the best of times, there are plenty of day-to-day concerns besides your SQL Servers, but we can make sure you have one less. I love teaching, travel, and laughing. I live in California with my wife Erika. Want to advertise here and reach my savvy readers?

Which Tables are Being Used in Oracle? March 18, Jeremiah Peschka.By using our site, you acknowledge that you have read and understand our Cookie PolicyPrivacy Policyand our Terms of Service. Server Fault is a question and answer site for system and network administrators.

It only takes a minute to sign up. How to find out if possible via Enterprise Managerthe total in Mb that a certain table is using? I would think not. Here is one that I am finding useful:. After some google searches and experiments, I've built the following query, which provides to my mind the most accurate result. You can easily extend it in order to provide additional statistics like block counts.

Sign up to join this community. The best answers are voted up and rise to the top. Home Questions Tags Users Unanswered. Oracle: how to find out storage space used by a table?

Java & MySQL - Check if a value already exists

Ask Question. Asked 10 years ago. Active 5 months ago. Viewed k times. Active Oldest Votes. Via OEM 10g, Connect to the database as usual Click on the Schema tab On the Tables link within Database Objects Enter the schema name and object name optional and click Go Use the radio button to select the table you want to look at and click on Edit Don't click on the table name link Click on the Segments tab and wait You will see the size of the table data and the indexes used.

OK, that technically answered your question. I like to save the script as t. Guy Guy 2, 2 2 gold badges 16 16 silver badges 24 24 bronze badges. Obscure 3 3 bronze badges.In Oracle The database maintains all indexes defined against a table regardless of their usage. With this in mind, it makes sense to identify and remove any indexes that are not being used as they are a pointless drain on resources.

Index monitoring allows unused indexes to be identified accurately, removing the risks associated with dropping useful indexes. It is important to make sure that index monitoring is performed over a representative time period.

If you only monitor indexes during specific time frames you may incorrectly highlight indexes as being unused. The safest method is to use a monitoring period which spans the whole lifecycle of your application, including any OLTP and batch operations. If you are using a version prior to Oracle From Oracle When a foreign key is unindexed, DML on the parent primary key results in a share row exclusive table lock or share-subexclusive table lock, SSX on the child table, preventing DML from other transactions against the child table.

If the DML affects several rows in the parent table, the lock on the child table is obtained and released immediately for each row in turn. When a foreign key is indexed, DML on the parent primary key results in a row share table lock or subshare table lock, SS on the child table.

This type of lock prevents other transactions from issuing whole table locks on the child table, but does not block DML on either the parent or the child table. Only the rows relating to the parent primary key are locked in the child table. This issue is mitigated somewhat in later releases, but it is still worth considering, and above all testing.

Index monitoring is only a guideline and does not list all indexes used by the system. Prior to Oracle Once the statement runs, other indexes for recursive SQL, like constraint checking, will also be referenced, but may not be included in the object usage views. Use index monitoring as a guide line, not an absolute.

As mentioned previously, the monitoring is only as good as the sample period you observe. Imagine you are checking usage for 11 months, decide to drop some indexes, then the following month you run some yearly reports which rely on those indexes.

Once again, use index monitoring as a guide line, not an absolute. Forums Aggregator Privacy policy.


JoJok

Website: