Re: Beginner's Questions

This is a summary of the answers I received to my "Beginner's Questions"; some of the answers listed here are paraphrases and some of the answers were worded so beautifully in the original response that I just copied the words directly. Thanks to all of you who responded!

> 1) Is it better to create a small database initially and add tablespaces
> to the database instead of creating a large database initially?
>

The general attitude is to start with a small database initially, but be sure that the tablespaces created are large enough to handle the initial load of data. It was also noted that a tablespace for rollback segments should be separate from the SYSTEM tablespace.

> 2) Is it better to have lots of little tablespaces or fewer, larger
> tablespaces?
>

This really depends on the application. Lots of little tablespaces give an advantage only if there are lots of physical devices over which to spread them. Many tablespaces on the same physical device will slow performance. Larger tablespaces give some leeway when guessing space requirements.

> 3) Are temporary tables and indices easily created/dropped within an
> application using Pro*C?
>

If I had taken some time to read the SQL manual, I would have found that one could just use EXEC SQL CREATE TABLE/INDEX, etc.

> 4) How do you completely destroy a database (i.e., no more datafiles,
> no more control files, no more anything; I want it to seem as if the
> database NEVER existed!)? Could I just remove all files associated
> with the database or is there some internal table that would be really
> upset if I did?
>

If all the database files are removed, then the database is gone. I was warned to be sure that the database is down when removing the files. To clear out a database completely, just rerun the create database command.

> 5) I created a tablespace, then dropped it (including contents). Why
> isn't the datafile removed and why does the row for the tablespace
> (status = INVALID) still exist in dba_tablespaces?
>

Oracle and the Host machine operating system (in my case, UNIX) are different things; removing a file is operating-system specific. If the datafile is on a raw device, there is no file to remove. The tablespace name remains with a status of INVALID because the tablespace row remains as a placeholder because internal objects are identified by a tablespace sequence number which must remain internally consistent. In addition, the datafile remains because you have the option of recreating the tablespace on the same file and reusing its space.

> 6) Is there any easy way to rename tables (aside from selecting into the
> new table and dropping the old table)? Likewise, is there any easy way
> to rename columns? The RDBMS I use now has simple rename commands:
> rename (old_table_name, new_table_name)
> rename_col (table, old_column_name, new_column_name)
> Does anything like this exist in Oracle?
>

To rename a table, use RENAME old_table TO new_table. Only tables and views can be renamed in Oracle. There is no easy way to rename a column - you have to do a

	create new_table (col1, col2, col3) as
		select old_table (col1, col2_old, col3);

> 7) How is fragmentation monitored and how is it repaired?
>

I got the feeling that this question was really too general to be dealt with in a short posting. It seems that there are many different kinds of fragmentation and many different ways to monitor each and to eliminate each. One person suggested I call Oracle for _Oracle_ magazine; two articles ("The One Minute DBA" from the Winter 92 issue and "Export by Tablespace, The Missing Utility" from the Spring 92 issue) from the magazine should help.

Marianne Received on Tue Sep 22 1992 - 17:42:51 CEST