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
0
votes
1 answer

could not open tablespace directory "pg_tblspc/132528327/PG_9.1_201105231" while cold backup restoration which is having two different tablespace

I'am facing an problem while restoring and recovering of cold backup with WALS. Actually my database storage as two tablespace. I have created one seperate tablespace located in another disk which takes data from it ie., tables which are in other…
0
votes
1 answer

Table space dropped but dbf file still exist

we have dropped a tablespace by command drop tablespace T1 assuming that the datafile will also be dropped. Later we created the same tablespace T1 with different datafile. Now the problem is that datafile is holding 14GB of diskspace. Is there any…
kanna
  • 101
  • 7
0
votes
1 answer

Oracle Enterprise Manager rounding tablespace size?

Does Oracle Enterprise Manager round values or am I missing something in my SQL query? OEM is showing the tablespace size for SYSAUX as 2GB. But, when I query the dba_data_files table, I get 1940 MB. You can see the query I'm using in the image…
eltaco431
  • 740
  • 2
  • 10
  • 24
0
votes
0 answers

Tablespace is not freed after dropping tables (Oracle 11g)

I have a Oracle 11g database with block size = 8192. So, if I'm correct maximum datafile size will be 32GB. I have a huge table containing around 10 million records. Data in this table will be purged often. For purging we chose CTAS as a better…
Shiva Mothkuri
  • 307
  • 3
  • 12
0
votes
3 answers

How can I organize tables in SQL*Plus?

I am practicing SQL, and suddenly I have so many tables. What is a good way to organize them? Is there a way to put them into different directories? Or is the only option to create a tablespace as explained here?
Moeb
  • 10,527
  • 31
  • 84
  • 110
0
votes
1 answer

Oracle tablespace resizing with consecutive free blocks

I often reduce tablespace in our Oracle instance (11g). Using the following script (from system user) I can know exactly the allocated space for each object in choosen tablespace (e.g. MY_TABLESPACE): select tablespace_name, file_id, block_id, …
0
votes
1 answer

Guideline when designing a database in Postgresql

I am designing a database in Postgresql and I would like to have some expert advices before refactorizing my work. The database naturally contains different parts that I plan to separate into schemas in order to have a mangling of object names that…
0
votes
2 answers

ORA-00972 Identifier is too long: while creating tablespace

This is a practice assignment where I have to create a table space with two datafiles. We have to use the exact naming conventions that are given to us. I believe I have it right, but I get this error: "ORA-00972: identifier is too long". I think…
relyt
  • 679
  • 6
  • 14
  • 26
0
votes
1 answer

move table from tablespace 16bit to 32bit-Db2

I need to move a table with its data from tablespace 16bit to 32bit in db2, how to do it? I have read many articles but they are very confusing, table space creation is going to be handled by someone else, I just need to migrate data from previous…
Just_another_developer
  • 5,737
  • 12
  • 50
  • 83
0
votes
1 answer

ORACLE is QUOTA required?

I have a quick question about assigning quotas to users on tablespaces. Does a user require quota in order to select, update and insert data into a table stored in x tablespace? Do I need to grant that user quota on that x tablespace? Thanks
gabz
  • 51
  • 8
0
votes
1 answer

Reuse existing space in tablespace files (ORA-01658: unable to create INITIAL extent for segment in tablespace USERS)

I recently ran into the following Oracle error ORA-01658: unable to create INITIAL extent for segment in tablespace USERS So I extended the USERS tablespace with another 5 GByte file. Unfortunately after some time the error came back and I can see…
Harold L. Brown
  • 8,423
  • 11
  • 57
  • 109
0
votes
1 answer

When and from Where to call or use temporary tablespace in database

In my project we have used temporary tablespace say X_TEMP ,assume below is tablepsace code , I have found this code in tablespace section CREATE TEMPORARY TABLESPACE X_TEMP TEMPFILE '/oradata/mytemp_01.tmp' SIZE 800M EXTENT MANAGEMENT LOCAL…
user3934778
0
votes
0 answers

Oracle dba_ind_partitions vs dba_segments misunderstanding

I've written a piece of SQL script to move all indexes inside a tablespace to other one, the code is OK and working fine. but I am confused about partitioned indexes that why some of them are inside DBA_SEGMENTS and some of them are inside…
DjBuddy
  • 171
  • 1
  • 4
  • 14
0
votes
2 answers

ORA-01652 - Query doen't work with hibernate but it works fine in SQL client

I execute a SQL query with hibernate and the application give the error: ORA-01652: unable to extend temp segment The TABLE SPACE has 4 GB. The strange thing is that the query from the application yesterday was working fine, and today it doen't…
TeTe
  • 217
  • 1
  • 4
  • 11
0
votes
0 answers

Erro MySQL InnoDB tablespace

Hello I created a data base on MySQL InnoDB, and I will created a general tablespace but when I try to created It there was an error message: #1478 - Table storage engine 'InnoDB' does not support the create option 'TABLESPACE or LOGFILE GROUP' Do…
Thomas
  • 1