Questions tagged [tablespaces]
A database storage unit that groups related logical structures together.
189
questions
0
votes
1
answer
73
views
What causes temp tablespace to be full and generates ORA-01652 error
I would like to understand in what sort of situations or what issues could cause TEMP tablespace to run out of space.
Is it too much sorting occur on TEMP?
How do I identify such issue from AWR?
And ...
-4
votes
1
answer
26
views
create a oracle database user with no object in tablespace [closed]
Create user Sara with a password of sara. Make sure that any objects not created
in the system tablespace and Sara can log in and create objects up to 10M in size in the MYUSERS.
how to create a ...
0
votes
0
answers
44
views
xfs Error during move tablespace in Postgresql
I wanted to move tablespaces because it created on $PGDATA folder. During move tablespace of database from test_tbl to pg_default, i got error on disk and database closed. Because Disk gave I/O error ...
- 274
0
votes
1
answer
45
views
Oracle 10g Database - SYSTEM tablespace space
I am concerned about the SYSTEM tablespace. It's space is 99% full. The whole tablespace has 1510 MB and there is 1507 MB already used. Max size of datafile is 32 GB and the autoincrement is on with ...
-1
votes
1
answer
117
views
Mysql, failing xtrabackup and weird undo tablespaces metrics
I'm trying to set up a MySQL replica server, but, since the database size is quite large, I'm using xtrabackup. The latter one constantly fails on start (though I used to use it earlier):
xtrabackup: ...
- 197
0
votes
1
answer
325
views
pg_basebackup and multiple tablespaces
I have the following case:
the master database has an additional tablespace
I'm trying to replicate the master with the pg_basebackup tool
the directory / partition layout on the slave is the same as ...
- 1
1
vote
2
answers
510
views
Different execution time for same query on different Postgres databases
I have two identical databases, development and live.
I run this query on both of them....
select count(*) FROM DeviceB where deviceID not in (SELECT distinct deviceId FROM Device)
On the dev box the ...
- 883
0
votes
1
answer
131
views
Create Tablespace is not working on Postgres - Ubuntu - NTFS
I cant create a new tablespace in mounted directory
psql -U sysuser -d postgres -c "CREATE TABLESPACE dadospostgres LOCATION '/media/tiago/Dados/Postgres/Bancos';"
ERROR: could not set ...
0
votes
1
answer
52
views
Production filesystem is at 100%. Move the datafiles to a new filesystem?
As the title states our file system reached 100% capacity and I want to try moving the tablespaces datafiles to a new filesystem. I'm attempting to follow the steps from this link:
http://www.dba-...
- 11
0
votes
0
answers
93
views
mySQL InnoDB tablespace move to network drive
In Windows 10 with mySQL 8.0.26, I am seeking to move a set of rarely used but occasionally necessary tables from my working SSD to another physical device with more space.
I have attempted to move ...
- 119
0
votes
2
answers
519
views
ORA-01652 - unable to extend TEMP segment?
Having an issue that I can't seem to find any solutions to online.
[72000][1652] ORA-01652: unable to extend temp segment by 256 in tablespace TEMP Position: 581
One of our users runs a query ...
- 11
0
votes
1
answer
3k
views
How do I correct InnoDB Multiple files found for the same tablespace ID?
Some time ago after an update of Ubuntu 20.10, I discovered that MySQL was not running. When I tried to restart it, the error message suggested that it had started, failed and stopped. A check of the ...
- 11
0
votes
1
answer
433
views
MySQL 8.0.23: Removeing huge '#sql-ib****.idb files?
I had a massive mysql database crash this morning and realized it was due to the server running out of disk space. I recently upgraded mysql to version 8.0.23 last week from 5.7.
As I was looking for ...
- 33
0
votes
1
answer
51
views
Estimate Oracle tablespace required before loading data
I have files ranging from 450 to 750 million rows, which I need to load into our Oracle environment. The DBA asked me to give an estimate on how much tablespace I would need.
How to estimate the ...
- 101
0
votes
1
answer
511
views
ORACLE execute error: ORA-01950: no privileges on tablespace 'PDATA'
I'm new in Oracle, so maybe my question could be stupid. I use Oracle only for data storage. I have made some research but I'm blocked. I use Oracle 12c. I created a PDB with admin user PEEI_SYS like ...
1
vote
0
answers
36
views
Naming a datafile with spaces in the name
I would like to know if there could be a problem when naming a datafile with spaces in the name, I have an Oracle 11gR2 database, using ASM as storage, a datafile was created, belonging to an ...
- 135
2
votes
2
answers
517
views
Ramifications of Full SYSAUX - Oracle
What are the ramifications of having a full or almost nearly full SYSAUX tablespace (Oracle 19c)?
There are plenty of sites discussing how to view the SYSAUX occupants, to reduce the size of SYSAUX ...
- 123
1
vote
1
answer
6k
views
mysqldump with --no-tablespaces on InnoDB with index?
Does mysqldump --no-tablespaces dumps index files of InnoDB?
I know that MyIsam uses separate index files *.MYI for index. So if one uses mysqldump with --no-tablespaces, does that mean index is not ...
- 31
0
votes
1
answer
243
views
How to quota a CDB only tablespace to a CDB user?
I can create a CDB tablespace like this: (all using: sqlplus / as sysdba)
-- create unique table space for admin
CREATE TABLESPACE admints DATAFILE '/path/to/admints.dbf' SIZE 20M AUTOEXTEND ON;
I ...
- 814
1
vote
1
answer
30
views
Creating table space steps [closed]
In order for me to create space in the database is this all I need to do?
alter tablespace USER_DATA_TBS add datafile '/oradata/bosv/user_data04.dbf' size 10m;
Or are there other steps I need to ...
0
votes
2
answers
238
views
MySQL one file per table not working
I've followed all of the instructions for enabling the one file per table in MySQL (v8, on Ubuntu 20):
Added innodb_file_per_table=ON
Restarted MySQL
Dumped all databases, one by one.
However, when ...
- 113
2
votes
1
answer
1k
views
Missing mysql, sys, performance_schema
I have Percona Mysql 8.0.19-10 on debian 10.
A year ago I had some issues restoring a backup that required me to mess with tablespaces. I forget what all I had done but eventually I got the database ...
- 163
0
votes
1
answer
357
views
Get tablespace name from dump file
I have an Oracle dump file which I need to import but do not know what is its tablespace name. How can I get it from the dump file? I know that it is possible to get many information using impdp ...
- 103
0
votes
1
answer
434
views
could not read symbolic link error while executing pg_start_backup function in Postgresql DB
In order to take data folder backup in postgres 9.6 database executed the order of functions for backup,
while executing pg_start_backup function produced warning : could not read symbolic link for ...
- 1
0
votes
0
answers
48
views
How to get dependencies between tablespaces?
For example, we have tablespace1 and tablespace2.
The tablespace1 has the table called table1 owned by soe user.
The tablespace2 has a table called table2 owned by soe user, which has a foreign key ...
- 566
1
vote
2
answers
487
views
How do I move tables and indexes owned by user to new tablespaces in Oracle 19C?
I have a user named SDE and the tablespace for this users tables are in SDE_TBS, I would like to move all of these tables to tablespace SDEBUS_DT. For indexes I would like to move to SDEBUS_IX. How ...
- 113
-1
votes
1
answer
87
views
How to locate the tablespace objects that have grown suddenly?
In my Oracle 11g database, I have one of my tablespace that has grown suddenly during the night.
I would like to find the tablespace objects (e.g. tables or index) that have grown suddenly as occupied ...
0
votes
0
answers
910
views
yet another Tablespace is missing
work0.psg.com:/var/db/mysql/wordpress# mysqldump -p --databases wordpress > dump.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all ...
- 1
0
votes
0
answers
20
views
How to shutdown and startup a database to delete datafiles in between
OS: Windows 10 Professional / DBMS: Oracle 18c XE
How would one approach the issue of losing a connection while shutting down the database?
I would like to have one script which deletes datafilepaths ...
- 61
0
votes
1
answer
510
views
Warning message: invalid value for parameter "default_tablespace" in postgres
I create a new tablespace using the following script:
CREATE SCHEMA semiarido;
CREATE ROLE semiarido PASSWORD 'senha';
GRANT CREATE ON SCHEMA semiarido TO semiarido;
GRANT USAGE ON SCHEMA semiarido ...
- 111
0
votes
1
answer
917
views
Adding multiple files to a tablespace
In Oracle, we have huge amount of data imported daily, which requires us to create data files off and on in a tablespace.
Wonder if we create two data files simultaneously, will it use both when one ...
mrd
1
vote
1
answer
358
views
Changing default permenant USERS tablespace to bigfile
I'm trying to build an oracle database server but it's setting up the users tablespace as a smallfile. How can I either install brand new database with users being bigfile or changing the existing ...
- 11
1
vote
1
answer
788
views
Postgres use multiple tablespaces in a single database
Is it possible to allocate tables directly to tablespaces within a single database/schema in postgres? Based on the docs, tablespace is a create database argument, but are there any workarounds?
- 113
1
vote
1
answer
2k
views
Get tablespace of a given table in DB2
What is the command to get the name of tablespace of a given table in DB2 database ?
- 25
0
votes
2
answers
1k
views
ORACLE TABLESPACE USERS GETS 100% USED
I am facing a problem with oracles users tablespace. It automatically grows really fast. What kind of data is stored there? And what happens when it hits 100% of space used? Is there a solution to ...
0
votes
2
answers
2k
views
Postgres tablespace size management
I have a Postgres 10 database which is the model for an MVC app.
On it many (200,000) LOBS get saved, eventually processed, and stored or deleted depending on some conditions.
I have two problems.
...
4
votes
1
answer
399
views
Continuously Adding SSDs to an existing PostgreSQL Server
An Ubuntu 18.04 server dedicated to running a PostgreSQL 11.2 server (with TimescaleDB extension) will be running out of disk space soon so new SSD disks will need to be added to the machine to ...
- 1,031
3
votes
1
answer
2k
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 ...
- 123
0
votes
1
answer
3k
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 ...
- 127
1
vote
1
answer
602
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 ...
- 179
-1
votes
1
answer
176
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 ...
- 35
0
votes
1
answer
469
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 ...
- 113
1
vote
0
answers
3k
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 ...
- 11
0
votes
1
answer
79
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 ...
- 177
0
votes
0
answers
1k
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 ...
- 52
1
vote
2
answers
3k
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
1
answer
824
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 ...
3
votes
3
answers
1k
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? (...
- 315
0
votes
0
answers
43
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/...
- 101
2
votes
1
answer
2k
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 ...
- 883