db2_practice2.txt

Other objects in the database (synonym, sequence, database link)
-----------------------------
(DBA_SYNONYMS, DBA_VIEWS, DBA_SEQUENCES, DBA_DB_LINKS)

------------------------------------------------------------------------------
Give the following query (in ARAMIS database):
  SELECT * FROM sz1;
Is there a table named 'sz1' ? (Answer -> no)
Then which is the table (owner, table_name) whose records are displayed?
You should find a table, a view is not enough.
------------------------------------------------------------------------------
Create your own copy of EMP and DEPT tables (if you don't have already).
CREATE TABLE emp AS SELECT * FROM nikovits.emp;
CREATE TABLE dept AS SELECT * FROM nikovits.dept;

Create a sequence to generate numbers for department number columns of the tables.
(start with 50, increment by 10)
INSERT 2 new departments and 3 employees for each department. The department numbers
should be generated by the sequence.
-------------------------------------------------------------------------------
We have two Oracle databases ARAMIS and ULLMAN. 
Create a database link in database ULLMAN pointing to database ARAMIS with the 
following statement:

CREATE DATABASE LINK aramis CONNECT TO username IDENTIFIED BY password       -- your username/password
USING 'aramis.inf.elte.hu:1521/aramis.inf.elte.hu';

Query a table in database ARAMIS while connecting to ULLMAN:

SELECT * FROM nikovits.emp@aramis;

See the following two tables about countries and rivers:

NIKOVITS.countries_v   in Aramis database  
  most important columns: tld (top level domain -> key) and cname (country name)
NIKOVITS.rivers_v      in Ullman database 
  most important columns: rname and countries (contains comma separated tld-s)

create view countries_v (tld, cname, region, area, population, gdp, capital, languages)
as select * from nikovits.orszagok;
grant select on countries_v to public;

create view rivers_v(region, rname, length, water, area, source_country, end_country, sea, countries)
as select * from nikovits.folyok;
grant select on rivers_v to public;


 Give the names of countries through which the Mekong flows.
*Give the names of countries in the order of flowing (down the river). -> see function INSTR
---------------------------------------------------------------------------------


Oracle storage concepts
-----------------------
(DBA_TABLES, DBA_DATA_FILES, DBA_TEMP_FILES, DBA_TABLESPACES, DBA_SEGMENTS, DBA_EXTENTS, DBA_FREE_SPACE)
1.
Give the names and sizes of database files. (file_name, size_in_bytes)
2.
Give the names of tablespaces. (tablespace_name)
3.
Which datafile belongs to which tablespace? (filename, tablespace_name)
4.
Is there a tablespace that doesn't have datafiles? -> see temp_files
5.
What is the block size in USERS tablespace? (block_size)
6.
Find some segments whose owner is NIKOVITS. What segment types do they have? List the types. (segment_type)
7.
How many extents there are in file 'users02.dbf' ? (num_extents)
How many bytes do they occupy? (sum_bytes)
8.
How many free extents there are in file 'users02.dbf', and what is the summarized size of them ? (num, sum_bytes)
How many percentage of file 'users02.dbf' is full (allocated to some object)?
9.
Who is the owner whose objects occupy the most space in the database? (owner, sum_bytes)
10.
Is there a table of owner NIKOVITS that has extents in more than one datafile? (table_name)
Select one from the above tables (e.g. tabla_123) and give the occupied
space by files. (filename, bytes)
11.
On which tablespace is the table ORAUSER.dolgozo?
On which tablespace is the table NIKOVITS.eladasok? Why NULL? 
 (-> partitioned table, stored on more than 1 tablespace)


Write a PL/SQL procedure, which prints out for the parameter user his/her oldest table (which was created earliest)
the size of the table in bytes (the size of the table's segment) and the creation date. (table_name, bytes, created)
CREATE OR REPLACE PROCEDURE oldest_table(p_user VARCHAR2) IS 
...
SET SERVEROUTPUT ON
execute oldest_table('NIKOVITS');