Re: Beginner's questions

gerette_at_cbnewsb.cb.att.com (marianne.g.pittorino) writes:

>
> Hi Oracle Users!
> I am just beginning to learn about Oracle and I have some really basic
> questions. I have just started reading this newsgroup and the little I
> have read impresses me greatly. I would appreciate it if some kind person
> would send me some answers (whether in e-mail or another posting is fine --
> whatever you think is appropriate).
>
> 1) Is it better to create a small database initially and add tablespaces
> to the database instead of creating a large database initially?
>

I prefer to start somewhere in between. That is, you should start small, but with a number of tablespaces. That will keep you from overburdening the SYSTEM tablespace. You can then move rollback segments and temporary segments out of SYSTEM entirely. It's easy to add on more space as you go.

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

Depends. If your applications are small, then use small tablespaces. If you have few disks to spread them over, use larger tablespaces. The trade-offs are disk IO versus separation of applications.

> 3) Are temporary tables and indices easily created/dropped within an
> application using Pro*C?
>
Sorry, don't know ProC. It's simple in SQLPlus, though.

> 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?
>
1. To clear out a database completely, just rerun the create database command. 2. To completely remove a database, delete all its files.

> 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 tablespace datafile should no longer show up as being accessed (in VMS, you can do a show dev/file on the disk to tell). It is not deleted by the database automatically; you have to do it. INVALID simply means that a tablespace with that name has been dropped. No problem.

> 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?
>
Ther is no way to easily rename columns in Oracle. To rename a table, SQL> rename emp to old-emp;
Only tables and views can be renamed in Oracle.

> 7) How is fragmentation monitored and how is it repaired?
>
There are a bazillion fragmentation scripts out there. Call Oracle and request Oracle magazine be sent to you. I have two articles there that'll help: The One Minute DBA (Winter 92) and, for repairing fragmentation problems, Export by Tablespace, The Missing Utility (Spring 92). Fragmentation is repaired by recreating the tablespace and performing and exp/imp of all objects contained there.
>
> 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

No FAQ is out there yet. I may start one up.

Kevin.

       As a matter of fact, no, I don't have a life.   kml%cellar_at_tredysvr.tredydev.unisys.com - My other account is a Mercedes. Received on Fri Sep 04 1992 - 13:50:39 CEST