22

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 ...


20

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 ...


11

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: ...


9

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 ...


9

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 ...


8

Here's the trick, at least with pgAdmin v4: Leave the tablespace blank. It will default to "pg_default" when creating the database.


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

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

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

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

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 ...


6

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 ...


6

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 ...


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

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 ...


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

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 ...


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

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

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 ...


4

For example, you have a table example_table (optionally in more than one schema): SELECT tablespace FROM pg_tables WHERE tablename = 'example_table' [AND schemaname = 'your_schema']; The same thing for the index example_index: SELECT tablespace FROM pg_indexes WHERE indexname = 'example_index' [AND schemaname = 'your_schema'];


3

How do you define the "tablespace size"? Are you interested in the total size of the data files on disk that comprise the tablespace? Or are you interested in the total size of all the segments that are part of the tablespace? Issuing a DELETE will not affect the size of the table's segment so it will have no impact on the size of the tablespace under ...


3

From what I have read (check out Understanding DB2: Learning Visually with Examples), IBM recommends that you place data, indexes, and long data (meaning LOB type data as well as deprecated types like LONG VARCHAR) into their own tablespaces. Main reason is for maintenance and support. If the data is in different tablespaces you can backup/restore based on ...


3

During the connect process Oracle builds information on what access you have. This remains static for the length of the connection. (If you need to access newly granted privileges, you need to reconnect.) Creating this data as temporary tables would seem the appropriate thing to do. This would require temporary tablespace and could trigger the problems ...


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