Transportable Tablespaces [Oracle]

Transportable Tablespaces - Exercise

Creating and transporting tablespaces

Objective:Create a locally managed tablespace and export it for transport.

Exercise scoring

This auto-scored exercise is worth 0 points.

Background/overview

In this exercise, you will create a new tablespace that is locally managed. Then assume time has passed and some data has been stored in the tablespace you created. To transport the tablespace, prepare the tablespace, then prepare the parameter file that will be used to export the file.

Instructions

Follow the instructions in each screen of the simulation.

Submitting your exercise

Run the simulation.

Instructions only

Simulation name: tablespaces

instruction text:

You are in SQL*Plus at this time and logged in as the SYSTEM user. You will create a tablespace named NEWSPACE that is locally managed and has uniform 20MB extents. It should also have the AUTOEXTEND feature turned on. The data file that you use is named 'newspacefile.dbs'. Begin by typing the first line of the command and pressing Enter.

text entry field: Line at the SQL> prompt.

Validation:CREATE TABLESPACE NEWSPACE other notes: upper- or lowercase allowed. Trailing blanks allowed.
Error message: Type the first part of the command. Syntax:
CREATE TABLESPACE tablespacename
tablespaces_020sm.gif
instruction text: Type in the second line of the command, which specifies the data file and the size of the file. The data file that you use is named 'newspacefile.dbs' and is 25MB in size. Type this part of the command and press Enter.
text entry field: Line at the 2 prompt.

Validation:

DATAFILE 'newspacefile.dbs' SIZE 25M

other notes: upper- or lowercase allowed. Trailing blanks allowed.

Error message: Type the next part of the command. Syntax:

DATAFILE 'datafilename' SIZE n

Instruction text:

I have added the AUTOEXTENT ON parameter. Now type in the final line of the command, which specifies that the tablespace is locally managed and has uniform 20MB extents.
Type this part of the command (don't forget the semicolon at the end) and press Enter to execute it.

text entry field:

Line at the 3 prompt.

Validation:

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 20M;

other notes:

upper- or lowercase allowed. Trailing blanks allowed.

Error message:

Type the next part of the command. Syntax:

EXTENT MANAGEMENT {DICTIONARY | LOCAL UNIFORM SIZE n};

Instruction text: The tablespace has been created. Now imagine that time has passed and many objects have been added to the tablespace. Begin the steps necessary to transport this tablespace to another database. First change the status of the tablespace to READ ONLY. Press Enter to execute the command.

text entry field:

Line at the SQL> prompt.

Validation:

ALTER TABLESPACE NEWSPACE READ ONLY;

other notes:

upper- or lowercase allowed. Trailing blanks allowed.

Error message:

Type the command. Syntax:
ALTER TABLESPACE tablespacename READ ONLY;

instruction text:

Now that the tablespace is in READ ONLY status, exit the SQL*Plus window.

Location:

X in top left corner. Action: left mouse click. Go to next image.

Error spot:

Anywhere else, or after pressing Enter.

Error message:

Click the spot that closes this window.

Instruction text:

You are now in a text editor, creating the parameter file that will be used to export the tablespace. Begin by typing the first line of the parameter file, which specifies that this export will be used to transport a tablespace. Press Enter when you are finished to go to the next line.

text entry field: The first blank line.

Validation:

TRANSPORT_TABLESPACE=y

other notes:

upper- or lowercase allowed. Trailing blanks allowed.

Error message:

Type the command. Syntax:
TRANSPORT_TABLESPACE={y|n}

instruction text:

Type the second line of the parameter file, which lists the tablespaces that are to be exported. In this example, there is only one tablespace to export. Press Enter when you are finished typing to go to the next line.

text entry field: The first blank line.

Validation: TABLESPACES=NEWSPACE

other notes:

Upper- or lowercase allowed. Trailing blanks allowed.

Error message:

Type the command. Syntax:
TABLESPACES=tablespace_name

tablespaces_080sm.gif

Instruction text:

Type the third line of the parameter file, which lists the optional parameters for triggers, constraints, and grants. In this example, you are exporting triggers and constraints, but not grants. Press Enter when you are finished typing to go to the next line.

Text entry field: The first blank line.

Validation:

TRIGGERS=y CONSTRAINTS=y GRANTS=n

other notes: upper- or lowercase allowed. Trailing blanks allowed.

Error message: Type the command. Syntax:

[TRIGGERS={y|n}] [CONSTRAINTS={y|n}] [GRANTS={y|n}]

tablespaces_090sm.gif

instruction text:

Type the final line of the parameter file, which lists the file that is created by the export utility. In this example, you want the file to be named newspaceout.dmp. Press Enter when you are finished typing to go to the next line.

text entry field: The first blank line.

Validation:

FILE=newspaceout.dmp

other notes:

upper- or lowercase allowed. Trailing blanks allowed.

Error message: Type the command. Syntax:

FILE=filename

Instruction text:

That is all the text that is needed in the parameter file. You have completed this simulation. Thank you.

Suggested results

First, you created a locally managed tablespace by typing in this command:

CREATE TABLESPACE NEWSPACE
DATAFILE 'newspacefile1.dbs' SIZE 25M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 20M;

Next, you changed it to READ ONLY status:
ALTER TABLESPACE NEWSPACE READ ONLY;
Finally, you created a parameter file that will be used for the export utility. You typed these lines into the parameter file:

TRANSPORT_TABLESPACE=y
TABLESPACES=NEWSPACE
TRIGGERS=y CONSTRAINTS=y GRANTS=n
FILE=newspaceout.dmp