Wednesday, December 3, 2008

DBA_OBJECT_SIZE

DBA_OBJECT_SIZE lists the sizes, in bytes, of various PL/SQL objects.

Related View

· USER_OBJECT_SIZE lists the size of PL/SQL objects owned by the current user.

Column

Datatype

NULL

Description

OWNER

VARCHAR2(30)

NOT NULL

Owner of the object

NAME

VARCHAR2(30)

NOT NULL

Name of the object

TYPE

VARCHAR2(12)

Type of the object: TYPE, TYPE BODY, TABLE, VIEW, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, JAVA SOURCE, JAVA CLASS or JAVA RESOURCE

SOURCE_SIZE

NUMBER

Size of the source in bytes. Must be in memory during compilation, or dynamic recompilation.

PARSED_SIZE

NUMBER

Size of the parsed form of the object, in bytes. Must be in memory when an object is being compiled that references this object.

CODE_SIZE

NUMBER

Code size, in bytes. Must be in memory when this object is executing.

ERROR_SIZE

NUMBER

Size of error messages, in bytes. In memory during the compilation of the object when there are compilation errors.

Note:

1. Oracle Metalink (Note 1020288.6) provides a script to report size of stored objects (PL/SQL packages, functions, procedures, Java objects, sequences, types and triggers).

column num_instances heading "Num" format 99999 
column type heading "Object Type" format a12 
column source_size heading "Source" format 99,999,999 
column parsed_size heading "Parsed" format 99,999,999 
column code_size heading "Code" format 99,999,999 
column error_size heading "Errors" format 999,999 
column size_required heading "Total" format 999,999,999 
compute sum of size_required on report 
 
select count(name) num_instances 
      ,type 
      ,sum(source_size) source_size 
      ,sum(parsed_size) parsed_size 
      ,sum(code_size) code_size 
      ,sum(error_size) error_size 
      ,sum(source_size) 
      +sum(parsed_size) 
      +sum(code_size)  
      +sum(error_size) size_required 
from dba_object_size 
group by type 
order by 2
/

NUM_INSTANCES

TYPE

SOURCE_SIZE

PARSED_SIZE

CODE_SIZE

ERROR_SIZE

SIZE_REQUIRED

362

FUNCTION

252230

195189

459916

0

907335

16447

JAVA CLASS

0

0

81741773

935

81742708

298

JAVA DATA

0

0

8671612

0

8671612

792

JAVA RESOURCE

0

0

10784295

0

10784295

8

JAVA SOURCE

0

0

33522

0

33522

879

PACKAGE

5479198

5459040

2814865

0

13753103

803

PACKAGE BODY

16519276

0

19127102

4239

35650617

144

PROCEDURE

301150

127219

391309

0

819678

763

SEQUENCE

0

365802

0

0

365802

818

SYNONYM

0

147102

0

0

147102

90

TABLE

0

115466

0

0

115466

2483

TRIGGER

2212113

0

2648689

1353

4862155

1617

TYPE

843995

1267411

612502

0

2723908

149

TYPE BODY

243634

8393

881753

0

1133780

16

VIEW

0

1595

0

929

2524

Oracle data dictionary views

Oracle dynamic performance views

Posted by Admin at 12/03/2008 11:57:00 AM 0 comments

Labels: ,

DBA_LOBS

 Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

===================================================================


ODBA_LOBS displays the BLOBs and CLOBs contained in all tables in the database. BFILEs are stored outside the database, so they are not described by this view. This view's columns are the same as those in "ALL_LOBS".

Related Views
· ALL_LOBS displays the large objects (LOBs) contained in tables accessible to the current user. LOBs include binary large objects (BLOBs) and character large objects (CLOBs). Binary files (BFILEs) are stored outside the database, so they are not displayed by this view or the related views.
· USER_LOBS describes the LOBs owned by the current user. This view does not display the OWNER column.
Column
Datatype
NULL
Description
OWNER
VARCHAR2(30)
Owner of the object containing the LOB
TABLE_NAME
VARCHAR2(30)
Name of the object containing the LOB
COLUMN_NAME
VARCHAR2(4000)
Name of the LOB column or attribute
SEGMENT_NAME
VARCHAR2(30)
Name of the LOB segment
TABLESPACE_NAME
VARCHAR2(30)
Name of the tablespace containing the LOB segment
INDEX_NAME
VARCHAR2(30)
Name of the LOB index
CHUNK
NUMBER
Size (in bytes) of the LOB chunk as a unit of allocation or manipulation
PCTVERSION
NUMBER
Maximum percentage of the LOB space used for versioning
RETENTION
NUMBER
Maximum time duration for versioning of the LOB space
FREEPOOLS
NUMBER
Number of freepools for this LOB segment
CACHE
VARCHAR2(10)
Indicates whether and how the LOB data is to be cached in the buffer cache:
· YES - LOB data is placed in the buffer cache
· NO - LOB data either is not brought into the buffer cache or is brought into the buffer cache and placed at the least recently used end of the LRU list
· CACHEREADS - LOB data is brought into the buffer cache only during read operations but not during write operations
LOGGING
VARCHAR2(7)
Indicates whether or not changes to the LOB are logged:
· NONE - Not specified
See Also: the *_LOB_SUBPARTITIONS view
· YES
· NO
ENCRYPT
VARCHAR2(4)
Indicates whether or not the LOB is encrypted:
· YES
· NO
· NONE - Not applicable to BasicFile LOBs
COMPRESSION
VARCHAR2(6)
Level of compression used for this LOB:
· MEDIUM
· HIGH
· NO
· NONE - Not applicable to BasicFile LOBs
DEDUPLICATION
VARCHAR2(15)
Kind of deduplication used for this LOB:
· LOB - Deduplicate
· NO - Keep duplicates
· NONE - Not applicable to BasicFile LOBs
IN_ROW
VARCHAR2(3)
Indicates whether some of the LOBs are stored inline with the base row (YES) or not (NO). For partitioned objects, refer to the *_LOB_PARTITIONS and *_PART_LOBS views.
FORMAT
VARCHAR2(15)
Indicates whether the LOB storage format is dependent on the endianness of the platform:
· NOT APPLICABLE
· ENDIAN SPECIFIC
· ENDIAN NEUTRAL
PARTITIONED
VARCHAR2(3)
Indicates whether the LOB column is in a partitioned table (YES) or not (NO)
SECUREFILE
VARCHAR2(3)
Indicates whether the LOB is a SecureFile LOB (YES) or not (NO)
Note:
1.
select table_name, column_name, segment_name, tablespace_name, index_name, in_row
from DBA_LOBS
where owner ='MDSYS';
TABLE_NAME
COLUMN_NAME
SEGMENT_NAME
TABLE
SPACE_
NAME
INDEX_NAME
IN_
ROW
CS_SRS_ORIG
"CS_BOUNDS"."SDO_ELEM_INFO"
SYS_LOB0001026430C00012$$
SYSTEM
SYS_IL0001026430C00012$$
YES
CS_SRS_ORIG
"CS_BOUNDS"."SDO_ORDINATES"
SYS_LOB0001026430C00013$$
SYSTEM
SYS_IL0001026430C00013$$
YES
SDO_COORD_OP_PARAM_VALS
PARAM_VALUE_FILE
SYS_LOB0001026659C00006$$
SYSTEM
SYS_IL0001026659C00006$$
YES
SDO_COORD_OP_PARAM_VALS
SYS_NC00008$
SYS_LOB0001026659C00008$$
SYSTEM
SYS_IL0001026659C00008$$
YES
SDO_COORD_REF_SYS
"LEGACY_CS_BOUNDS"."SDO_ELEM_INFO"
SYS_LOB0001026641C00022$$
SYSTEM
SYS_IL0001026641C00022$$
YES
SDO_COORD_REF_SYS
"LEGACY_CS_BOUNDS"."SDO_ORDINATES"
SYS_LOB0001026641C00023$$
SYSTEM
SYS_IL0001026641C00023$$
YES
SDO_CS_SRS
"CS_BOUNDS"."SDO_ELEM_INFO"
SYS_LOB0001026664C00012$$
SYSTEM
SYS_IL0001026664C00012$$
YES
SDO_CS_SRS
"CS_BOUNDS"."SDO_ORDINATES"
SYS_LOB0001026664C00013$$
SYSTEM
SYS_IL0001026664C00013$$
YES
SDO_GEOR_SYSDATA_TABLE
OTHER_TABLE_NAMES
SYS_LOB0001027507C00007$$
SYSTEM
SYS_IL0001027507C00007$$
YES
SDO_GEOR_XMLSCHEMA_TABLE
XMLSCHEMA
SYS_LOB0001027497C00003$$
SYSTEM
SYS_IL0001027497C00003$$
YES
SDO_GR_MOSAIC_0
SYS_NC00028$
SYS_LOB0001059852C00028$$
TEMP
SYS_IL0001059852C00028$$
YES
SDO_GR_RDT_1
"BLOCKMBR"."SDO_ELEM_INFO"
SYS_LOB0001059858C00013$$
TEMP
SYS_IL0001059858C00013$$
YES
SDO_GR_RDT_1
"BLOCKMBR"."SDO_ORDINATES"
SYS_LOB0001059858C00014$$
TEMP
SYS_IL0001059858C00014$$
YES
SDO_GR_RDT_1
RASTERBLOCK
SYS_LOB0001059858C00015$$
TEMP
SYS_IL0001059858C00015$$
YES
SDO_GR_RDT_2
"BLOCKMBR"."SDO_ELEM_INFO"
SYS_LOB0001059866C00014$$
TEMP
SYS_IL0001059866C00014$$
YES
SDO_GR_RDT_2
"BLOCKMBR"."SDO_ORDINATES"
SYS_LOB0001059866C00015$$
TEMP
SYS_IL0001059866C00015$$
YES
SDO_GR_RDT_2
RASTERBLOCK
SYS_LOB0001059866C00016$$
TEMP
SYS_IL0001059866C00016$$
YES
SDO_INDEX_METADATA_TABLE
"SDO_ROOT_MBR"."SDO_ELEM_INFO"
SYS_LOB0000026741C00043$$
SYSTEM
SYS_IL0000026741C00043$$
YES
SDO_INDEX_METADATA_TABLE
"SDO_ROOT_MBR"."SDO_ORDINATES"
SYS_LOB0000026741C00044$$
SYSTEM
SYS_IL0000026741C00044$$
YES
SDO_MAPS_TABLE
DEFINITION
SYS_LOB0000026938C00004$$
SYSTEM
SYS_IL0000026938C00004$$
YES
SDO_NETWORK_CONSTRAINTS
CLASS
SYS_LOB0001027581C00005$$
SYSTEM
SYS_IL0001027581C00005$$
YES
SDO_STYLES_TABLE
DEFINITION
SYS_LOB0000026945C00005$$
SYSTEM
SYS_IL0000026945C00005$$
YES
SDO_STYLES_TABLE
IMAGE
SYS_LOB0000026945C00006$$
SYSTEM
SYS_IL0000026945C00006$$
YES
SDO_STYLES_TABLE
"GEOMETRY"."SDO_ELEM_INFO"
SYS_LOB0000026945C00013$$
SYSTEM
SYS_IL0000026945C00013$$
YES
SDO_STYLES_TABLE
"GEOMETRY"."SDO_ORDINATES"
SYS_LOB0000026945C00014$$
SYSTEM
SYS_IL0000026945C00014$$
YES
SDO_THEMES_TABLE
STYLING_RULES
SYS_LOB0000026958C00006$$
SYSTEM
SYS_IL0000026958C00006$$
YES
SDO_TOPO_METADATA_TABLE
TOPO_GEOMETRY_LAYERS
SYS_LOB0001026543C00004$$
SYSTEM
SYS_IL0001026543C00004$$
YES
SDO_XML_SCHEMAS
XMLSCHEMA
SYS_LOB0001026936C00003$$
SYSTEM
SYS_IL0001026936C00003$$
YES
USER_CS_SRS
"CS_BOUNDS"."SDO_ELEM_INFO"
SYS_LOB0000026782C00012$$
SYSTEM
SYS_IL0000026782C00012$$
YES
USER_CS_SRS
"CS_BOUNDS"."SDO_ORDINATES"
SYS_LOB0000026782C00013$$
SYSTEM
SYS_IL0000026782C00013$$
YES

Oracle data dictionary views

Oracle dynamic performance views

Posted by Admin at 12/03/2008 11:47:00 AM

Labels: ,

DBA_FGA_AUDIT_TRAIL

DBA_FGA_AUDIT_TRAIL displays all audit records for fine-grained auditing.

Column

Datatype

NULL

Description

SESSION_ID

NUMBER

NOT NULL

Session id of the query

TIMESTAMP

DATE

Date and time of the query in the local database session time zone

DB_USER

VARCHAR2(30)

Database username who executed the query

OS_USER

VARCHAR2(255)

Operating system username who executed the query

USERHOST

VARCHAR2(128)

Client host machine name

CLIENT_ID

VARCHAR2(64)

Client identifier in each Oracle session

ECONTEXT_ID

VARCHAR2(64)

Application execution context identifier

EXT_NAME

VARCHAR2(4000)

External name

OBJECT_SCHEMA

VARCHAR2(30)

Owner of the table or view

OBJECT_NAME

VARCHAR2(128)

Name of the table or view

POLICY_NAME

VARCHAR2(30)

Name of the Fine-Grained Auditing Policy

SCN

NUMBER

System change number (SCN) of the query

SQL_TEXT

NVARCHAR2(2000)

SQL text of the query

SQL_BIND

NVARCHAR2(2000)

Bind variable data of the query

COMMENT$TEXT

VARCHAR2(4000)

Comments

STATEMENT_TYPE

VARCHAR2(7)

Statement type of the query:

· SELECT

· INSERT

· UPDATE

· DELETE

EXTENDED_TIMESTAMP

TIMESTAMP(6) WITH TIME ZONE

Timestamp of the query in UTC (Coordinated Universal Time) time zone

PROXY_SESSIONID

NUMBER

Proxy session serial number, if an enterprise user has logged in through the proxy mechanism

GLOBAL_UID

VARCHAR2(32)

Global user identifier for the user, if the user has logged in as an enterprise user

INSTANCE_NUMBER

NUMBER

Instance number as specified by the INSTANCE_NUMBER initialization parameter

OS_PROCESS

VARCHAR2(16)

Operating System process identifier of the Oracle process

TRANSACTIONID

RAW(8)

Transaction identifier of the transaction in which the object is accessed or modified

STATEMENTID

NUMBER

Numeric ID for each statement run (a statement may cause many actions)

ENTRYID

NUMBER

Numeric ID for each audit trail entry in the session

OBJ_EDITION_NAME

VARCHAR2(30)

Reserved for future use

Note:

The SQL_BIND and SQL_TEXT columns are populated only if the policy has been created with the AUDIT_TRAIL parameter set to db, extended.

Check the link for a demo of fine-grained auditing.

Oracle data dictionary views

Oracle dynamic performance views

Posted by Admin at 12/03/2008 10:53:00 AM 0 comments

Labels: ,

Subscribe to: Comments (Atom)