sql query, amateur

Posted on 2007-10-20
Last Modified: 2010-03-20
I need help writing queries for the following items:
the size of the database buffer cache
number of datablocks in the db buffer cache
identify db name
identify name of host computer
syntax to show how many users currently connected to database
instance name
size of database blocks
name and location of datafiles
id name and location of online redo log files
id name and location of control files
id db version number
size of shared pool

I know this is alot, I'm not trying to make a career out of this I just wanna get through this class. I would appreciate any help you can offer. If you know of a web site that would help me that would be great or if you feel like giving me some examples I would be eternally greatful.
Question by:scoobykidd
    LVL 7

    Expert Comment

    Its looks like you are trying to access the information from a Oracle Database.
    Here are 2 links which you will find it useful for this purpose.

    Perform the SQL queries in SQL*Plus (Log in as sys or system in your database) and it should display certain set of information. You may not need all the data in each table/view.

    Example: Shows you basic information from this database.
    select * from v$database;

    Author Comment

    So, to display the size of the db buffer cache I would use

    Am I going in the right direction?
    LVL 7

    Accepted Solution

    If i am not wrong, the buffer cache contains many blocks.
    The statement you listed will list out all the blocks (or rather, the information of each block) in the cache.

    Each block size probably can be retrieved with
    select value from v$parameter where name = 'db_block_size'

    So you multiply the number of blocks with the block size, you can get the cache size.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now