ORACLE DATABASE ADMINISTRATION

I WILL LIKE TO WELCOME YOU TO THIS WONDERFUL BLOG, IF THIS IS YOUR FIRST TIME OF VISITING THIS SITE. I PROMISE YOU WILL ENJOY THIS SITE IF YOU WANT TO MASTER ORACLE DATABASE ADMINISTRATION. I WILL BE UPDATING THIS BLOG VERY OFTEN, SO I ENTREAT YOU TO BE VISITING THIS BLOG VERY OFTEN FOR MORE WONDERFUL THINGS TO COME. FOR ANY CRITICISM OR PRAISE, GIVE ME A SHOUT AT elvisboateng@yahoo.com

SQL PRACTICE

NOTE: You are to use the hr schema

Let us refresh our minds on what a table is, in terms of columns, rows and field.
before we start with the drill, you should be able to know all the columns a table has by issuing this instruction: desc table_name example desc employees
1. select * from employees;
This displays all the columns and rows in the table employees
2. select last_name, salary from employees;
This will display two columns ie last_name and salary and all their
corresponding rows in the table
3. select last_name, salary+200 from employees;
This will display two columns ie last_name and 200 will be added to all the
values in the salary column giving a new column salary + 200
4. select last_name, 12*(salary+100) from employees;
Kindly describe the result
5. Read on NULL value
select first_name,commission_pct from employees where last_name in ('Hall','Bull');
select first_name,commission_pct*2 from employees where last_name in ('Hall','Bull');
6. select last_name name from employees;
the column name last_name is replaced with name
7. select salary*12 "Annual Salary" from employees;
8. select last_name||' '||job_id AS "Employees" from employees;

select department_name||',it's assigned manager id:'||manager_id as
"department and manager" from departments;

select department_name||q'[,it's assigned manager id: ]' || manager_id as
"department and manager" from departments

9. select department_id from employees;
10. select distinct department_id from employees;
11. describe employees
12. desc employees
13. select employee_id, last_name from employees where department_id=90;
14. select last_name, department_id from employees where last_name='Whalen';
15. select last_name, salary from employees where salary <=3000; 16.select last_name, salary from employees where salary between 2500 and 3500; 17. select last_name, salary from employees where manager_id in (100,101,201); 18. select last_name from employees where first_name like 'S%'; 19. select last_name from employees where last_name like '_o%'; 20. select employee_id, last_name, job_id from employees where job_id like '%SA\_%' ESCAPE '\'; 21. select last_name, manager_id from employees where manager_id is NULL; 22. select employee_id, last_name, from employees where salary>10000 AND job_id like '%MAN%';
23. select employee_id, last_name, from employees where salary>10000 OR job_id like '%MAN%';
24. select employee_id, last_name, from employees where job_id NOT IN ('IT_PROG','SA_REP');
25. select last_name, job_id from employees order by hire_date;
26. select last_name, job_id from employees order by hire_date desc;
27. select last_name, job_id from employees order by department_id, salary DESC;
28. select employee_id, last_name from employees where employee_id=&emp_num;
29. select employee_id,last_name, &colum_name from employees where &condition order by &order_column;
30. select lower('SQL Course') from dual;
31. select upper('SQL Course') from dual;
32. select initcap('SQL Course') from dual;
33. select concat('Hello','World') from dual;
34. select substr('HelloWorld',1,5) from dual;
35. select substr(concat('Hello','World'),1,5) from dual;
36. select length('Mother') from dual;
37. select length(substr(concat('Hello','World'),1,5)) from dual;
38. select instr('monkey','k') from dual;
39. select lpad('elvis',10,'¢') from dual;
40. select rpad('elvis',10,'¢') from dual;
41. select replace ('Pencil','n','a') from dual;
42. select trim('H' from 'HelloWorld') from dual;
43. select trim('d' from 'HelloWorld') from dual;
44. select trim('W' from 'HelloWorld') from dual;
45. select trim('World' from 'HelloWorld') from dual;
46. select upper(replace(substr(trim('a' from rpad(lpad('Kenkey',12,'a'),15,'a')),1,3),'K','P')) from dual;
47. select round(45.926,2) from dual;
48. select trunc(45.926,2) from dual;
49. select mod(5,2) from dual;
50. select sysdate from dual;
51. select to_char(hire_date,'dd-mm-yy') from employees;
52. select to_char(hire_date,'day-mm-yy') from employees;
53. select to_char(hire_date,'dy-mm-yy') from employees;
54. select to_char(hire_date,'dd-mon-yy') from employees;
55. select to_char(hire_date,'dd-month-yy') from employees;
56.select to_char(hire_date,'dd-month-yyyy') from employees;
57.select to_char(hire_date,'dd-month-year') from employees;
58.select to_char(hire_date,'month-yyyy') from employees;
59.select to_char(hire_date,'fmmonth-yyyy') from employees;
60.select to_char(hire_date,'month/yyyy') hiredate from employees;
61.select to_char(hire_date,'fmmonthyyyy') from employees;
62.select to_char(salary,'99,999.99') salary from employees;
63.select last_name,commission_pct commission from employees where last_name like 'A%'
64.select last_name,commission_pct+100 commission from employees where last_name like 'A%'
65.select last_name,nvl(commission_pct,0) commission from employees where last_name like 'A%'
66.select last_name,nvl(commission_pct,0)+100 commission from employees where last_name like 'A%'
67. select last_name,commission_pct,nvl2(commission_pct,'Earned Commission','No Commission') from employees where last_name like 'A%'
68.create table test
(
name varchar2(10),
69.salary number,
allowance number
);
70.select * from test;
71.insert into test values('Kofi',120,100);
72.insert into test values('Paul',90,90);
73.insert into test values('Raymoon',60,60);
74.insert into test values('Elvis',110,110);
75.insert into test values('Baby',80,150);
commit;
76.select * from test;
77.select name,salary,allowance, nullif(salary,allowance) from test;
78select name,salary,allowance, nullif(allowance,salary) from test;
79.select last_name,coalesce(commission_pct,1) from employees where last_name like 'A%'
80.select last_name,salary,job_id,
case job_id when 'IT_PROG' THEN 1.1*salary
when 'ST_CLERK' THEN 1.15*salary
when 'SA_REP' THEN 1.2*salary
else salary
end "Revised Salary"
from employees;
81.select last_name,salary,job_id,
decode(job_id,'IT_PROG',1.1*salary,
'ST_CLERK',1.15*salary,
'SA_REP',1.2*salary,
salary) "Revised Salary"
from employees;

82.select avg(salary),max(salary),min(salary),sum(salary),stddev(salary),variance(salary) from employees;
83.select count(*) from employees;
84.select count(commission_pct) from employees;
85.select count(employee_id) from employees;
86.select count(department_id) from employees;
87.select count(distinct department_id) from employees;

Questions

1.Create a report to display the manager number and the salary of the lowest paid employee for that manager.
Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is 6000 or less. Sort the output in descending order
of salary.


88. select department_id,department_name,location_id,city
from departments
natural join locations;


89. select department_id,department_name,a.location_id,city
from departments a, locations b
where a.location_id=b.location_id;


90. select employee_id,last_name,department_id,manager_id,department_name
from employees
natural join departments;


91. select employee_id,last_name,a.department_id,a.manager_id,department_name
from employees a, departments b
where a.department_id=b.department_id
and a.manager_id=b.manager_id;


92. select employee_id,last_name,department_id,b.manager_id,department_name
from employees a join departments b
using(department_id);


93. select employee_id,last_name,department_id,a.manager_id,department_name
from employees a join departments b
using(department_id);


94. select employee_id,last_name,department_id,a.manager_id,department_name
from employees a join departments b
using(department_id)
where a.department_id=50;


95. select employee_id,last_name,a.department_id,manager_id,department_name
from employees a join departments b
using(manager_id);


96. select employee_id,last_name,department_id,a.manager_id,department_name
from employees a join departments b
on (a.department_id=b.department_id);


97. select employee_id,last_name,a.department_id,a.manager_id,department_name
from employees a join departments b
on (a.department_id=b.department_id);


98. select e.last_name,m.last_name
from employees e join employees m
on (e.manager_id=m.employee_id);


99. select e.last_name,e.department_id,d.department_name
from employees e left outer join departments d
on (e.department_id=d.department_id);


100. select e.last_name,e.department_id,d.department_name
from employees e right outer join departments d
on (e.department_id=d.department_id);


101. select e.last_name,e.department_id,d.department_name
from employees e full outer join departments d
on (e.department_id=d.department_id);


102. select last_name,department_name
from employees
cross join departments;


103. select last_name
from employees
where salary=
(
select min(salary) from employees
);


104. select last_name,department_id,salary
from employees
where salary =
(select min(salary) from employees group by department_id);


105. select last_name,department_id,salary
from employees
where salary in
(select min(salary) from employees group by department_id);


106. select last_name,department_id,salary
from employees
where salary < (select min(salary) from employees group by department_id); select name,salary from test where salary > any(select salary from test where name in('Kofi','Paul'));


107. select name,salary from test
where salary > all(select salary from test where name in('Kofi','Paul'));


108. select last_name,department_id,salary
from employees
where salary < id="1;" id="1;" id="1;">500));

156. insert into box values(1,200);

157. insert into box values(1,500);

158. insert into box values(1,501);

159. create table dept100
as
select employee_id,salary from employees where department_id=100;



160. create table dept101
as
select employee_id,salary*10 from employees where department_id=101;


161. create table dept101
as
select employee_id,salary*10 sal from employees where department_id=101;


162. select * from dept101;

163. create table dept110 (emp, sal)
as
select employee_id,salary*10 from employees where department_id=110;

164. select * from dept110;

165. alter table dept110
add (job_id varchar2(10),hire_date date default sysdate);

166. select * from dept110;

167. desc dept110

168. alter table dept110 modify (emp varchar2(10));

169. alter table dept110 modify (job_id number(10));

170. alter table dept110 modify (job_id varchar2(10));

171. truncate table dept110;

172. alter table dept110 modify (emp varchar2(10));

173. desc dept110

174. select * from dept110;

175. alter table dept110 drop column job_id;

176. select * from dept110;

177. alter table dept110 set unused column sal;

178. select * from dept110;

179. alter table dept110 drop unused columns;

180. select * from user_unused_col_tabs;

181. log in as sys and create table emp

182. create table hr.emp
as select * from scott.emp;

183. create table hr.dept
as select * from scott.dept;

184. Now log in as hr

185. CREATE VIEW myview
AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY, job JOB
FROM emp
WHERE deptno=20;

186. CREATE force VIEW myview
AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY, job JOB
FROM emps
WHERE deptno=20;

187. select * from myview;

188. CREATE or replace VIEW myview
AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY, job JOB
FROM emp
WHERE deptno=20;

/* the same as

189. CREATE VIEW myview(EMLOYEE_NO,NAME,SALARY,JOB)
AS SELECT empno,ename,sal,job
FROM emp
WHERE deptno=20;

*/

190. select * from myview;

191. CREATE VIEW v2
AS SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;


192. CREATE VIEW v2
AS SELECT d.dname, MIN(e.sal) mini, MAX(e.sal) maxi , AVG(e.sal) aver
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;

193. select * from v2

COURSE OUTLINE

Oracle Database 10g: Database Administration Workshop

What you will learn:

This course is your first step towards success as an Oracle professional, designed to give you a firm foundation in basic database administration. In this class, you'll learn how to install and maintain an Oracle database. You will gain a conceptual understanding of the Oracle database architecture and how its components work and interact with one another. You will also learn how to create an operational database and properly manage the various structures in an effective and efficient manner including performance monitoring, database security, user management, and backup/recovery techniques. The lesson topics are reinforced with structured hands-on practices. This course is designed to prepare you for the corresponding Oracle Certified Associate exam.

Course Topics:

Writing SQL SELECT statements

• Define projection, selection, and join terminology
• Review the basic SQL SELECT statement syntax
• Select all columns using a wildcard notation from a table
• State simple rules and guidelines for writing SQL statements
• Write a query containing the arithmetic operators
• Use aliases to customize column headings
• Create a character expression with the concatenation operator
• Use the quote operator q to control text strings containing the quote character

Restricting Data

• Limit Rows Using a Selection
• Use the WHERE Clause
• List the main Comparison Conditions
• Use the LIKE Condition to Compare Literal Values
• List the Logical Conditions AND, OR, NOT
• Use Multiple Conditions in the WHERE clause
• Describe the Rules of Precedence

Sorting Data

• Sort Rows with the ORDER BY Clause
• Use the && Substitution Variable
• Use the iSQL*Plus DEFINE Command
• Use the VERIFY Command

SQL Functions

• Differentiate between Single Row and Multiple Row SQL Functions
• Categorize the Character Functions into Case Manipulation and Character Manipulation types
• Explain the Numeric Functions ROUND, TRUNC, and MOD
• List the Rules for Applying the Arithmetic Operators on Dates
• Use the Arithmetic Operators with Dates in the SELECT Clause
• Explain the DATE functions MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC
• Explain Implicit and Explicit conversion
• Nest Functions to Perform Multiple Tasks in One Statement

Conditional Expressions

• Use the CASE Expression
• Explain the DECODE Expression

Aggregating Data Using Group Functions

• Categorize the Types of Group Functions
• Use the AVG, SUM, MAX, MIN, and COUNT Functions in a Query
• Utilize the DISTINCT Keyword with the Group Functions
• Describe how Nulls are handled with the Group Functions
• Create Groups of Data with the GROUP BY Clause
• Group Data by more than one column
• Avoid Illegal Queries with the Group Functions
• Exclude Groups of Data with the HAVING Clause

Displaying Data from Multiple Tables

• Identify Types of Joins
• Retrieve Records with Natural Joins
• Use Table Aliases to write shorter code and explicitly identify columns from multiple tables
• Create a Join with the USING clause to identify specific columns between tables
• Use the ON clause to specify arbitrary conditions or specify columns to Join
• Create a Three-way join with the ON clause to retrieve information from 3 tables
• List the Types of Outer Joins LEFT, RIGHT, and FULL
• Generating a Cartesian Product

Using Subqueries

• Use a sub query to solve a problem
• Identify where sub queries can be placed in a SELECT statement
• Describe the types of sub queries (single row, multiple row)
• Show the single row sub query operators
• Use the group functions in a sub query
• Identify illegal statements with sub queries
• Show the multiple row sub query operators
• Explain how null values are handled in sub queries

Using the Set Operators

• Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
• Use the UNION ALL operator to return all rows from multiple tables (with duplicates)
• Describe the INTERSECT operator
• Use the INTERSECT operator
• Explain the MINUS operator
• Use the MINUS operator
• List the SET operator guidelines
• Order results when using the UNION operator

Inserting and Updating Data

• Write INSERT statements to add rows to a table
• Insert Special Values
• Copy Rows from Another Table
• Update Rows in a Table

Deleting Data

• Use DELETE statements to remove rows from a table
• Delete Rows Based on Another Table
• Describe the TRUNCATE Statement

Database Transactions

• Save and Discard Changes to a Table through Transaction Processing (COMMIT, ROLLBACK, and SAVEPOINT)
• Show how Read Consistency works

Using DDL Statements

• List the main database objects
• Identify the Naming Rules
• Display the basic Syntax for Creating a Table
• Show the DEFAULT option
• List the Data Types that are available for Columns

Managing Tables

• Explain the different types of constraints
• Show resulting exceptions when constraints are violated with DML statements
• Create a table with a sub query
• Describe the ALTER TABLE functionality
• Remove a table with the DROP statement

Creating Other Schema Objects

• Categorize simple and complex views and compare them
• Create a view
• Retrieve data from a view
• Explain a read-only view
• List the rules for performing DML on complex views
• Create a sequence
• List the basic rules for when to create and not create an index
• Create a synonym

Managing Objects with Data Dictionary Views

• Describe the structure of each of the dictionary views
• List the purpose of each of the dictionary views
• Write queries that retrieve information from the dictionary views on the schema objects
• Use the COMMENT command to document objects

Introduction (Database Architecture)
Describe course objectives
Explore the Oracle 10g database architecture

Installing the Oracle Database Software on UNIX
Explain core DBA tasks and tools
Plan an Oracle installation
Use optimal flexible architecture
Install software with the Oracle Universal Installer (OUI)

Creating an Oracle Database
Create a database with the Database Configuration Assistant (DBCA)
Create a database design template with the DBCA
Generate database creation scripts with the DBCA

Managing the Oracle Instance
Start and stop the Oracle database and components
Use Enterprise Manager (EM)
Access a database with SQL*Plus and iSQL*Plus
Modify database initialization parameters
Understand the stages of database startup
View the Alert log
Use the Data Dictionary

Managing Database Storage Structures
Describe table data storage (in blocks)
Define the purpose of tablespaces and data files
Understand and utilize Oracle Managed Files (OMF)
Create and manage tablespaces
Obtain tablespace information

Administering User Security
Create and manage database user accounts
Authenticate users
Assign default storage areas (tablespaces)
Grant and revoke privileges
Create and manage roles
Create and manage profiles

Configuring the Oracle Network Environment
Use Enterprise Manager for configuring the Oracle network environment
Create additional listeners
Create Net Service aliases
Configure connect-time failover
Control the Oracle Net Listener
Test Oracle Net connectivity
Identify when to use shared versus dedicated servers

Automatic Performance Management
The Use of 10g Database Advisors
Identifying Tunable Components
Advisory Framework
Using the SQL Tuning and SQL Access Advisor
Manage the Automatic Workload Repository (AWR)
Use the Automatic Database Diagnostic Monitor (ADDM)
Describe advisory framework

Using AWR
Create and manage AWR snapshots
Generate AWR reports
Create snapshot sets and compare periods
Generate ADDM reports
Generate ASH reports

Backup and Recovery Concepts
Identify the types of failure that may occur in an Oracle Database
Describe ways to tune instance recovery
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARCHIVELOG mode

Performing Database Backups
Create consistent database backups
Back your database up without shutting it down
Create incremental backups
Automate database backups
Monitor the flash recovery area

Performing Database Recovery
Recover from loss of a control file
Recover from loss of a redo log file
Perform complete recovery following the loss of a data file

Configuring Recovery Manager
Recovery Manager Features and Components
Using a Flash Recovery Area with RMAN
Configuring RMAN
Control File Autobackups
Retention Policies and Channel Allocation
Using Recovery Manager to connect to a target database in default NOCATALOG mode
Displaying the current RMAN configuration settings
Altering the backup retention policy for a database

Using Recovery Manager
RMAN Command Overview
Parallelization of Backup Sets
Compressed Backups
Image Copy
Whole Database and Incremental Backups
LIST and REPORT commands
Enable ARCHIVELOG mode for the database
Use Recovery Manager

Recovering from Non-critical Losses
Recovery of Non-Critical Files
Creating New Temporary Tablespace
Recreating Redo Log Files, Index Tablespaces, and Indexes
Read-Only Tablespace Recovery
Authentication Methods for Database Administrators
Loss of Password Authentication File
Creating a new temporary tablespace
Altering the default temporary tablespace for a database

Incomplete Recovery
Recovery Steps
Server and User Managed Recovery commands
Recovering a Control File Autobackup
Creating a New Control File
Incomplete Recovery Overview
Incomplete Recovery Best Practices
Simplified Recovery Through RESETLOGS
Point-in-time recovery using RMAN

Flashback
Flashback Database Architecture
Configuring and Monitoring Flashback Database
Backing Up the Flash Recovery Area
Flashback Database Considerations
Using Flashback Database EM Interface
Managing and monitoring Flashback Database operations

PROJECT

PROJECT
1. Create a folder ( C:\database ), in the database folder create folders data, log, flash, backup
2(a). Create a transaction processing database named PROJECT, run a script mcctb.sql while creating the database. Try to add your own scripts
2(b). During the creation of the database, kindly answer the following questions:
i. Difference among custom, data warehouse, general purpose and transactional databases
j. The purposes of the following users: sys, sysman, system, dbsnmp
k. The differences among the various storage mechanism: file system, automatic storage management (ASM) and raw devices
l. What is Oracle-Managed Files

3. (Write a script to) answer the following questions:
a. What is the oracle base, oracle home
b. What is the instance name, database name and global database name
c. What is the flash recovery area
d. What is the name and location of the alert log
e. What are the names and locations of all datafiles, temporary files, online redo log files and control files.
f. What is the name and location of parameter file
4. Look into the mcctb.sql script and log in as the user specified in the script
5. Always look into the alert log at each stage
6. Make sure you have both pfile and spfile. If only one exist, create the other one. Note: Make sure you can create pfile from spfile and vice versa.
7. Shutdown immediate
8. Remove all the parameter files from their location
9. Startup nomount and note the error
10. Bring back the spfile and startup nomount
11. What is Total System Global Area ?
12. Briefly describe the components of the SGA based on what is on your screen.
13. Determine the status of the database.
14. What goes into startup nomount ….. ?
15. To check the content of the parameter file, issue show parameter
16. Example to check the value of db_name, type show parameter db_name. This means that, to check the value of a particular parameter issue show parameter parameter_name
17. The locations of the control files can be located in the parameter file, so find these locations.
18. Alternatively, issue the following statements: select name from v$controlfile; and select name from v$datafile;. What did you observed?
19. Remove the locations control files from their locations
20. Let us move into the mount state, alter database mount and note down the error.
21. Bring back all the control files to their respective locations and mount the database
22. Determine the status of the database.
23. After mounting the database, issue the statements: select name from v$controlfile; and select name from v$datafile;.
24. What going into mounting a database
25. Open the database, alter database open;.
26. Determine the status of the database.
27. The name format of the password file is PWDsid.ora, it is in the same locations as with the parameter file. Find the one for this database
28. All those with sysdba and sysoper roles can be found in the password file.
29. Let us check those with sysdba and sysoper roles: select * from v$pwfile_users;
30. grant sysdba to mcctb
31. issue the query : select * from v$pwfile_users;
32. grant sysoper to mcctb
33. issue the query : select * from v$pwfile_users;
34. Shutdown immediate
35. Move the password file from its location
36. startup the database
37. issue the query : select * from v$pwfile_users;
38. How can you recreate the password file
39. What is your observation about password file
40. Summarize differences among shutdown normal, transactional, immediate and abort with examples
41. When do we use startup force
42. Create an sqlplus shortcut on the desktop to log into user account mcctb
43. Open a fresh command prompt, set oracle_sid=project
44. Issue: emctl status dbconsole, to check the status of the enterprise manager (EM)
45. If the controller is down, issue : emctl start dbconsole
46. Copy the URL onto internet explorer address bar
47. Log in into the Enterprise Manager
48. List all data dictionary and dynamic performance views which has something to do with control files, datafiles, tablespace, online and archive redo log files
49. Check the default permanent and temporary tablespaces and also the default undo tablespace
50. Use the enterprise manager to create one permanent , temporary and undo tablespaces
51. Make the above the default permanent, temporary and undo tablespaces
52. Change the locations of all the datafiles to c:\database\data, the alert log to c:\database\log and flash recovery area to c:\database\flash
53. Using the Enterprise Manager, go onto the Maintenance tab, click on the Schedule Backup, fill in the Host Credentials using the user and password of the Operating System, click on Schedule Oracle-Suggested Backup, check Disk, click next, next. Read with patience to the final stage and submit



content of script mcctb.sql in notepad

create user mcctb identified by mcctb;
grant dba to mcctb;

USERS

LOG IN AS SYS AND RUN THE FF

set pagesize 100
col profile format a10
col temporary_tablespace format a10
col default_tablespace format a10
set linesize 200
col account_status format a10
col username format a10
col grantee format a10
col owner format a10
col table_name format a10
col grantor format a10
col privilege format a15
col column_name format a11
col role format a10

AFTERWARDS THAT GO THROUGH THE FOLLOWING STEPS:




1. Connect as sys
2. Create a permanent tablespace (obo) of size 5m
3. Create a temporary tablespaces (marian) of size 5m
4. Create a user
create user cipd identified by cipd
default tablespace obo
temporary tablespace marian
profile default
password expire
account lock
quota 1m on obo;

5. Run the query below to check the properties of the users
Select username,account_status,lock_date,expiry_date,default_tablespace,
temporary_tablespace,profile from dba_users;
6. Conn as the user created in (4) above
7. What is the error encountered. Take closely look at step 4
8. Connect as sys
9. Run the query below and repeat step 6. What were your observations
alter user cipd account unlock;
10. Log in as sys and run the query below, repeat step 6 and show user
grant create session to cipd;
11. Repeat step 5. What were your observations
12. Run queries below:

select * from user_sys_privs;
select * from user_role_privs;
select * from user_tab_privs_recd;
select * from user_tab_privs_made;
select * from user_tab_privs;
select * from user_col_privs_recd;
select * from user_col_privs_made;
select * from user_col_privs;
select * from role_role_privs;
select * from role_sys_privs;
select * from role_tab_privs;
select * from session_privs;
select * from session_roles;



What is your observation
NB: You will be running the queries above several times, kindly observe the rows that will be return as the queries are being run.
13. Create a table by using the query below.
create table ttb ( id number );
Note the error
14. run insert into ttb values (1); and commit
15. Repeat step 12
16. Log in as hr and select * from cipd.ttb, note down the error
17. Log in as cipd and run the query below
grant select on ttb to hr;
repeat step 16
18. Repeat step 12
19. Create another table ssb
Create table ssb (id number, name varchar2(10));
20. Log in as hr and select * from cipd.ssb
21. Log in as cipd and issue: grant insert (id) on ssb to hr;
22. Log in as hr and issue: insert into cipd.ssb(id) values(1); and again
select * from cipd.ssb
23. Log in as cipd and select from ssb
24. Log in again as hr and issue: insert into cipd.ssb values(2,'KKK').
Note down the error. Issue this other statement: insert into cipd.ssb(id) values(2);
25. Connect as cipd and repeat step 12
26. Connected as cipd create a user kwame.
27. If the above fail, conn as sys and issue: grant create user to cipd and repeat 26. Please make sure you can log in as Kwame.
28. Log in as cipd and issue: grant create table to Kwame, what happened..?
29. Connect as sys and issue: grant create table to cipd with admin option
30. Repeat step 28
31. Connect as hr and issue: grant select on employees to cipd;
32. Connect as cipd and repeat step 12
33. Connect as scott and issue : grant insert (empno) on emp to cipd;
34. Connect as cipd and repeat step 12
35. Connected as cipd issue: grant insert (empno) on scott.emp to kwame;
36. Note the error
37. Connect as scott and issue : grant insert (empno) on emp to cipd with grant option;
38. Repeat step 35
39. Connected as cipd run step 12
40. Connected as cipd issue: create role mama identified by mama;
41. Connect as sys and grant create role to cipd. And repeat step 40
42. Connect as cipd and issue: grant create table to mama;
43. Run step 12
44. Connected as cipd issue: grand select on ttb to mama; and repeat step 12
45. As cipd issue: create role papa identified by papa and repeat step 12
46. AS cipd issue: grant mama to papa and repeat step 12
47. As sys issue: grant connect to cipd and repeat step 12

This practice will help you understand or appreciate tablespace better. Use the command prompt to undertake this project
.

Let me know your worries for additions and omissions.

1. Log in as sys and start your database with an spfile
2. Check whether the spfile was used to start the database ---refer to query 28. If the value of the spfile is NULL. Then try to figure out how to perform step 1 well.
3. Create a user named “AMA” – refer to query 1
4. Grant a dba role to user ama ---refer to query 2
5. Log in as ama
6. Create a small file permanent tablespace --refer to query 3
7. Create a big file permanent tablespace --- refer to query 4
8. Create a temporary tablespace --- refer to query 5 ----try to bypass this level
9. Create an undo tablespace named CAIN – refer to query 24
10. Check the default undo tablespace --- refer to query 25
11. Run query 26
12. Repeat step 10
13. Run any DML or DCL statement ---refer to query 33
14. Is there any error?
15. Log in as sys and repeat step 11, query 34 and commit. Is there any error?
16. Run query 27
17. Log in as ama and run queries 33, 34 and commit
18. What is your observation and conclusion from steps 9 to 17
19. Check the properties of the tablespaces created above ---refer to query 6 to 8
20. Take note of the number of blocks allocated to the datafiles and tempfiles
21. Check the default permanent and temporary tablespaces and also some other properties of the database---- refer to query 9
22. Change the default permanent and temporary tablespaces to mama and gimpa respectively ---refer to query 10
23. Repeat step 21, to confirm the change made in step 22
24. create a table (segment) by name transact --- refer to query 11
25. Check the properties of the segment (transact) , write the detail somewhere---refer to query 12
26. Calculate the space in KB occupied by the segment (transact) based on the query above. write the detail somewhere
27. Check the detail number of extents allocated to the segment (transact) --- refer to query 13. write the detail somewhere
28. Create another index segment transact_idx --- refer to query 14
29. Repeat steps 25 to 27 to check the properties of the index segments, transact_idx
30. Create sequences seqq01,seqq02 and seqq03 ---refer to query 15
31. Check all the objects owned by AMA ---refer to query 16
32. Also check the spaced used on the tablespace MAMA --- refer to query 17
33. Insert rows into the table transact --- refer to query 18
34. If there is an error ---refer to queries 35 and 36---state your observation and make sure step 33 goes through (you can look at query 21).
35. Repeat steps 25 to 27 and step 32 to check the properties of the segments(transact and transact_idx)
36. Insert another rows into transact --- refer to query 19 and if an error occur refer to query 21 for solution.
37. Repeat step 35 and how many extents are free in tablespace mama
38. Insert another rows into transact ---refer to query 20
39. Repeat step 35
40. An error will be return, refer to query 21 to resolve the error
41. Repeat step 35 and run query 22 afterwards
42. How many segments does AMA have
43. How many extents are contain in each segments mention in step 42 above
44. What is the size in MB of each segment mention in step 42 above
45. What is your observation about segments spanning datafiles
46. Describe the table transact. This table contains information about the transactions which took place for a financial institution with nine branches. The column transact_id contains transaction id of each transaction. The first number of the transact_id indicates the branch the transaction took place, eg transact_id 2004 means the transaction took place in branch 002. The column ac_no indicates the account numbers of the customers. The initial number indicates the branch a customer belongs. Eg ac_no 1000, means that customer belongs to branch 001. The next column indicates whether the transaction is a credit (C) or debit (D). The last column, amount shows the amount involve. Now, relax for Questions relating to SQL statements ie Write a query to solve the following questions.

NB: The branch number should have two zeros preceding the number ie 001, 002

47. What is the total number of colums in the table transact
48. What is the total credit
49. What is the total debit
50. Write a single query to determine the total credit and total debit
51. What is the balance for the day ie credit – debit
52. Write a single query to determine total credit and total debit for the nine branches.
53. Write a single query to determine the balances for the nine branches
54. How many distinct customers performed transaction for that day
55. Create table boddy ---refer to query 31
56. Drop tablespace mama --- refer to query 29
57. How will be bypass the error you had from step 56. Make sure you perform step 53 successfully.
58. Drop tablespace papa ---refer to query 30
59. How will you bypass the error you had from step 58. Make sure you perform step 58 successfully.
60. Drop tablespace cain ---refer to query 32
61. How will you bypass the error you had from step 60. Make sure you perform step 60 successfully.
62. How will you move a datafile from one location onto a different location.

---------------------------------------

QUERIES:

QUERIES
=======

1. create user ama identified by ama;
2. grant dba to ama;
3. create tablespace mama datafile 'c:\mama01.bdf' size 1m;
4. create bigfile tablespace papa datafile 'c:\papa.bdf' size 1m;
5. create temporary tablespace gimpa tempfile 'c:\gimpa01.dbf' size 1m;
6. select tablespace_name,block_size,contents,bigfile from dba_tablespaces;
7. col file_name format a15
select file_name,tablespace_name,bytes,blocks from dba_data_files where tablespace_name IN ('MAMA','PAPA','CAIN');
8. select file_name,tablespace_name,bytes,blocks from dba_temp_files;
9. col property_name format a18
col property_value format a20
col description format a40
select * from database_properties;
10.alter database default tablespace mama;
alter database default temporary tablespace gimpa;
11. create table transact
(
transact_id varchar2(5),
ac_no varchar2(10),
cr_debit varchar2(1),
amount number(5)
);
12.col segment_name format a12
col tablespace_name format a15
select segment_name,segment_type,tablespace_name,bytes,blocks,extents from dba_segments where owner='AMA';
13.select segment_name,segment_type,tablespace_name,bytes,blocks from dba_extents where owner='AMA' ;
14.create unique index transact_idx on transact(transact_id);
15.create sequence seqq01 minvalue 100 increment by 1;
create sequence seqq02 minvalue 1000 increment by 5;
create sequence seqq03 minvalue 1000 increment by 10;
16.col object_name format a20
select object_name,object_type from dba_objects where owner='AMA';
17.select * from dba_tablespace_usage_metrics;
18. begin
for i in 1..7000
loop
insert into transact values(seqq01.nextval,seqq02.nextval,'C',i+24);
end loop;
commit;
end;
/
19.begin
for i in 1..7000
loop
insert into transact values(seqq01.nextval,seqq02.nextval,'D',i+10);
end loop;
commit;
end;
/
20.begin
for i in 1..7000
loop
insert into transact values(seqq01.nextval,seqq03.nextval,'C',i+60);
end loop;
commit;
end;
/

21.alter tablespace mama add datafile 'c:\mama02.dbf' size 1m;

22.begin
for i in 1..6000
loop
insert into transact values(seqq01.nextval,seqq03.nextval,'D',i+50);
end loop;
commit;
end;
/

23. select a.segment_name,a.segment_type,a.tablespace_name,b.file_name,a.bytes,a.blocks,a.extent_id
from dba_extents a, dba_data_files b
where a.relative_fno=b.file_id
and owner='AMA';

24.create undo tablespace cain datafile 'c:\cain01.dbf' size 1m;
25.show parameter undo_tablespace;
26.alter system set undo_tablespace='';
27.alter system set undo_tablespace='CAIN';
28.show parameter spfile;
29.DROP TABLESPACE mama INCLUDING CONTENTS;
30.DROP TABLESPACE papa;
31.create table boddy
(
id varchar2(5),
amount number(5)
) tablespace papa;
32.DROP TABLESPACE cain including contents;
33.create table ttb
(
id number(5)
) ;
34.insert into ttb values(5);
35.alter tablespace cain add datafile 'c:\cain02.dbf' size 1m;
36.alter database datafile 'c:\cain02.dbf' resize 5m;