Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

8.0.5 tablespaces and query

Posted on 2002-05-22
5
Medium Priority
?
1,050 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
  • 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 800 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

885 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