8.0.5 tablespaces and query

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
LVL 17
k_murli_krishnaAsked:
Who is Participating?
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
konektorCommented:
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
 
k_murli_krishnaAuthor Commented:
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
 
k_murli_krishnaAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.