OraFAQ Forum: Server Administration » Need help to create tablespace for administration

Home » RDBMS Server » Server Administration » Need help to create tablespace for administration (oracle, 10.2.0, windows xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need help to create tablespace for administration [message #484350]

Mon, 29 November 2010 22:28

fearroi
Messages: 5
Registered: November 2010
Location: Singapore

Junior Member

Hi, i'm a student currently learning database administration security.
I need help to create a tablespace for administration of database but i don't know what datafile settings are best suited for admin usage.

I have attached the schema that was given to me for this assignment.

Attachment: Untitled.jpg
(Size: 80.43KB, Downloaded 971 times)

Report message to a moderator

Re: Need help to create tablespace for administration [message #484351 is a reply to message #484350]

Mon, 29 November 2010 22:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Need help to create tablespace for administration [message #484364 is a reply to message #484350]

Tue, 30 November 2010 00:51

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Choose a datafile you can create on your server.
What is your real problem to create the tablespace?

Regards
Michel

Report message to a moderator

Re: Need help to create tablespace for administration [message #484367 is a reply to message #484364]

Tue, 30 November 2010 00:57

fearroi
Messages: 5
Registered: November 2010
Location: Singapore

Junior Member

I need help on datafiles settings suited for admin usage. Like the space management and the sizes.

Report message to a moderator

Re: Need help to create tablespace for administration [message #484369 is a reply to message #484367]

Tue, 30 November 2010 01:24

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

I don't know what you mean by "admin usage", but there are some standards that apply to all tablespaces: you should create it as locally managed with automatic segment space management. Do you understand why?
My preference is to use a uniform extent size, but some DBAs disagree: they say system managed extent sizes are preferable. If you intend to use one tablesapce for all your objects, then system managed extent size might be a better option. Think about it.
As for the size of the datafile(s), you will have to make your own estimates based on the expected size and numbers of the rows, not forgetting the indexes. There is a segment space advisor that will help you to do this.

I hope this gives you a few hints that will help you to direct your reading in the documentation.

Report message to a moderator

Re: Need help to create tablespace for administration [message #484373 is a reply to message #484369]

Tue, 30 November 2010 01:48

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

My preference is to use a uniform extent size, but some DBAs disagree: they say system managed extent sizes are preferable.

I think system managed extent is a better option as it is suitable to any table size and it is the best one for a non-admin person like OP.

Regards
Michel

Report message to a moderator

Re: Need help to create tablespace for administration [message #484415 is a reply to message #484373]

Tue, 30 November 2010 07:53

fearroi
Messages: 5
Registered: November 2010
Location: Singapore

Junior Member

Thanks for the advice, but i still need recommendations for the exact size. Admin usage as in used for user account and security and also for auditing

Report message to a moderator

Re: Need help to create tablespace for administration [message #484418 is a reply to message #484415]

Tue, 30 November 2010 07:59

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

To give a size estimate we'ed need to know what you are auditing and what you mean by security (and user accounts - are these oracle accounts or something different?). Could be you need 500M, could be you need 500G or 500T. We have no way of knowing with the information provided.

Report message to a moderator

Re: Need help to create tablespace for administration [message #484419 is a reply to message #484418]

Tue, 30 November 2010 08:10

fearroi
Messages: 5
Registered: November 2010
Location: Singapore

Junior Member

I need to audit things like who accessed the database, who modified data, and who changed the data structure.
User account is public host database account that allows multiple sessions.

The maximum duration for this account's session is 45 minutes and the session can only remain idle for 15 minutes. Allocations should be set on memory and CPU usage to make sure the database is not overloaded due to badly written queries.

Storage for the public host account must be limited to 1MB.
The public host account will have privileges to create the most common database objects. Assign these privileges via a role.

The public host account must be able to view the data owned by your schema (SCHEMA_yourmatricno) but not be allowed to modify the data or structure of the database objects.

These are the things given to me to do.

Report message to a moderator

Re: Need help to create tablespace for administration [message #484425 is a reply to message #484419]

Tue, 30 November 2010 08:45

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Dear fearroi, you totally misrepresented your problem! This is nothing to do with the schema description you supplied, or with space management.

The topics you need to research are database auditing, implemented with the AUDIT command; resource and session management, implemented with CREATE PROFILE; system privileges, assigned with GRANT and CREATE ROLE; and space quotas, set with ALTER USER.

That should give you enough to get started

Report message to a moderator

Re: Need help to create tablespace for administration [message #484462 is a reply to message #484350]

Tue, 30 November 2010 17:43

fearroi
Messages: 5
Registered: November 2010
Location: Singapore

Junior Member

no, that's the second part. Here's the first part.

A. Create a tablespace to house the Order Tracking Schema tables. The tablespace name should be TS_yourmatricno. Derive all other tablespace and datafile settings based on what you have learnt.
B. Create a schema user called SCHEMA_yourmatricno whose default tablespace is the tablespace you have just created. Derive all other user creation settings based on what you have learnt.
C. Create the tables in the schema that you have just created using the script provided to you.

Sorry for giving you the wrong information.

Report message to a moderator

Re: Need help to create tablespace for administration [message #484464 is a reply to message #484462]

Tue, 30 November 2010 18:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Need help to create tablespace for administration [message #484578 is a reply to message #484462]

Wed, 01 December 2010 12:37

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Hello again - it is time you did some work.
Try to create a tablespace. You have the advice on what options to use, so read up on the CREATE TABLESPACE command and have a go. That deals with your (A).
Then create a schema. You have the advice on that too, which should fall into place when you read up the CREATE USER command. That should sort out (B).
Then create your tables, (C).
Any problems: post the SQL you ran, with the errors, according to the Posting Guidelines.
Over to you!

Report message to a moderator

Previous Topic:	which situation will scn increase?
Next Topic:	density of the index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 13:17:05 CST 2024