Regarding Logical structure of a DB & Schema

[Former Member]

Hi all,

Need to know some information on Logical structure of ORACLE DB like,,,

---> How many blocks can be placed in an extent?

---> How many data files can be in a table space?

---> Can we see which tables are placed in a table space?

---> What is the naming convention of a schema and what is schema id?

---> How to view PGA and SGA?

IMPORTANT: These are not interview questions, So trying to gather all the details of a DB Architecture.

Thanks in Advance,

Subbu.

[Former Member]

Nice SAP NOTE

[Former Member]

---> How many blocks can be placed in an extent? in a LMTS autoallocate tablespace, it is 64mb -> 8192 blocks or less

---> How many data files can be in a table space? a lot

---> Can we see which tables are placed in a table space?

DB02 -> Space -> Segments -> Detailed Analysis -> Tablespace X or

SQL> select table_name from dba_tables where tablespace_name = 'X';

---> What is the naming convention of a schema and what is schema id?

Today it is SAPSR3, in the past it was either SAPR3 or SAPSID

---> How to view PGA and SGA?

SQL> show parameter pga_aggregate_target

SQL> show sga

Best regards

Michael

EDIT, one remark concerning 'how many data files': its at least 1000+, it is depending on your os version, but who will ever have a tablespace with more than 1000 files? Remember to increase the paramter db_files if needed. Have a look at sap note [606395 - FAQ: Oracle Limitations|https://service.sap.com/sap/bc/bsp/spn/sapnotes/index2.htm?numm=606395] as well.

[Former Member]

Hi MHO,

Thank you a lot..

Thanks & Regards,

Subbu