?
Solved

how to allocate table space to schema

Posted on 2009-02-18
9
Medium Priority
?
802 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:gudii9
9 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1200 total points
ID: 23674519
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
 
LVL 23

Expert Comment

by:David
ID: 23674954
Sean, other than personal preferance and assuming logical file systems, what reason for multiple datafiles?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23675037
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 23675051
or "really"  what my preference would be is...

create tablespace xyz_tbs;


and let omf do the rest  :)
0
 
LVL 7

Assisted Solution

by:Fayyaz
Fayyaz earned 400 total points
ID: 23687358
alter user xyz quota unlimited on XYZ_TBS;
or

alter user xyz quota 14G on XYZ_TBS;
0
 
LVL 23

Assisted Solution

by:David
David earned 400 total points
ID: 23687725
I guess at 14 Gb I'd be looking at BIGFILE.  No "big" deal.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1200 total points
ID: 23688566
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
 
LVL 23

Expert Comment

by:David
ID: 23691603
Hey, I'm working to re-educate some DBAs here that it's no longer necessary to use 4Gb max file sizes....
0
 
LVL 7

Author Closing Comment

by:gudii9
ID: 31548442
Thank you very much. I appreciate your help
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month16 days, 15 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question