TablesHow do I…
4.2 Determine all of the tables owned by a particular user?
4.3 Put comments on a table and its columns?
4.4 Recreate CREATE TABLE statements?
4.5 Determine a table’s initial size parameters?
4.6 Use the ANALYZE command to determine access statistics?
4.7 Create a table from another table?
4.8 Interpret the format of ROWID?
4.9 Create an index-organized table?
Tables are the fundamental data storage objects within a relational database. Without tables, your applications would have no need for Oracle or any relational database. Tables are composed of one or more columns, which define what type of data may be stored, and whether the data is a string of characters, a number, a date, a binary object, or an external file. Tables may relate to each other, bound by common columns, which define Oracle as a relational database. This chapter covers the creation and manipulation of tables within Oracle. Most likely, the Data Definition Language (DDL) statements used to manipulate production database tables in your organization are maintained and executed by the database administrator. Techniques used to design and build tables are important to both application developers and database administrators.
4.1 Create a Table
The most basic and important elements of a database are tables. Usually, all data used by applications and user accounts are stored within tables. This How-To explains the steps and syntax needed to create a regular table.
4.2 Determine All of the Tables Owned by a Particular User
Applications can contain many tables, and there can be several applications within a database. With many users and tables in an Oracle database, it is important to know which users own which tables. This How-To explains the methods used to determine which tables are owned by a particular user.
4.3 Put Comments on a Table and its Columns
Comments can be stored in the data dictionary to describe tables and their columns. The data dictionary is the set of internal Oracle tables that define everything within the database environment. Comments should always be stored in the data dictionary to provide a central repository for information about the schema. The better the database is documented, the easier future maintenance of the applications will be. This How-To covers the process of creating comments on tables and columns.
4.4 Recreate CREATE TABLE Statements
After a table has been altered a few times, the original scripts used to create it may look nothing like the table in production. The best way to re-create the scripts needed to rebuild a table is to query the data dictionary. This How-To covers the task of building CREATE TABLE statements from the data dictionary using SQL*Plus.
4.5 Determine a Table’s Initial Size Parameters
Before creating a table, it is important to determine how much storage space will be needed, as well as knowing how quickly and how large the table will eventually grow. Without this information, performance on the table may be adversely affected. Also, storage for the table may be either wasted or not enough. This How-To takes you through the steps of determining a table’s initial size parameters.
4.6 Use the ANALYZE Command to Determine Access Statistics
Oracle’s Cost Based Optimizer can greatly improve query performance. Objects must have statistics gathered for the CBO to work. This How-To shows the steps needed to use the ANALYZE command to gather statistics on database objects.
4.7 Create a Table from Another Table
Tables can be created from a query of one or more other tables or views. The table can be populated with data when it is created. This How-To covers the methods used to create a table as the result of a query.
4.8 Interpret the Format of ROWID
ROWIDs are pseudo-columns of Oracle tables that are used to quickly access records. They contain information such as file, block, and object location, and uniquely identify records within a table. This How-To describes the ROWID formats and shows how to interpret the ROWID format.