How to check exiting table space structure in Sybase 15.7

[Former Member]

Hi All,

I am doing up gradation here it is asking about to create new table spaces But i am new to sybase.

How to check existing table spaces structure for create new table spaces same as old one.

Please help me in this..

How to create new table spaces like old one with another name...

Please give command to check existing and create new one also.

I have searched in sybase tutorials but i didn't find ...

Please help me in this....

Regards

Chandra

[former_member207908 · Accepted solution]

Hi Chandhra,

Since you said that you are new to Sybase (and looks like you know Oracle) the following comparisions between Oracle and Sybase might help you in understanding the data storage concepts of Sybase analogous to Oracle

Note:  FOR UNDERSTANDING PURPOSE ONLY

When you create an Oracle server both the SYSTEM and SYSAUX tablespaces will be created and you cannot drop them thereafter.
When you create ASE, the system specific databases master, model, sybsystemprocs, sybsystemdb and tempdb will be created and you cannot drop them either.

ASE’s master database contains the server wide metadata sys and the dynamic monitoring MDA tables.These tables are functionally equivalent to Oracle’s DBA and Dynamic Performance V$ Views respectively.

In a nutshell, master, model and sybsystemprocs databases are as vital to ASE as are SYSTEM, SYSAUX and control files for Oracle.

ASE’s tempdb database is equivalent in functionality to the Oracle’s temporary tablespace. ASE can have multiple tempdbs and these can be grouped much like Oracle 10g’s temporary tablespace groups.

Storage Concepts
In Oracle data block is the fundamental unit of storage whereas the basic unit of storage in ASE is data page.

Extent is the next unit of storage in ASE. Unlike Oracle where an extent is a specific number of contiguous data blocks, an ASE extent is fixed at 8 contiguous data pages.
The smallest amount of space that a table or index can occupy is one extent.
Extents are always allocated to a table, index, or LOB structure.

The next logical unit of storage above the extent is the allocation unit. An allocation unit is fixed at 32 extents.

Both Oracle and ASE provide logical constructs for mapping of the underlying operating system datafiles.
Oracle calls this construct a tablespace and ASE refers to the construct as device.
An ASE device, like an Oracle tablespace, is a logical structure

Most confusing part of data storage when it comes to ASE is the segment.
In Oracle, a segment is a group of one or more extents that contains all the data for a specific structure within a tablespace, such as table or index.
However, this is not the case in ASE. When an ASE database is created on devices or device fragments, three segments are automatically created on each corresponding device or device fragment.
In ASE the segments are there to constrain where an object can be placed.
For example, you can create a table or index on a specific segment. The system generated segments are called system, default and logsegment respectively. User defined segments can be added to devices if required.

The relationship between Logical and Physical storage schemas in ASE

I hope that this has provided you with the basic understanding of data storage of Oracle and ASE

Regards,
Rajesh

[Former Member · Accepted solution]

Thanks!

It helped me a lot.

BR,

Garry

[former_member89972]

Chandra

Following stored procedures also help to find more details on how a database, devices,segments and objects are inter related

sp_helpdevice [device name]

sp_helpdb [database name]

sp_helpsegment [segment name]

sp_help [table name]

Also to encourage responses like the detailed one from Rajesh  please mark them as helpful answers.

And of course if a response exactly matches to what you were expecting  and solves your problem do mark that as correct answer.

BR

Avinash

[JanStallkamp]

Hi,

ASE uses a different concept for data storage. There is no such thing like table spaces. All SAP tables are stored in one database (named like the SID of your system), monitoring data is stored in a separate database 'saptools' and there are several system databases available. Databases are using so called 'devices' that are (in most cases) files somewhere in the filesystem.

You can see the current setup of databases and devices and how they are linked to each other in the DBA Cockpit.

Please check the getting started guide and the ASE documentation for more details.

Regards,

Jan