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;