[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • 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.
  • 2
1 Solution
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;
scoobykiddAuthor Commented:
So, to display the size of the db buffer cache I would use

Am I going in the right direction?
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.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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