how to allocate table space to schema

Hi
I have Schema name XYZ. I need to  Allocate 14 GB Tablespace for XYZ_TBS. How can i achieve it. Any syntax, links, resources highly appreciated. Thanks in advance.
LVL 7
gudii9Asked:
Who is Participating?
 
sdstuberCommented:
you can't really allocate space to a schema per se

you can create a tablespace and then choose to only use it for one schema though.

Here's a simple example that creates a new tablespace with 2 datafiles,  one 10Gb and one 4Gb with rules to allow it to grow to 20Gb if needed.  This will use local extent management with system generated extent sizes.
CREATE TABLESPACE XYZ_TBS 
DATAFILE 
  '/your/path/your_file2.dbf' SIZE 4096M AUTOEXTEND ON NEXT 100M MAXSIZE 10240M,
  '/your/path/your_file1.dbf' SIZE 10240M AUTOEXTEND OFF
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Open in new window

0
 
DavidSenior Oracle Database AdministratorCommented:
Sean, other than personal preferance and assuming logical file systems, what reason for multiple datafiles?
0
 
sdstuberCommented:
simply to illustrate how to use more than one and to show both autoextend on and off.

I couldn't create a single file that was auto on and auto off.

I figured it would be easy enough to extend to multiple files or reduce to just one if needed/desired.

My personal preference would be more like this...  
(start small and allocate as needed, unless I was "sure" I was going to immediately populate it to

CREATE TABLESPACE XYZ_TBS
DATAFILE
  '/my/path/my_xyz_tbs_file1.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 14G
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
or "really"  what my preference would be is...

create tablespace xyz_tbs;


and let omf do the rest  :)
0
 
FayyazCommented:
alter user xyz quota unlimited on XYZ_TBS;
or

alter user xyz quota 14G on XYZ_TBS;
0
 
DavidSenior Oracle Database AdministratorCommented:
I guess at 14 Gb I'd be looking at BIGFILE.  No "big" deal.
0
 
sdstuberCommented:
really? 14Gb is a "bigfile"?


on Fayyaz's post, maybe quota is what the author was asking,  that is "sort of" allocating space to a schema,
It doesn't restrict it though if that was the goal as user abc could also be granted a 14Gb or unlimited quota on XYZ_TBS

I say "sort of" because a quota doesn't actually allocate any space.  A 14Gb quota on a 100 Mb file is not 14Gb of space, it's still only 100Mb and even then only
if no other schema happens to use any of it.
0
 
DavidSenior Oracle Database AdministratorCommented:
Hey, I'm working to re-educate some DBAs here that it's no longer necessary to use 4Gb max file sizes....
0
 
gudii9Author Commented:
Thank you very much. I appreciate your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.