Continuation of table maintenance revisited (again)

Speaker Notes

Slide 1:

This continues the topic of table maintenance and deals with some useful tools.

Slide 2:

The table with information about objects is called user_objects. Doing a describe of that table shows the field names that can be tested to obtain information .

Slide 3:

As can be seen from the description, the catalog contains the table_name and the table_type.

You could also have keyed in:

DESC cat;

SELECT * FROM cat;

In other words, the user_catalog can be abbreviated as cat.

Slide 4:

User_tables can also be used to get the name of existing tables and other information about the tables by using a SELECT followed by the column name(s) FROM user_tables;

Slide 5:

Notice the difference between DDL statements such as CREATE and DML statements such as INSERT, UPDATE and DELETE. THE DML statemetns can be rolled back, the DDL statements cannot.

Slide 6:

DEFAULTS let the developer put common information in as a default to avoid extensive data entry.

I can tell that it was stored as a number because it is pushed over against the right wall of the column.

Slide 7:

Note that in this example 234 was entered without the single quotes since it is a numeric field. It is stored the same way that it was stored on the previous slide, up against the right wall of the column.

The last day function applied to the sysdate gives the last day of the current month.

Slide 8:

In this example, selected columns and selected rows from maintain00 are used to create newmain00.

A big difference between the two tables is the new column updtprice in newmain00. It was the result of adding 1 to the price column in maintain00.

The new column got name through assigning an alias to the calculation.

Slide 9:

DDL stands for Data Definition Language.

It should be noted that there is no warning with the DROP TABLE command so be VERY careful.

Slide 10:

A table, view, sequence or synonym can be renamed. The objects other than table will be discussed later.

NOTE: RENAME is a DDL so it commits upon execution.

Slide 11:

Notice the difference in response to the DESC new_name and the SELECT * FROM new_name;

Slide 12:

DELETE can also be used to get rid of all rows in the table, however it does not release the storage space and it can be rolled back because it is not a DDL statement.