Write an SQL query to extract the following information from data dictionary: Fo

Question

Write an SQL query to extract the following information from data dictionary:

For each question you need to submit query and screenshot with results.

Start Oracle SQL Developer

Use SELECT * FROM dictionary; to find all available dictionary views.

Find all dictionary views that contain information about indexes.

How many indexes are defined on OE.CUSTOMERS table? (Hint: all views have meaningful name, just search the page)

For each index you found in #3 how many distinct keys?

Use V$...stat views to find information about "free memory" and "DB time"

Hint: DB time is system parameter
free memory is SGA (System Global Area) parameter

From your standpoint what is the most useful Oracle data dictionary view/table/procedure. Describe it and provide examples.

Include your code and results of the execution

Explanation / Answer

create database if not exists `dictionary`;

USE `dictionary`;

CREATE TABLE `customer table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`file_name` varchar(220) NOT NULL,
`data` longblob NOT NULL,
`auditor_data` longblob,
`userid` varchar(220) NOT NULL,
`status` varchar(220) NOT NULL,
`date1` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

CREATE TABLE `index` (
`aid` int(11) NOT NULL AUTO_INCREMENT,
`fid` varchar(255) NOT NULL,
`uid` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`fname` varchar(255) NOT NULL,
`fsize` varchar(255) NOT NULL,
`mblock` varchar(255) NOT NULL,
`mby` varchar(255) NOT NULL,
`date` varchar(255) NOT NULL,
`status` varchar(255) NOT NULL,
PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `user` (
`uid` varchar(225) NOT NULL,
`name` varchar(255) NOT NULL,
`userid` varchar(255) NOT NULL,
`pass` varchar(255) NOT NULL,
`mobile` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`date` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT DISTINCT name FROM index;

CREATE TABLE#OS_Available_Memory(
ID[int],NAME[sysname],
Physical_Memory_In_MB[int],
Physical_Memory_In_Bytes[nvarchar](512)
)
INSERT#OS_Available_Memory
EXEC [master]..[xp_msver]

SELECT[Name],
[Physical_Memory_In_MB]
,[Physical_Memory_In_Bytes]
FROM#OS_Availeble_Memory
WHERE NAME = 'PhysicalMemory'
GO
DROP TABLE#OS_Available_Memory

SELECT*
FROM[master]..[sysconfigures]
WHERE[comment]IN ('Minimum size fo server memory (MB)', 'Maximum size of server memoty (MB)')

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.