Questions tagged [tablespaces]
A database storage unit that groups related logical structures together.
23 questions
18
votes
3
answers
129k
views
"ORA-01950: no privileges on tablespace 'SYSTEM' " : What error is it?
Earlier I created a user :
SQL>create user suhail identified by password
SQL>User created.
SQL>Grant create session,create table to suhail;
SQL>Grant Succeeded.
but when I login as ...
- 421
37
votes
4
answers
45k
views
Moving postgresql data to different drive
I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my ...
- 2,023
11
votes
2
answers
3k
views
Recovering logical space from a tablespace
I have a tablespace called DATA, and it is setup with auto-extend as false. This tablespace has two datafiles and is set up so it takes up 350 GB of physical space.
A week ago I queried ...
- 211
3
votes
3
answers
2k
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? (...
- 325
2
votes
1
answer
4k
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..');
...
1
vote
1
answer
4k
views
Oracle - Using index on unique constraint, what the deal?
Using Druid to generate SQL script for Oracle, I have seen a difference between the version 3.10 and 3.13 and I would like to understand what going on.
SQL generated by Druid 3.10 :
CREATE TABLE ...
- 213
1
vote
2
answers
4k
views
How to split a MySQL table file?
I have a table in a MySQL database for which innodb_file_per_table is enabled. One of my tables is about 20GB and growing. How can I partition the table into separate files?
Suppose that I have a ...
- 246
13
votes
6
answers
129k
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,038
7
votes
3
answers
131k
views
How can I resolve this ORA-01652 error when I already added a new file to the TEMP tablespace?
I've inherited basic DBA-like responsibilities on an Oracle database and I'm stuck on a ORA-01652 error while trying to run a query that contains lots of joins and grouping. Here's the error I get:
...
- 173
6
votes
2
answers
4k
views
Is Tablespace something like Logical Volume?
I'm confused about tablespaces in PostgreSQL. Is it something like LVM? I mean when disk is getting full, can we add another disk, format and then CREATE TABLESPACE tblspace LOCATION /media/disk2/data?...
- 2,321
5
votes
3
answers
17k
views
In Oracle how do you move a Schema to a different Tablespace?
I noticed that a Schema has been created in the USERS Tablespace but our local rule is that it should be in a Tablespace by itself. In Oracle how do you move a Schema to a different Tablespace?
- 488
5
votes
2
answers
2k
views
Does changing a Postgresql table's tablespace compact it like "vacuum full" or "cluster"?
I need to reclaim disk space in a table in a Postgresql 9.3.2 database to be used for other tables. The standard suggestions to accomplish this are: "VACUUM FULL" or "CLUSTER" or copy the contents ...
- 283
5
votes
6
answers
33k
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 ...
3
votes
2
answers
13k
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.
...
3
votes
1
answer
7k
views
How to create a tablespace on Windows 7 for Postgresql 9.2?
I try to run this simple command:
CREATE TABLESPACE tb_rec
OWNER postgres
LOCATION 'd:/tablespaces';
However, the tablespace is not created and I got this message:
ERREUR: n'a pas pu ...
- 1,493
3
votes
1
answer
6k
views
DB2: Is it good to create the index table space to use 32 KB data page
I found our DB2 server configs all the index into one table space. And it then defines the table space to use 32kb data page. Is this good? How to judge it? Any advice is welcome.
- 151
3
votes
1
answer
4k
views
How do I access a old saved tablespace after reinstalling postgres?
So a lot of things might have gone wrong here. I first installed postgres 9.3 on mac 9.1 via homebrew to manage some data. I am using postgres.app. Turns out my harddrive was too small so I decided to ...
- 31
3
votes
2
answers
6k
views
What will happen if I drop SYSAUX tablespace from Oracle 11G
As there are two mandatory tablespaces in Oracle SYSTEM and SYSAUX.
They have mention on docs 1 " You cannot drop the SYSTEM tablespace. You can drop the SYSAUX tablespace only if you have the SYSDBA ...
- 81
2
votes
1
answer
468
views
Advantages and use cases of Oracle's tablespaces
I understand that a tablespace is a logical collection of tables and database objects. It could be made up of multiple data files (which are physical collections of objects). Apart from this, what are ...
- 135
1
vote
1
answer
133
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
3k
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 ...
- 169
0
votes
1
answer
2k
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 ...
- 135
0
votes
1
answer
839
views
Postgres tablespace on mounted usb
I've got postgres 9.1 running on raspbian installed with apt-get.
I've got NTFS usb HDD mounted in /mnt/usb.
What I'd like to achieve is to create a tablespace pointing to /mnt/usb/postgres so I ...
- 1