Solved

8.0.5 tablespaces and query

Posted on 2002-05-22
5
1,046 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup
Suggested Courses

630 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