21

This error says that the user doesn't have quota on tablespace SYSTEM which is set as the default persistent tablespace. You can assign a user the quota like this: sql> alter user scott quota 50m on system; Here, 50m means that the user quota on the SYSTEM tablespace is 50 mebibytes. You can also set the quota to unlimited. However it is a bad practice ...


19

The following should do it: Shut down PostgreSQL Make sure PostgreSQL does not run any longer Check that PostgreSQL is really stopped Copy the old data directory to the new drive This is usually defined through a commandline parameter (-D) for your service or through the PGDATA environment variable. Update your PostgreSQL configuration (service, ...


14

When you delete records there is nothing that automatically compacts the segment, therefore you will need to do a segment shrink to reclaim the space. Here is excerpt from the 11.2 Administrator's Guide on Reclaiming Wasted Space: Over time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not ...


9

Autoextensible datafiles still have a size limit. select file_id, bytes, maxbytes from dba_data_files where tablespace_name = 'XYZ'; Maxbytes is the maximum size of the datafile, it can not grow beyond that. If bytes = maxbytes, you have two options: 1) increase the maximum size (maxbytes) as (15 is the file_id from above query), for example to 10G: ...


8

Yes, it will be due to fragmentation. To reclaim the space, first get a list of tables in the tablespace with the following query (ignoring partitions - edit your question if you're using them): select distinct table_name from dba_tables where tablespace_name = 'DATA'; Then for each table, enable row movement: alter table TABLEINDATAPARTITION enable row ...


8

Like this: create table THIS_TABLE ( id number NOT NULL, constraint THIS_TABLE_PK PRIMARY KEY(id) USING INDEX TABLESPACE INDEX_TABLESPACE ) tablespace DATA_TABLESPACE; USING INDEX TABLESPACE is the syntax - you weren't far off. As far as good/bad practice is concerned, that's opinion-based, so not really something that should be asked ...


7

A schema itself can not be stored nor can have changed tablespace en bloc in any way. In fact, it is just a meta-structucre. Instead - there is DEFAULT TABLESPACE attribute of underlying USER. If you change it, then new objects are created in this tablespace by default (unless you excplicitly specify another one). In addition, each type of structure (Table/...


7

Historically, PostgreSQL stored tables and indexes in individual files. Tablespaces are a means of placing multiple tables/indexes into a single file or group of related files in the same directory. Other database management systems use similar techniques, although tney can store multiple objects within a file. On PostgreSQL a tablespace is implemented as ...


7

You really need to look at the definition of sp_spaceused to find your answer of why unused is zero. exec sp_helptext 'sp_spaceused'; go Take a look at a snippet here of the stored procedure: begin /* ** Now calculate the summary data. * Note that LOB Data and Row-overflow Data are counted as Data Pages. */ SELECT @...


7

You have a heap. Heaps don't clear out space with DELETEs in most cases. You could truncate the table, or you could put a clustered index on the table. Heaps are great for insert-heavy systems, but not great if there are lots of deletes.


6

If you just want to remove the tablespace have you tried logging in as sys and dropping it? DROP TABLESPACE <tablespace> INCLUDING CONTENTS CASCADE CONSTRAINTS; If so, what error message do you receive? (I'm assuming you've made a cold backup of the database at this point in case the tablespace is needed.) Alternatively, if you can determine which ...


6

When you get into it -- really get into it -- storing componentized address data is an extremely complicated problem because of all the disparate and varied systems in use globally. I think whatever you develop needs to be balanced between flexibility, and storing only what your business needs to store. The biggest piece of the puzzle here is to move all ...


6

Your select on dba_tables doesn't take into account: Empty blocks in the table (see initial/next, minextents and freelist-related storage parameters among others) Empty space in the blocks (due to pct_free mainly) Block headers (initrans influences this size, among others) I.e. it doesn't take into account the physical storage of the data at all. ...


6

You've missed one place to get an overview of Oracle: the Concepts Guide. It covers all the major topics (including backup and recovery, which is quite important and doesn't appear in the list of links you've posted). Whats the next step? Create the Schema or Tablespace? Both! They're orthogonal. Users are logical entities that access your database. ...


6

In DB2, a tablespace is the object that serves as a layer of abstraction between tables (and indexes) and disk. It is similar to a file group in MS SQL Server. In DB2, a database is roughly equivalent to a SQL Server instance – a database contains multiple tablespaces, transaction logs, one or more bufferpools and other memory areas. A "database" in ...


5

I'm assuming you're not using ASM. Either set the current datafile to AUTOEXTEND: alter database datafile '/full/path/to/system01.dbf' autoextend on next 128m maxsize 8192m; Or, add a new datafile: alter tablespace SYSTEM add datafile '/full/path/to/system02.dbf' size 512m; Obviously, alter the numbers and path as needed. Adding a datafile ...


5

Blocks. Blocks are the units of the DBA_TABLESPACE_USAGE_METRICS.USED_SPACE and DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE. The latter column accounts for possible AUTOEXTEND MAXSIZE. EDIT: I'm not sure what is the meaning of USED_SPACE for undo tablespace though. For example: SQL> SELECT tablespace_name, sum(blocks), status FROM dba_undo_extents ...


5

Not true, unless they meant "materialised views" ORAFAQ Permanent tablespaces are used to store user data and user created objects like tables, indexes and materialized views.


5

For PostgreSQL 9.2 and above there is no postgres system user (by default). It runs as NETWORK SERVICE. Create d:\tablespaces and give the NETWORK SERVICE (SERVICE RÉSEAU in french) "Full control" over it. That should do the trick.


5

This error code, [ORA-01654], means the server has failed to allocate an extent of the required number of blocks for an index segment in the specified tablespace. As you have said you have got 13 datafiles in your tablespace, you are using small file tablespace. In order to get rid of this error, you can simply add data file using ALTER TABLESPACE ADD ...


5

The SYSTEM and SYSAUX tablespaces are reuqired by the RDBMS to function correctly. The SYSAUX tablespace A list of objects that reside in the SYSAUX tablespace can be retrieved using the V$SYSAUX_OCCUPANTS view. This view displays the following information: OCCUPANT_NAME VARCHAR(64) Occupant name OCCUPANT_DESC VARCHAR(64) Occupant ...


5

I agree with EdStevens' comment, but here are some other ideas: The AskTom website has a script that will list exactly how much you can shrink the existing data files. If that doesn't yield much savings, you can find the biggest indexes on that tablespace and rebuild them; that is a temporary solution and the indexes would eventually get big again, but ...


4

I mean when disk is getting full, can we add another disk, format and then CREATE TABLESPACE tblspace LOCATION /media/disk2/data? Is it enough? No, that is not enough - it is true that if "the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until ...


4

I had no problems with a VirtualBox 4.14 Windows 8 preview VM and x64 Oracle 11gR2. My VM uses 40 GB Hard Disk. While installing it downloaded .Net Framework 3.51 I started with a fresh Windows 8 VM in VirtualBox 4.14 and installed guest additions and run Windows update once. Afterwards I installed Oracle and got the items below on my Start page. I find ...


4

I don't think there's much value in me copying what others have done so well in explaining in the past, so I'll just point you at a brilliant blog post: Reclaiming Unused Space in Datafiles. Essentially you just need to do: ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 111M;


4

Stop postgresql service cp -a source_data_directory destination_data_directory chown -R postgres_user /destination_data_directory export PGDATA=destination_data_directory Changing data directory to destination_data_directory within postgresql.conf pg_ctl start


4

The design doesn't meet third normal form, but not just because of the city. The fields STREET, CITY are functionally dependant on each other (if you change the city, the street should probably change as well and vice-versa). You could also have the same street, city combination represented in different ways (Foo St, Foo; Foo Street, Foo; etc.). To ...


4

Few uses for the tablespaces: When you no longer need the data you can drop the tablespace and delete datafiles. This is basically the only way to release space back from Oracle to the operating system. When doing backups you can exclude tablespace from backup. E.g. if it contains non essential or easily recreatable data. When doing restore you can skip ...


4

The comments so far are roughly correct, but to give an authoritative answer from looking at src/backend/tablecmds.c: If you're only performing ALTER TABLE ... SET TABLESPACE ... ;, then ATExecSetTableSpace() will be invoked to handle the SET TABLESPACE, and it uses copy_relation_data() to perform a WAL-logged block-by-block copy of the table. However, if ...


4

I think you are over complicating this. Grants and roles govern access by users not what table space and data files where the data is located in. Yes, backup, recover and export are much easier if schemas have their own table space but that doesn't seem to be your question. Separate your business domains by user/schema. Give each user/schema their own ...


Only top voted, non community-wiki answers of a minimum length are eligible