This discussion is archived
12 Replies Latest reply: Jan 18, 2013 11:02 PM by 985067 RSS

DBA - SQL PLUS ASSIGNMENT - Need Veteran Guidance

985067 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    This is very helpful advice. Going to take action on what is said here. Thanks!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points