tablespace

How to move a database to a new tablespace in postgres

This article explains how to move a database to a new tablespace in postgres. This is same as changing the default tablespace for a postgres database.   For this activity we have make sure that no a...

How to create tablespace in postgres with streaming replication

Creating tablespace in a standalone postgres server is a very straight forward. But what if we need to create a tablespace  in postgres with streaming replication enabled. Will the tablespace be crea...

ORA-32773: operation not supported for smallfile tablespace

PROBLEM: While enabling autoextend for an tablespace, got error like ORA-32773: operation not supported for small file table space. SQL> alter tablespace SMALLTS autoextend on; alter tablespace SMA...

ORA-01536: space quota exceeded for tablespace

PROBLEM: While creating a table or inserting data, user is getting error ORA-01536: space quota exceeded for tablespace . SQL> create table OBJCOPY tablespace SMALLTS as select * from dba_objects; ...

How to get tablespace quota details of an user in oracle

This script reports the tablespace quota allocated for each database user and the amount of tablespace consumed by that user. TABLESPACE QUOTA DETAILS OF ALL THE USERS: set pagesize 200 set lines 200 ...

ORA-30041: Cannot grant quota on the tablespace

PROBLEM: Granting tablespace quota to a user is failing with ORA-30041. SQL> alter user dba_raj quota unlimited on TEMP ; alter user dba_raj quota unlimited on TEMP * ERROR at line 1: ORA-30041: Ca...

Tablespace Management in oracle

What is a tablespace and datafile: Tablespace is the primary logic structure of the oracle database. It consists of one or more physical datafiles. Datafiles physical stores database data in storage.(...

How to get ddl of all tablespaces in oracle

 Below script will generate the DDLs of all the tablespaces in oracle. SQL>set heading off; SQL>set echo off; SQL>Set pages 999; SQL>set long 90000; SQL>spool ddl_list.sql SQL>selec...

Query to check tablespace free space

Use below query to check all the space related details of tablespaces.   set feedback off set pagesize 70; set linesize 2000 set head on COLUMN Tablespace format a25 heading 'Tablespace Name' COL...