• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

sql query, amateur

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.
0
scoobykidd
Asked:
scoobykidd
  • 2
1 Solution
 
jax79sgCommented:
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.

http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html
http://www.adp-gmbh.ch/ora/misc/x.html

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;
0
 
scoobykiddAuthor Commented:
So, to display the size of the db buffer cache I would use
SELECT * FROM V$BH;

Am I going in the right direction?
0
 
jax79sgCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now