Thursday, April 23, 2009

SQL for altering user schema to add a new tablespace

The following sql is to create a tablespace after creation of a user schema and then alter the user schema to take the new tablespace as default.
It also changes the system defined tablespace 'temp' as the temporary tablespace for the user.


create tablespace pfss datafile '/u12/oradata/wcrsec01/PFSS.dbf' size 100m autoextend on next 50m maxsize 1000m;

create tablespace pfss_idx datafile '/u12/oradata/wcrsec01/PFSS_IDX.dbf' size 100m autoextend on next 50m maxsize 1000m;

alter user pfss default tablespace pfss temporary tablespace temp;

Alter user pfss quota unlimited on pfss_idx;

Alter user pfss quota unlimited on pfss;

No comments:

Post a Comment