1 2 3 Previous Next 33 Replies Latest reply: Mar 24, 2014 9:21 AM by BluShadow Go to original post RSS
      • 15. Re: Cleanup Prod Schema dilemma
        Nicolas.Gasparotto

        John Stegeman wrote:

         

        +1

         

        The real answer to this problem is:

         

        1). Don't give people privileges to create tables in production.

        2). Don't allow things to be deployed to production without documentation.

         

        However, both of those would have been needed to be implemented in the past to have been useful

        3) Why on earth wanted to drop existing "unused" tables ? Especially when it has already been asked and answered

         

        Nicolas.

        • 16. Re: Cleanup Prod Schema dilemma
          EdStevens

          Baris Yildirim wrote:

           

          they don't give certian informantion, but dba_hist_sql_plan, dba_hist_active_sess_history can help you

           

          Regards

          If you are licensed to use them.

          • 17. Re: Cleanup Prod Schema dilemma
            Baris Yildirim

            as I said before, it's not certian

             

            SELECT DISTINCT SQL_ID FROM dba_hist_sql_plan where object_name='your_table_name'

             

            Regards

            • 18. Re: Cleanup Prod Schema dilemma
              sb92075

              >I am cleaning up unused tables in our PROD schema

              by definition, unused table have NO impact on performance since they never are accessed!

              • 19. Re: Cleanup Prod Schema dilemma

                But i need to freeup space too

                 

                Thanks

                • 20. Re: Cleanup Prod Schema dilemma
                  andrewmy

                  You are treading a dangerous path here. Deleting objects which you think are unused but are actually not can cause your application to behave strangely or stop working unexpectedly. While there may be not 100% surety with any approach, given that object references may be hidden outside of the database, you want to get as close to that percentage before entering that truncate or drop command. This is the reason why in a poorly documented application nobody cleans up - because nobody knows who or what may be using those seemingly unused tables.

                   

                  You want to rule out all the possible methods a database object can be referenced from inside or outside the database

                  a) Look for internal dependencies using dba_dependencies

                  b) Look for external dependencies - reading design documentation (if any!), scanning source codes, client-side SQL scripts, Sql*loader  jobs, etc

                  c) Enable auditing - that will allow you to keep tabs on object usage for a while (say a  couple of months) just to be doubly safe that you have not missed anything in your search

                  After all that there may still be things you have missed such as tables that are only used once a year for the annual report or only triggered in very specific once-in-a-blue-moon situations.

                   

                  Then if you are 99% sure it isn't used, then BACKUP the table before truncating or dropping it.

                  • 21. Re: Cleanup Prod Schema dilemma

                    you mean I have to read everyting in tahiti.com from cover to cover

                    I bet you have not done that too

                    • 22. Re: Cleanup Prod Schema dilemma
                      sb92075

                      >you mean I have to read everyting in tahiti.com from cover to cove

                      You only need to read 1 page which is the page that contains the answer to your question.

                      • 23. Re: Cleanup Prod Schema dilemma
                        sb92075

                        >How long does table name entries in v$segment_statistics aged out?

                        what is the oldest object recorded V$SEGMENT_STATISTICS

                        • 24. Re: Cleanup Prod Schema dilemma
                          Aman....

                          petra-K wrote:

                           

                          you mean I have to read everyting in tahiti.com from cover to cover

                          I bet you have not done that too

                          No one does that but what most of us do is to know what to search and check that book in teh documentation. Suggest that you do the same.

                           

                          Aman....

                          • 25. Re: Cleanup Prod Schema dilemma
                            EdStevens

                            petra-K wrote:

                             

                            you mean I have to read everyting in tahiti.com from cover to cover

                            I bet you have not done that too

                            Where did anyone say you need to read tahiti.com  "cover to cover"?

                             

                            No one does that, but as Aman said, you need to know where and how to search it.

                             

                            Learning how to look things up in the documentation is time well spent investing in your career.  To that end, you should drop everything else you are doing and do the following:

                             

                            Go to  docs.oracle.com.

                             

                            Locate the link for your Oracle product and version, and click on it.

                             

                            You are now at the entire documentation set for your selected Oracle product and version.

                             

                            BOOKMARK THAT LOCATION

                             

                            Spend a few minutes just getting familiar with what is available here. Take special note of the "books" and "search" tabs. Under the "books" tab (for 10.x) or the "Master Book List" link (for 11.x) you will find the complete documentation library.

                             

                            Spend a few minutes just getting familiar with what kind of documentation is available there by simply browsing the titles under the "Books" tab.

                             

                            Open the Reference Manual and spend a few minutes looking through the table of contents to get familiar with what kind of information is available there.

                             

                            Do the same with the SQL Reference Manual.

                             

                            Do the same with the Utilities manual.

                             

                            You don't have to read the above in depth.  They are reference manuals.  Just get familiar with what is there to be referenced. Ninety percent of the questions asked on this forum can be answered in less than 5 minutes by simply searching one of the above manuals.

                             

                            Then set yourself a plan to dig deeper.

                            - *Read a chapter a day from the Concepts Manual*.

                            - Take a look in your alert log.  One of the first things listed at startup is the initialization parms with non-default values. Read up on each one of them (listed in your alert log) in the Reference Manual.

                            - Take a look at your listener.ora, tnsnames.ora, and sqlnet.ora files. Go to the Network Administrators manual and read up on everything you see in those files.

                            - *When you have finished reading the Concepts Manual, do it again*.

                             

                            Give a man a fish and he eats for a day. Teach a man to fish and he eats for a lifetime.

                            • 26. Re: Cleanup Prod Schema dilemma
                              Nicolas.Gasparotto

                              EdStevens wrote:

                              ...

                               

                              Give a man a fish and he eats for a day. Teach a man to fish and he eats for a lifetime.

                              For some, it's hopeless.

                               

                              Nicolas.

                              • 27. Re: Cleanup Prod Schema dilemma
                                EdStevens

                                Nicolas.Gasparotto wrote:

                                 

                                EdStevens wrote:

                                ...

                                 

                                Give a man a fish and he eats for a day. Teach a man to fish and he eats for a lifetime.

                                For some, it's hopeless.

                                 

                                Nicolas.

                                True.  If you really want to see hopeless, check out the thread at OraFAQ regarding external tables:  Problem in external table

                                • 28. Re: Cleanup Prod Schema dilemma

                                  Guys, do you have ideas on Label Security?

                                  • 29. Re: Cleanup Prod Schema dilemma
                                    EdStevens

                                    petra-K wrote:

                                     

                                    Guys, do you have ideas on Label Security?

                                    Gee, could you make the question any more vague, and any more off-topic for this thread?