This
practice covers creating various types of tablespaces
1.
Create a permanent tablespace named DATA01 that is data dictionary
managed and make the datafile 2M in size.
|
SQL>
CREATE TABLESPACE data01
2 DATAFILE
'$HOME/ORADATA/u04/data01.dbf' SIZE 2M
3 EXTENT
MANAGEMENT DICTIONARY; Tablespace
created. |
2.
Create a permanent tablespace named DATA02 that is locally managed with
uniform sized extents of 128 KB each. Create
the datafile with 2M of storage.
|
SQL>
CREATE TABLESPACE data02
2 DATAFILE
'$HOME/ORADATA/u03/data02.dbf' SIZE 2M
3 EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 128K; Tablespace
created. |
3.
Create a permanent tablespace named INDEX01 that is locally managed
with uniform sized extents of 64K each. Create
the tablespace with an initial file size of 2m and enable automatic extension
of 512 KB when more extents are required.
Define the datafile with a maximum size of 20 M.
|
SQL>
CREATE TABLESPACE index01
2 DATAFILE
'$HOME/ORADATA/u02/index01.dbf' SIZE 2M
3 AUTOEXTEND
ON NEXT 512K MAXSIZE 20M
4 EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 64K; |
4.
Create a permanent tablespace named READONLY for read-only tables.
Create the datafile with 2M of disk space and use the Oracle default
storage parameters. DO NOT make the tablespace read only at this time.
|
SQL>
CREATE TABLESPACE readonly
2 DATAFILE
'$HOME/ORADATA/u01/ronly01.dbf' SIZE
2M; Tablespace
created. |
5.
Create an undo tablespaces named undo5 for automatic undo management.
Create the datafile with 40M of disk space and use the Oracle default
storage parameters.
|
SQL>
CREATE UNDO TABLESPACE undo5
2 DATAFILE '/u01/oradata/undo501.dbf'
SIZE 40M; Tablespace
created. |
This
practice covers creating temporary tablespaces and the allocation of
temporary segments.
Instructions
1.
Create a temporary tablespace named TEMP123 that is locally managed
and make the datafile 20M in size. Create
the tablespace using the TEMPORARY clause, specifying extent management as
local and specifying a uniform size of 4M for each extent.
|
SQL>
CREATE TEMPORARY TABLESPACE temp123 TEMPFILE
2
'$HOME/ORADATA/u01/temp01.dbf' SIZE 20M
3
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M; Tablespace
created. |
2.
Determine
if there is a default temporary tablespace for the database by querying the
DATABASE_PROPERTIES table.
|
SQL>
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM 2
DATABASE_PROPERTIES
3
where
property_name like '%TEMP%'; PROPERTY_NAME
PROPERTY_VALUE -----------------------
-------------- DEFAULT_TEMP_TABLESPACE
TEMP |
3.
Alter the database to make the temporary tablespace TEMP123 the
default temporary tablespace for the database,
|
SQL>
ALTER DATABASE 2
DEFAULT
TEMPORARY TABLESPACE temp123; PROPERTY_NAME
PROPERTY_VALUE -------------------------
-------------------- DEFAULT_TEMP_TABLESPACE
TEMP123 |
4.
Determine
if the TEMP123 temporary tablespace is now the default temporary tablespace
for the database by querying the DATABASE_PROPERTIES
table.
|
SQL>
SELECT PROPERTY_NAME,
PROPERTY_VALUE FROM
2
DATABASE_PROPERTIES
3
where property_name like '%TEMP%';
PROPERTY_NAME
PROPERTY_VALUE -------------------------
-------------------- DEFAULT_TEMP_TABLESPACE
TEMP123 |
5.
Create a new user without specifying a temporary tablespace and then
verify that the TEMP123 temporary tablespace was assigned as the default
temp tablespace. Verify the
temporary tablespace for the user by querying the DBA_USERS table.
|
SQL>
CREATE USER alex IDENTIFIED BY alex; SQL>
select username, temporary_tablespace 2
from dba_users 3
where username in ('SYS','SYSTEM','ALEX'); USERNAME
TEMPORARY_TABLESPACE ---------------------------
---------------------------- ALEX
TEMP123 SYSTEM
TEMP123 SYS
TEMP123 |
6.
Attempt to drop the temporary tablespace TEMP123.
|
SQL>
drop tablespace TEMP123; drop
tablespace TEMP123 * ERROR
at line 1: ORA-12906:
cannot drop default temporary tablespace |
This
practice covers changing the status of tablespaces
Instructions
1.
Create a permanent tablespaces named READONLY for read-only tables.
Create the datafile with 2M of disk space and use the Oracle
default storage parameters. DO NOT make the tablespace read only at this
time.
|
SQL>
CREATE TABLESPACE ro 2
DATAFILE
'$HOME/ORADATA/u01/ronly01.dbf' SIZE 2M; Tablespace
created. |
2.
Create a small table in the READONLY tablespace.
|
SQL>
CREATE TABLE TESTRO
TABLESPACE ro
2
AS SELECT * FROM dual;
Table
created.
|
3.
Alter the RO tablespace to change its status to read only.
|
SQL>
ALTER TABLESPACE ro READ
ONLY; Tablespace
altered. |
4.
Validate that you can query data from the TESTRO table in the
readonly tablespace and then see if you can insert data into the TESTRO
table.
|
SQL>
select * from testro; D - X SQL>
INSERT INTO testro SELECT
* FROM testro; INSERT
INTO testro SELECT * FROM testro
* ERROR
at line 1: ORA-00372:
file 12 cannot be modified at this time ORA-01110:
data file 12: '/home1/user13/ORADATA/u01/ronly01.dbf' |
This
practice covers changing the storage settings of tablespaces.
Instructions
1.
Allocate 500K more disk space to tablespace DATA02. Verify the
result.
|
SQL>
select file_name, bytes
from dba_data_files where
2
tablespace_name = 'DATA02'; FILE_NAME
BYTES ----------------------------------------
---------- /home1/user13/ORADATA/u03/data02.dbf
2048000 SQL>
ALTER DATABASE
2
DATAFILE '$HOME/ORADATA/u03/data02.dbf' RESIZE 2500K;
Database
altered. SQL>
select file_name, bytes
from dba_data_files where
2
tablespace_name = 'DATA02';
FILE_NAME
BYTES ----------------------------------------
---------- /home1/user13/ORADATA/u03/data02.dbf
2560000 |
2.
Relocate tablespace INDEX01 to subdirectory u06.
Verify relocation and status of
INDEX01 after the move.
Hints
·
Take the
INDEX01 tablespace offline.
·
Use
V$DATAFILE to verify status.
·
Use
operating system move command to move the tablespace to u06.
·
Use
ALTER TABLESPACE to relocate the tablespace.
·
Place
the INDEX01 tablespace online.
·
Use
V$DATAFILE to verify status.
|
SQL>
ALTER TABLESPACE index01
OFFLINE; Tablespace
altered. SQL>
SELECT name, status FROM
v$datafile; NAME
STATUS -------------------------------------------------------
------- /home1/user13/ORADATA/u01/system_01.dbf
SYSTEM /home1/user13/ORADATA/u02/undo1_01.dbf
ONLINE /home1/user13/ORADATA/u03/users_01.dbf
ONLINE /home1/user13/ORADATA/u03/indx_01.dbf
ONLINE /home1/user13/ORADATA/u02/sample_01.dbf
ONLINE /home1/user13/ORADATA/u01/query_data_01.dbf
ONLINE /home1/user13/ORADATA/u04/data01.dbf
ONLINE /home1/user13/ORADATA/u03/data02.dbf
ONLINE /home1/user13/ORADATA/u02/index01.dbf
OFFLINE /home1/user13/ORADATA/u03/data031.dbf
ONLINE /home1/user13/ORADATA/u01/undo501.dbf
ONLINE /home1/user13/ORADATA/u01/ronly01.dbf
ONLINE 12
rows selected. SQL>
!mv $HOME/ORADATA/u02/index01.dbf
$HOME/ORADATA/u06/index01.dbf SQL>
ALTER TABLESPACE index01
RENAME DATAFILE
2
'$HOME/ORADATA/u02/index01.dbf'
TO
3
'$HOME/ORADATA/u06/index01.dbf';
Tablespace
altered. SQL>
ALTER TABLESPACE index01
ONLINE; Tablespace
altered. SQL>
SELECT name, status
FROM v$datafile; NAME
STATUS -------------------------------------------------------
------- /home1/user13/ORADATA/u01/system_01.dbf
SYSTEM /home1/user13/ORADATA/u02/undo1_01.dbf
ONLINE /home1/user13/ORADATA/u03/users_01.dbf
ONLINE /home1/user13/ORADATA/u03/indx_01.dbf
ONLINE /home1/user13/ORADATA/u02/sample_01.dbf
ONLINE /home1/user13/ORADATA/u01/query_data_01.dbf
ONLINE /home1/user13/ORADATA/u04/data01.dbf
ONLINE /home1/user13/ORADATA/u03/data02.dbf
ONLINE /home1/user13/ORADATA/u06/index01.dbf
ONLINE /home1/user13/ORADATA/u03/data031.dbf
ONLINE /home1/user13/ORADATA/u01/undo501.dbf
ONLINE /home1/user13/ORADATA/u01/ronly01.dbf
ONLINE 12
rows selected. |