Re: Beginner's questions

Recently, gerette_at_cbnewsb.cb.att.com (marianne.g.pittorino) writes:
> 1) Is it better to create a small database initially and add tablespaces
> to the database instead of creating a large database initially?
It is better to plan ahead, and create tablespaces large enough for your tables, with the tables pre-allocated (that is, their initial extent size) to their eventual production size (if this can be determined).
>
> 2) Is it better to have lots of little tablespaces or fewer, larger
> tablespaces?
That depends. The virtue of lots of tablespaces is that you can place them on different physical devices, and thereby level the i/o loading. You can assign default tablespaces by user or by application. OTOH, if you don't have lots of devices, lots of tablespaces on the same device doesn't buy anything in terms of performance.
>
> 3) Are temporary tables and indices easily created/dropped within an
> application using Pro*C?
 Yes, 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?
The database includes the control files, and there is no higher authority, so if you destroy the control files and other data files, your database is gone.
>
> 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?
The datafile remains because you have the option of recreating the tablespace on the same file and reusing its space. the tablespace row remains as a placeholder because internal objects are identified by a tablespace sequence number which must remain internally consistent.
>
> 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?
 Use RENAME old_tabel TO new_table.
>
> 7) How is fragmentation monitored and how is it repaired?
There are different kinds of fragmentation and different ways to monitor each. User tables can be considered fragmented when all the data resides in many non-contiguous extents extents. Free (unused) space can have the same problem. When tables are fragged, access slows down; when freespace is fragged, you may have lots of free space in many small chunklets that cannot be used when your table grows. You monitor these things by querying the extent strorage tables. There are many examples of this on the net.
>
>
> If an FAQ file exists for this group, please tell me how I can get a copy. I
> don't want to rehash the same old questions over and over.
>
> Thank you,
> Marianne G. Pittorino

--