Questions tagged [tablespaces]

A database storage unit that groups related logical structures together.

Filter by
Sorted by
Tagged with
2
votes
1answer
73 views

Can shrinking temporary TEMP tablespace in Oracle cause any harm?

I have a massive TEMP tablespace that is 1TB large. It consists of 30datafiles, each one is like 31GB in size. Entire TEMP tablespace has a size of 1TB, while used size is only ~600MB. I want to ...
0
votes
1answer
46 views

What is the default quota on a tablespace for a given user in oracle if you don't set one?

My environment I'm using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Explanation of the situation I was creating a user with the following command create user ...
0
votes
1answer
30 views

PostgreSQL: Table and index on different tablespace

In PostgreSQL i have the freedom of choosing to put index (of a table or materialized view) on tablespace other than the one i chose for the table (or materialized view) itself. Is there any ...
0
votes
1answer
68 views

ORACLE DMU (database migration assistant for unicode) Tool

Reading the documentation for Oracle DMU tool, when I try the step create a step for DMU Repository tablespace the document says to use the result of the query for initial size. However; I could not ...
0
votes
1answer
57 views

Oracle Tablespace not using 100% of available space

About a year ago, we added a new tablespace to our Oracle 11g RAC database and we are now using all of the space and are running out of room occasionally. I check on the space available with this ...
1
vote
0answers
69 views

mysql - importing large Tablespace: Lost connection during query

iam trying to recover innodb table which has 1.5M rows from ibd file ( 5.5 GB ) this is the exact steps i do: getting create table query using mysqlfrm command create the table Alter Table discard ...
0
votes
1answer
26 views

“Server not open” / “Drop tablespace” catch-22 situation

I am getting the "server not open" error in SQLPLUS, and the equivalent on Oracle SQL Developer, which says "Oracle Initialization or shutdown in progress". Fair enough. The fix for this is as ...
0
votes
0answers
75 views

can delete manually a .ibd file and .frm file?

I have the following trouble in a mysql server database: 190919 17:35:30 [ERROR] Cannot find or open table abril2017/mexico from the internal data dictionary of InnoDB though the .frm file for ...
1
vote
2answers
67 views

Free space of tablespace deleting table content (rows)

I have a theorical question, last week we have found this error in one database. The error has been given by our app when trying to save some WS data. ORA-01691: unable to extend lob segment RS2....
1
vote
1answer
157 views

ORA-01652: Extend a tablespace in Oracle 12c, no DBA_DATA_FILES table

I know that there are other questions about this topic but reading and trying them they cannot solve my problem. Mainly because select * from DBA_DATA_FILES; shows me an error when executing, because ...
0
votes
0answers
11 views

Backup size keeps growing more then data is added

A schema grows daily, more then data is added. According to table sizes the data in the tables is 2750. The tables spaces are as follows: In storage management is see: Name#Size#Available#...
2
votes
3answers
263 views

In DB2 LUW, When should I use a 4K, 8K, or 16K tablespace rather than just creating a 32K tablespace and being done with it?

We're using DB2 LUW 10.5 & 11.1 on Windows & Linux systems, in case it's relevant to he answers. Question: Is there a time where it would be correct to use 4K rather than 32K? If so, why? (...
0
votes
0answers
30 views

How to use tablespace

I am trying to implement Transperent Data Encryption. I am done with setting up the wallet and creating the tablespace. My doubt is how to specify tablespace so that every object(tables/functions/...
0
votes
1answer
272 views

Checking space availability before a VACUUM FULL (postgres)

Is it possible to check how much re-usable space is available in a tablespace before running a VACUUM FULL on a large table? I have a large postgres table (about 20G) that gets an occasional VACUUM ...
0
votes
1answer
18 views

During table migration of tables from one tablespace to another, does it needs free space to do the alter table move?

During migration of tables from one tablespace to another (all datafiles in the same disk/partition/mount), does it needs free space to do the alter table move? exemple:. I have 30gb of free space on ...
0
votes
2answers
46 views

Moving tables to new tablespace needs temp space or any kind of addictional space? (Oracle 11g)

I've been trying to search for this info on oracle documents but they just don't talk about it. I need to move about 2TB of different tables from 3 different tablespaces to a new tablespace (...
0
votes
2answers
2k views

Best practice for creating tablespaces in Postgresql

PostgreSQL version : 11.2 OS : RHEL or Oracle Linux 7.6 (Yet to be decided) I am at the design stage of setting up a production database. In production, the DB will be around 300GB to 400GB in size. ...
0
votes
1answer
129 views

Oracle12c: Objects attempting to import to original tablespace (despite wanting to remap them)

I've exported a schema from a DB server (Oracle 12.2.0.1.0) using exp with the following command. exp USERID=FOO/FOO2019 FILE="D:\app\ORACLE\admin\orcl\dpdump\FOO.dmp" LOG=FOO.log I now want to ...
0
votes
2answers
290 views

What is the relation between Tablespaces, .frm & .ibd files and database pages in MySQL?

I looked in the documentation but there was not a clear answer regarding their relation, can someone give an real life example of their relation in MySQL? Database pages are smallest read write unit ...
-1
votes
2answers
370 views

Creating Dictionary Management Tablespace Oracle [closed]

i am stuck at creating a dictionary management tablespace in 11g Release 2 Oracle 32 bit , I can't create a normal tablespace with dictionary management because my System tablespace is locally ...
1
vote
1answer
63 views

postgres pg_xlog explodes during set tablespace

I'm running postgreSQL 9.6.0 on CentOS 7.2. Occasionally I need to shift data on to a larger tablespace to prevent /var from filling up. When this is running the pg_xlog directory grows rapidly, and ...
0
votes
1answer
195 views

TEMP Tablespace Removal

Oracle Version: 12.1.0.2 We have a discrepancy between our PROD and DR TEMP tablespace data files. PROD = 30G + 30G + 30G data files DR = 30G + 30G + 30G + 21G + 2G + 1G + 15G data files I ...
3
votes
1answer
292 views

changing tablespace seems to waste space

I have a large (several TBytes) Postgres database (v10 on windows) containing tables that are partitioned by date-range. Depending on volumes, some of them are partitioned by day, others by month. I ...
1
vote
1answer
267 views

Is it safe to move big table to another tablespace while working with it?

I have one big bloated table (real data size approx 65 Gb, 140 Gb on disk). Vacuum does not work cause of low space on default tablespace disk. So I want to clean the database from trash by moving fat ...
1
vote
1answer
2k views

Creating a table space sql developer

I asked this question over at https://stackoverflow.com/questions/52068320/creating-a-table-space-sql-developer, but there was no traction on the question, i thought i might try my luck over here. I'...
2
votes
1answer
1k views

DB2 Tablespace in state Restore pending

I have the problem that in my DB2 database (SLES, Db2 v10.5) my User Temp Tablespaces are all in state Restore pending, Storage must be defined, Storage may be defined. When I set off the following ...
0
votes
1answer
224 views

How to import an existing schema and tablespace into a postgres database, from a filesystem-level backup

Follow-up question from: Have postgres' pg_dump export an index Say I have an existing database, and I want to add to it another schema with content. I saved that content previously through a ...
1
vote
5answers
3k views

TABLESPACE creation error

I have an error "ORA-01537" when I create a tablespace. create tablespace "VOLUMT" datafile 'VOLUMT.dbf' size 250M autoextend on; Then, it shows like following. ERROR at line 1: ORA-01537: cannot ...
4
votes
0answers
1k views

Getting “Cannot open datafile for read-only” for an intentionally DROPped InnoDB table

It's been too long to remember how we got here accurately, but we get the following error on MySQL start (though it does still start the service with InnoDB): [ERROR] InnoDB: Cannot open datafile for ...
0
votes
1answer
105 views

How do you list all items from all databases located in a specific tablespace in postgresql?

I'm trying to clean out some old tablespaces in my postgres instance, and I can't seem to figure out how to list everything in a specific tablespace. SELECT * from pg_tables WHERE tablespace ='...
10
votes
1answer
4k views

Why do I get a PostgreSQL permission error when specifying a tablespace in the “create database” command?

When I create a database in PostgreSQL without explicitly specifying a default tablespace the database is created without issue (I'm logged in as the pgsys user): postgres=> create database rich1; ...
2
votes
1answer
6k views

Oracle 12c export and import schema with different data tablespace and index tablespace

Suppose that I exported the schema objects from my Oracle 12c database using the original export utility as below: exp userid=<export_schema_name>/<export_schema_password>@<db_string&...
7
votes
4answers
24k views

Oracle shrinking / reclaiming free tablespace space

I have a Oracle 12c tablespace with the size of 90GB. Almost 40GB are free. TB1 54 % 90880 41383 49497 46 98304 50 % ONLINE PERMANENT The tablespace has 3 datafiles. E:\ORACLE\...
1
vote
2answers
191 views

Rebalance/move or strink oracle undotablespace

I'm running out of space in oradata01 and oradata02. Is there a ways to move or rebalance with oradata03? /opt/oracle/oradata01/x/undotbs01.dbf 4 100 % /opt/oracle/oradata02/x/undotbs02.dbf ...
0
votes
1answer
642 views

DBA_TABLESPACE_USAGE_METRICS is empty

For one of my 11.2g databases, select * from dba_tablespace_usage_metrics returns no rows. Why is that and how can I fix it? Thank you in advance.
0
votes
1answer
3k views

How to rebuild SYSAUX corrupted tablespace?

I have database 10g 32-bit windows without backup. I want to migrate full database to 11g, but the SYSAUX tablespace is corrupted. Can I rebuild the SYSAUX tablespace?
5
votes
6answers
14k views

Lack of space in SYSTEM and SYSAUX tablespace in oracle 11g R2

SYSTEM and SYSAUX Tablespace size of my DB is in the following status now: SYSTEM - PCT_USED= 99 SYSAUX - PCT_USED= 95 I believe the db is in critical situation now. What can I do to bring back it ...
2
votes
1answer
129 views

Specificness of Tablespace

I'm fairly new to working with databases and am trying to fully understand the purpose of a tablespace. Lets say I have a system where a user will input a schedule of tasks that need to be performed....
0
votes
1answer
37 views

Oracle writes whole file during database creation is it ok?

Consider empty (!) database creation: CREATE TABLESPACE reload DATAFILE 'D:\myDb.dbf' SIZE 3000M ONLINE; This is leads to writring 3000 megabytes to the disk for empty database. Is it normal for ...
2
votes
1answer
9k views

SYSTEM tablespace getting filled up

My SYSTEM tablespace is getting filled up enormously within a few days. I've added another dbf file when it was filled up completely earlier. Now, the second .dbf is also about to fill. The only ...
0
votes
1answer
384 views

Alter Postgres Tablespace [closed]

I can't seem to create a table in a particular tablespace. For instance, when I do: CREATE TABLE myschema.mytable (rc_id integer NOT NULL) TABLESPACE my_space; and then I do: select * from ...
0
votes
1answer
717 views

Why can't I create temporary tablespace?

I'm trying to create dictionary managed tablespace with the TEMPORARY clause and TEMPFILE clause of CREATE TABLESPACE statement. But oracle says I can't use the DEFAULT STORAGE clause doing that... ...
1
vote
1answer
69 views

How much Tablespace we need to create for Oracle schema [closed]

How is the optimum number of tablespace we need to create in an Oracle schema ? One for data and one for index ? Thanks
1
vote
1answer
141 views

How does PostgreSQL decide what to name its tablespace directories?

Using the psql CLI tool with a PostgreSQL 9.6.1 server running on localhost, I created a new tablespace with create tablespace my_tablespace location /tmp/data; and found that this created a ...
-1
votes
1answer
403 views

ORA-01536: Out of space error when doing CTAS

I have two schemas A and B. I'm trying to copy over table B.users to A. I've followed these steps: Run in schema B: GRANT ALL ON users TO A; Run in schema A: CREATE TABLE users AS (SELECT * FROM ...
1
vote
2answers
175 views

How can I insert into a Destination Table from multiple tables concurrently..?

I have a monthly single threaded process that restores about 2,000 databases and pumps data into a table then drops the DB. It takes about 18 hours for the insert to complete (Its via an SSIS Package)....
1
vote
1answer
528 views

oracle schema grants and tablespaces

I have a schema with its tablespace where are application's data are located. Now I need a separate schema which will have an access only to select the data. I wish to create a set of views for that ...
2
votes
1answer
36k views

ORA-00959: tablespace does not exist but I can see it in select

So, I am connected as SYSDBA to Oracle and trying to run this command: create user C##demo identified by demopassword; That ends with User created. Then I run: alter user C##demo default ...
2
votes
1answer
2k views

Oracle: Not able to create tablespace from stored procedure

I am trying to create tablespace from stored procedure. create or replace procedure testprc is query_string VARCHAR2(4000); tbs_name VARCHAR2(20):='test77'; begin dbms_output.put_line('test..'); ...
2
votes
1answer
7k views

How to reduce sysaux tablespace size with Audit Trail Unified enabled

I have Oracle 12c Standard instance with dev database with enabled Audit Trail Unified. And now SYSAUX tablespace has size 32Gb, but all data is only 9Gb. When I tried to reduce size of SYSAUX I ...