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;