OraFAQ Forum: Performance Tuning » Locally Managed Table syntax

Home » RDBMS Server » Performance Tuning » Locally Managed Table syntax

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

Locally Managed Table syntax [message #123950]

Wed, 15 June 2005 12:11

livetaurean19
Messages: 4
Registered: June 2005

Junior Member

Hi,
I already have LOCALLY MANAGED TABLESPACE, but I need to include LOCALLY MANAGED in the CREATE TABLE syntax as well.
I have no clue as to how to include this in the CREATE TABLE.
If anyone knows how to do this..pls reply ASAP..
Any help would be appreciated..

Report message to a moderator

Re: Locally Managed Table syntax [message #123952 is a reply to message #123950]

Wed, 15 June 2005 12:15

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

No Need.
Just define the quotas on the tablespace to the user.
create the table in the tablespace you want.

Code: [Select all] [Show/ hide]

  1* select tablespace_name,extent_management from user_tablespaces
scott@9i > /

TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         LOCAL
UNDOTBS1                       LOCAL
TEMP                           LOCAL
USERS                          LOCAL
TOOLS                          LOCAL

scott@9i > create table mytable (id number) tablespace users;

Table created.

scott@9i > select table_name,tablespace_name from user_tables where table_name='MYTABLE';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
MYTABLE                        USERS

Report message to a moderator

Re: Locally Managed Table syntax [message #123953 is a reply to message #123952]

Wed, 15 June 2005 12:20

livetaurean19
Messages: 4
Registered: June 2005

Junior Member

Hi ,
My these 2 tablespace(s)
-A_TRAN_512K_INDX
- A_MSTR_512K_DATA

are already LOCALLY MANAGED tablespace(s).
and I've even included their names in the CREATE TABLE syntax.
But still I've been asked to include the LOCALLY MANAGED syntax in the create tables script.
Pls refer to my create table script pasted below.
Pls advice, how do I write the create statements also as 'locally managed'.

CREATE TABLE TEST_LMT (
NBR_ID NUMBER(22) CONSTRAINT XPKLDS_TEST_LMT primary key USING INDEX
STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50)TABLESPACE A_TRAN_512K_INDX,
NBR_NM VARCHAR2 (50) NOT NULL
)
TABLESPACE A_MSTR_512K_DATA LOGGING
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;

[Updated on: Wed, 15 June 2005 12:21]

Report message to a moderator

Re: Locally Managed Table syntax [message #123954 is a reply to message #123953]

Wed, 15 June 2005 12:46

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I am not aware of any such syntax (upto 9i. havent checked 10g).
Extent management is defined during the tablespace creation.

Report message to a moderator

Re: Locally Managed Table syntax [message #123961 is a reply to message #123954]

Wed, 15 June 2005 13:38

livetaurean19
Messages: 4
Registered: June 2005

Junior Member

Ramesh,
Thanks anyways..

Report message to a moderator

Re: Locally Managed Table syntax [message #123964 is a reply to message #123950]

Wed, 15 June 2005 13:53

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

I agree that locally managed is something at the tablespace level. You don't specify it at the table level, because all of the tables inside the same tablespace must be managed in the same way.

It is possible that whoever asked you was referring to the extra table and index properties you have specified in your script that you don't need to specify in a table or index residing in a locally managed tablespace.

For example, you can leave off storage parameters such as initial and the pctused parameter. And if you are also using automatic segment space management you can leave off the freelists.

Report message to a moderator

Re: Locally Managed Table syntax [message #123995 is a reply to message #123964]

Wed, 15 June 2005 16:25

livetaurean19
Messages: 4
Registered: June 2005

Junior Member

Wanted to know, even if I mention these extra parameters still the table would be a part of the locally managed tablespace?
Rather what happens or how does it impact if I mention these extra parameters to a table belonging to a tablespace which has LOCAL EXTENT MANAGEMENT.

Report message to a moderator

Re: Locally Managed Table syntax [message #123999 is a reply to message #123950]

Wed, 15 June 2005 16:34

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

The exact behavior is documented in the sql reference. I'll let you look that up. But, even if you specify them, it is still in a locally managed tablespace. And you specify locally managed at the tablespace level not the table level.

Report message to a moderator

Re: Locally Managed Table syntax [message #124000 is a reply to message #123995]

Wed, 15 June 2005 16:38

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

try it!.
Test it in your local database in your pc.
that is the easiest way.
Any setting in the table level will ovewrite tablespace level setting. But you cannot set extent management in table level.
< quoting docs >
Tablespaces allocate space in extents. Tablespaces can use two different methods to keep track of their free and used space:

* Locally managed tablespaces: Extent management by the tablespace
* Dictionary managed tablespaces: Extent management by the data dictionary

When you create a tablespace, you choose one of these methods of space management. You cannot alter the method at a later time.
Note:

If you do not specify extent management when you create a tablespace, then the default is locally managed.
< /quoting docs >
parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS etc are NOT EFFECTIVE with LMT.

Report message to a moderator

Previous Topic:	How do you cleanup chained rows!
Next Topic:	Storage recomendation please

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 11:27:01 CDT 2025