Solved

8.0.5 tablespaces and query

Posted on 2002-05-22
5
1,042 Views
Last Modified: 2011-09-20
1) I have oracle enterprise server 8.0.5 on winNT.
2) In 8i, we have system tablespace as default which we should not use, since filled by data dictionary.
3) Also, tablespaces are identified by .DBF files in a particular directory and users tablespace is for us.
4) In 8.0.5, I have created users identified by password without mentioning any tablespace and the data is able to build in number of schemas thus created.
5) What is default tablespace here and can we specify temporary tablespace.
6) Where can we find the database files which constitute tablespaces.
7) Can you give me the list of tablespaces in 8.0.5 with what they represent.
8) Can you give me create tablespace, alter tablespace and alter database commands. At least a link following which I can carry out my DBA activities.
9) Where can I get differences between 8.0.5 and 8.1.6.
10) alter table emp drop column sal; works in 8.1.6 but not in 8.0.5. How do I drop a column in 8.0.5.

Thanx in advance - k_murli_krishna
0
Comment
Question by:k_murli_krishna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 9

Expert Comment

by:konektor
ID: 7027528
5.
select default_tablespace from dba_users where username = '<username>' -- must be connected as sys, or have selest privilege granted on this view
6.
select file_name from dba_data_files where tablespace_name = '<tablespacename>'
7.
system - system cataloges
rbs - rollback
temp - temporary (should be set as temp_tablespace for all users)
users - default tablespace of users
...
8.
alter
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/statem3a.htm#2057323
create
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/statem4c.htm#2063129
for registered users, registration is free
9.
see manual of 8.1.6 section "what's new"
10.
i'm affraid you have to :
create table b_xxx as select col1, col2, col3, ... from xxx
backup all constraints, triggers, grants (via extract ddl in sql navigator by quest software, toad, ...)
drop table xxx
rename b_xxx to xxx
insert backuped constraints, triggers, ...

or
upgrade to 8.1.6 and drop column after that
0
 
LVL 17

Author Comment

by:k_murli_krishna
ID: 7029193
USER_DATA, ROLLBACK_DATA, TEMPORARY_DATA, SYSTEM are the only 4 tablespaces as part of installation.

I have one last question. All tablespace related commands are working from that of 8.1.6. How do we drop a tablespace OR delete all associated datafiles since we cannot resize to 0K/0M/0G.

--- k_murli_krishna
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 200 total points
ID: 7030026
The four tablespaces you listed myabe the only ones created by default in an 8.1.6 installation, but it  certainly is possible to create additional ones.  Any tablespaces that are added could have any file extension (or none) they wouldn't necessarily have a *.DBF extension.

To drop a tablespace, simply
drop tablespace [tablespace_name];
in SQL*Plus if you are logged in as a DBA.

After that is done, you will need to use an operating system tool or command to actually delete the data file(s) that the tablespace used.  On some operating systems (Windows at least) Oracle keeps a lock on the file for at least a few minutes after the "drop tablespace" command finishes, so you may need to wait a few minutes, or stop and restart Oracle before you can delete the datafiles.

One of the advantages of Oracle8.1 is "locally-managed" tablespaces.  This is a way to both improve performance and reduce the possibility for fragmented and/or unusable freespace when tables or indexes are dropped, moved or rebuilt.  I highly recommend that you use locally-managed tablespaces for all but the system tablespace.
0
 
LVL 17

Author Comment

by:k_murli_krishna
ID: 7031945
1) A file extension means fixing the format of the file and the application it opens with. Does not giving no extension lead to confusion and giving any extension stop from it acting as a oracle database file.
2) Are all tablespaces in 8.1.6 apart from system locally managed ones. If so, how to create locally managed custom tablespaces.
3) The 4 tablespaces that I listed belong to 8.0.5. In this version of oracle, locally managed tablespaces do not exist?

--- k_murli_krishna
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7032183
1. No, a file extension is just a clue to the file format and a guide to the application that likely can open the file.  It does not guarantee though that the contents of the file are a particular format.  Oracle can use a data file with any extension (or none).  It records the data file name and extension in its control file when the tablespace is created.  Whenever Oracle is restarted, it simply opens each file listed in the control file.  It does NOT open any/all files that happen to have a *.DBF extension.

2. No, all tablespaces other than SYSTEM in Oracle 8.1.5 or higher may be locally-managed, but by default they are not.  Here is an example of a create tablespace statement I used that creates a locally-managed tablespace:
create tablespace data_small datafile 'K:\ORA_DATA\DATA_SML.ORA' size 40M
  extent management local UNIFORM SIZE 80K;
It is also possible to convert existing dictionary-managed tablespaces to locally-managed ones.

3. Correct, locally-managed tablespaces were introduced in 8.1.5.  They are not supported in 8.0.5.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
make null the repeated levels 2 36
Sybase and replication server 13 56
Procedure syntax 5 48
How to get the current date and Time upon oracle insert into a database table 9 60
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question