Questions tagged [tablespace]

A tablespace is a database concept and refers to a storage location where the actual data underlying database objects can be kept.

A tablespace is a storage location where the actual data underlying database objects can be kept. It provides a layer of abstraction between physical and logical data, and serves to allocate storage for all DBMS managed segments. (A database segment is a database object which occupies physical space such as table data and indexes.)

Once created, a tablespace can be referred to by name when creating database segments. Tablespaces specify only the database storage locations, not the logical database structure, or database schema. For instance, different objects in the same schema may have different underlying tablespaces. Similarly, a tablespace may service segments for more than one schema. Sometimes it can be used to specify schema as to form a bond between logical and physical data.

cf. wikipedia

319 questions
13
votes
2 answers

CREATE and DROP TABLESPACE Oracle

I have created this tablespace CREATE TABLESPACE IA643_TBS DATAFILE 'IA643_dat' SIZE 500K AUTOEXTEND ON NEXT 300K MAXSIZE 100M; I tried to drop it using this command DROP TABLESPACE IA643_TBS; And it said that it was dropped, when I tried to…
WT86
  • 823
  • 5
  • 13
  • 34
12
votes
5 answers

How to determine MAXSIZE of existing tablespace

I need to determine the MAXSIZE that was set for a tablespace when it was created (Oracle 10g) I'm sure I'm missing something obvious, but the information isn't immediately apparent in the information in DBA_TABLESPACES.
The Archetypal Paul
  • 41,321
  • 20
  • 104
  • 134
11
votes
3 answers

How to get available space in tablespace for a user (Oracle)

I'm working on a web application where I need to warn the user that they're running out of space in the given db user's tablespace. The application doesn't know the credentials of the db's system user, so I can't query views like dba_users,…
Emrah
  • 566
  • 1
  • 4
  • 9
11
votes
2 answers

Index on which tablespace

How to find out which 'tablespace' a particular 'index' belongs to. (oracle) (need to use it via jdbc)
frewper
  • 1,385
  • 6
  • 18
  • 44
11
votes
2 answers

no privileges on tablespace 'USERS'

i have many tables that i can insert rows, but i get this error only for one table; Error starting at line 1 in command: INSERT INTO ERRORLOG (MESSAGE) VALUES ('test') Error report: SQL Error: ORA-01950: no privileges on tablespace 'USERS' 01950.…
10
votes
6 answers

How do you move a partitioned table from one tablespace to another in Oracle 11g?

I have a partitioned table that belongs to tablespace report. I want to move it to tablespace record instead. One possibility is to drop the table and recreate it in the new tablespace, but that is not an option for me, since there is data in the…
Henrik Warne
  • 2,313
  • 2
  • 25
  • 20
8
votes
2 answers

Grant Select, Insert, Update to a Tablespace

I've got a lot of tables in a tablespace, nearly 100. I have to grant Select, Insert, Update privileges on all those tables to a user. Is it possible? When I write: GRANT USE OF TABLESPACE MYTABLESPACE TO USERNAME I get oracle error "invalid or…
Mikayil Abdullayev
  • 12,117
  • 26
  • 122
  • 206
8
votes
3 answers

`psql` command to view all existing tablespaces?

What's the psql command to view all existing tablespaces? \l+ displays all existing databases with their configured tablespace, but it won't display tablespaces which have been created but don't yet contain a database.
jsstuball
  • 4,104
  • 7
  • 33
  • 63
8
votes
3 answers

True tablespace size in oracle

I need to know true tablespace size in Oracle. I have some tablespace and I need to know how many space it uses now and how many space is free (and maybe percent of free space). I found in web some sqls but all of them showed size based on water…
miki
  • 81
  • 1
  • 1
  • 2
7
votes
1 answer

Oracle: how to check space used by a tablespace when no dba privs

I need to check space used by a tablespace but I have no dba privs. Is there a way to do this?
user840930
  • 5,214
  • 21
  • 65
  • 94
6
votes
2 answers

PL/SQL developer import dump

I have a dump file which includes two tables. Now I need to import this dump file. I was instructed to create two tablespaces beforehands.Now how do I import this dump file to these tablespaces. I'm using PL/SQL developer.
Mikayil Abdullayev
  • 12,117
  • 26
  • 122
  • 206
6
votes
2 answers

Oracle Tablespaces maxsize "unlimited" not really unlimited

I recently needed to import a .dmp into a new user I created. I also created a new tablespace for the the user with the following command: create tablespace my_tablespace datafile 'C:\My\Oracle\Install\DataFile01.dbf' size 10M autoextend on next…
spots
  • 2,483
  • 5
  • 23
  • 38
5
votes
2 answers

Monitoring tablespace usage in Oracle XE

As it says on the Oracle XE overview page: Oracle Database XE can be installed on any size host machine with any number of CPUs (one database per machine), but XE will store up to 4GB of user data, use up to 1GB of memory, and use one CPU on the…
thevilledev
  • 2,367
  • 1
  • 15
  • 19
5
votes
2 answers

List only Oracle Temp Table Space

Is there way to list only temp tablespaces in Oracle? I found following query which is listing all the tablespaces, I just need only temp tablespaces. SQL> select tablespace_name from…
Malatesh
  • 1,944
  • 6
  • 26
  • 39
5
votes
2 answers

How to specify separate tablespaces for data and indexes?

I have two tablespaces: one for data and one for index. I can't change this configuration. How can I specify in Hibernate the index tablespace? I've read Hibernate Oracle Tablespace Annotation, but it's not the same problem. I need to specify the…
1
2
3
21 22