12 Replies Latest reply: Jan 19, 2013 1:02 AM by 985067 RSS

    DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance

    985067
      I am using Oracle Enterprise Manager and SQL Plus to get this assignment done. I need a veteran who knows how to guide me into accessing the table as instructed.
      "Using the Schema Manager, find the AQ$_QUEUES table in the SYSTEM schema. These tables are part of the Oracle data dictionary." Oracle 10g <<

      Where shall I begin? How would I approach this to find the table in the SYSTEM Schema?

      Here is a screenshot of the Tree from Oracle Enterprise Manager:
      http://i.imgur.com/EiTJQ.png
        • 1. Re: DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance
          sb92075
          982064 wrote:
          I am using Oracle Enterprise Manager and SQL Plus to get this assignment done. I need a veteran who knows how to guide me into accessing the table as instructed.
          "Using the Schema Manager, find the AQ$_QUEUES table in the SYSTEM schema. These tables are part of the Oracle data dictionary." Oracle 10g <<

          Where shall I begin? How would I approach this to find the table in the SYSTEM Schema?

          Here is a screenshot of the Tree from Oracle Enterprise Manager:
          http://i.imgur.com/EiTJQ.png
          SQL> desc system.AQ$_QUEUES
           Name                                      Null?    Type
           ----------------------------------------- -------- ----------------------------
           OID                                       NOT NULL RAW(16)
           EVENTID                                   NOT NULL NUMBER
           NAME                                      NOT NULL VARCHAR2(30)
           TABLE_OBJNO                               NOT NULL NUMBER
           USAGE                                     NOT NULL NUMBER
           ENABLE_FLAG                               NOT NULL NUMBER
           MAX_RETRIES                                        NUMBER
           RETRY_DELAY                                        NUMBER
           PROPERTIES                                         NUMBER
           RET_TIME                                           NUMBER
           QUEUE_COMMENT                                      VARCHAR2(2000)
           SUBSCRIBERS                                        SYS.AQ$_SUBSCRIBERS
           MEMORY_THRESHOLD                                   NUMBER
           SERVICE_NAME                                       VARCHAR2(64)
           NETWORK_NAME                                       VARCHAR2(256)
          • 2. Re: DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance
            985067
            Amazing! Thanks for a detailed response. I will try it out soon and report results. This looks accurate though.
            • 3. Re: DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance
              985067
              I have another question. The next part is what I cannot overcome. I need to run the Analyze Wizard on that same table in the System Schema: AQ$_QUEUES -- How would I go about doing that?

              Can you also provide me with a direct source if possible to find these commands that I would need. Thanks
              • 4. Re: DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance
                sb92075
                982064 wrote:
                I have another question. The next part is what I cannot overcome. I need to run the Analyze Wizard on that same table in the System Schema: AQ$_QUEUES -- How would I go about doing that?
                I do not know what Analyze Wizard is.
                Can you also provide me with a direct source if possible to find these commands that I would need. Thanks
                post the same for your DB
                SQL> select * from v$version;
                
                BANNER
                --------------------------------------------------------------------------------
                Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
                PL/SQL Release 11.2.0.2.0 - Production
                CORE    11.2.0.2.0      Production
                TNS for Linux: Version 11.2.0.2.0 - Production
                NLSRTL Version 11.2.0.2.0 - Production
                • 5. Re: DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance
                  EdStevens
                  982064 wrote:
                  I have another question. The next part is what I cannot overcome. I need to run the Analyze Wizard on that same table in the System Schema: AQ$_QUEUES -- How would I go about doing that?

                  Can you also provide me with a direct source if possible to find these commands that I would need. Thanks
                  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 tahiti.oracle.com.

                  Drill down to your product and version.

                  <b><i><u>BOOKMARK THAT LOCATION</u></i></b>

                  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 <b><i><u>kind</u></i></b> 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 <b><i><u>kind</u></i></b> 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 <b><i><u>reference</b></i></u> manuals. Just get familiar with <b><i><u>what</b></i></u> is there to <b><i><u>be</b></i></u> 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.
                  • 6. Re: DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance
                    985067
                    I am still trying to figure this all out... This is very important or I cannot learn. I've been reading up on the materials and I am still trying to get this stuff to sink in.

                    For the first question you helped me discover "desc AS$QUEUES" to get the list... but now I have to answer these followings.. Where would I answer these questions from? the table after running the command? or where?

                    Who owns this table?
                    What tablespace is it located in?
                    How many columns does the table have?
                    Which column is the Primary Key column?
                    Are there any other constraints associated with this table? If yes, what is the name of the constraint?
                    • 7. Re: DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance
                      sb92075
                      982064 wrote:
                      I am still trying to figure this all out... This is very important or I cannot learn. I've been reading up on the materials and I am still trying to get this stuff to sink in.

                      For the first question you helped me discover "desc AS$QUEUES" to get the list... but now I have to answer these followings.. Where would I answer these questions from? the table after running the command? or where?

                      Who owns this table?
                      query ALL_TABLES
                      What tablespace is it located in?
                      query ALL_TABLES
                      How many columns does the table have?
                      query ALL_TAB_COLUMNS
                      Which column is the Primary Key column?
                      query ALL_CONSTRAINTS
                      Are there any other constraints associated with this table? If yes, what is the name of the constraint?
                      query ALL_CONSTRAINTS
                      • 8. Re: DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance
                        sb92075
                        http://www.oracle.com/pls/db111/search?remark=quick_search&word=data+dictionary&partno=

                        You'd benefit from taking some time to read about the Data Dictionary from URL above
                        • 9. Re: DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance
                          985067
                          This is very helpful and as I am conducting more research, its slowly making more sense. Thank you so much! and thanks for the resources.
                          • 10. Re: DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance
                            sb92075
                            982064 wrote:
                            This is very helpful and as I am conducting more research, its slowly making more sense. Thank you so much! and thanks for the resources.
                            ALL_OBJECTS needs to be used to join other views as a "bridge" between AQ$_QUEUES & ALL_TABLES.
                            • 11. Re: DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance
                              985067
                              Like this? Found this article here: [www.java2s.com/Tutorial/Oracle/0600__System-Tables-Data-Dictionary/Querythealltables.htm|www.java2s.com/Tutorial/Oracle/0600__System-Tables-Data-Dictionary/Querythealltables.htm] ?

                              select owner, table_name
                              2 from all_tables
                              3 where rownum < 30
                              4 order by owner, table_name ;

                              Also, I am trying to find out the answer to these, and as I query them, it seems I am getting closer, but identify which is right seems all confusing.
                              So I did the query, and it tells me the names, what do these names represent? I know what types represent. Also, how would I know the owner name? I believe it is SYS from my research.

                              Same goes with Tablespace location, I am underclear of that part too
                              What tablespace is it located in?

                              SQL> desc ALL_TABLES
                              Name Null? Type
                              ----------------------------------------- -------- ----------------
                              OWNER NOT NULL VARCHAR2(30)
                              TABLE_NAME NOT NULL VARCHAR2(30)
                              TABLESPACE_NAME VARCHAR2(30)
                              CLUSTER_NAME VARCHAR2(30)
                              IOT_NAME VARCHAR2(30)
                              STATUS VARCHAR2(8)
                              PCT_FREE NUMBER
                              PCT_USED NUMBER
                              INI_TRANS NUMBER
                              MAX_TRANS NUMBER
                              INITIAL_EXTENT NUMBER
                              NEXT_EXTENT NUMBER
                              MIN_EXTENTS NUMBER
                              MAX_EXTENTS NUMBER
                              PCT_INCREASE NUMBER
                              FREELISTS NUMBER
                              FREELIST_GROUPS NUMBER
                              LOGGING VARCHAR2(3)
                              BACKED_UP VARCHAR2(1)
                              NUM_ROWS NUMBER
                              BLOCKS NUMBER
                              EMPTY_BLOCKS NUMBER
                              AVG_SPACE NUMBER
                              CHAIN_CNT NUMBER
                              AVG_ROW_LEN NUMBER
                              AVG_SPACE_FREELIST_BLOCKS NUMBER
                              NUM_FREELIST_BLOCKS NUMBER
                              DEGREE VARCHAR2(10)
                              INSTANCES VARCHAR2(10)
                              CACHE VARCHAR2(5)
                              TABLE_LOCK VARCHAR2(8)
                              SAMPLE_SIZE NUMBER
                              LAST_ANALYZED DATE
                              PARTITIONED VARCHAR2(3)
                              IOT_TYPE VARCHAR2(12)
                              TEMPORARY VARCHAR2(1)
                              SECONDARY VARCHAR2(1)
                              NESTED VARCHAR2(3)
                              BUFFER_POOL VARCHAR2(7)
                              ROW_MOVEMENT VARCHAR2(8)
                              GLOBAL_STATS VARCHAR2(3)
                              USER_STATS VARCHAR2(3)
                              DURATION VARCHAR2(15)
                              SKIP_CORRUPT VARCHAR2(8)
                              MONITORING VARCHAR2(3)
                              CLUSTER_OWNER VARCHAR2(30)
                              DEPENDENCIES VARCHAR2(8)
                              COMPRESSION VARCHAR2(8)
                              DROPPED VARCHAR2(3)

                              Edited by: 982064 on Jan 18, 2013 10:56 PM
                              • 12. Re: DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance
                                985067
                                This is very helpful advice. Going to take action on what is said here. Thanks!