Solved

8.0.5 tablespaces and query

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle and DateTime math 6 39
oracle 11g 23 84
Oracle 12c database link between pdb not working 20 76
SQL Developer 6 48
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

777 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