Spatial DBA - Oracle and ArcSDE: 11/01/2008

Friday, November 28, 2008

DBA_FREE_SPACE_COALESCED

DBA_FREE_SPACE_COALESCED describes statistics on coalesced space in all tablespaces in the database.

Column	Datatype	NULL	Description
`TABLESPACE_NAME`	`VARCHAR2(30)`		Name of the tablespace
`TOTAL_EXTENTS`	`NUMBER`		Total number of free extents in the tablespace
`EXTENTS_COALESCED`	`NUMBER`		Total number of coalesced free extents in the tablespace
`PERCENT_EXTENTS _COALESCED`	`NUMBER`		Percentage of coalesced free extents in the tablespace
`TOTAL_BYTES`	`NUMBER`		Total number of free bytes in the tablespace
`BYTES_COALESCED`	`NUMBER`		Total number of coalesced free bytes in the tablespace
`TOTAL_BLOCKS`	`NUMBER`		Total number of free Oracle blocks in the tablespace
`BLOCKS_COALESCED`	`NUMBER`		Total number of coalesced free Oracle blocks in the tablespace
`PERCENT_BLOCKS _COALESCED`	`NUMBER`		Percentage of coalesced free Oracle blocks in the tablespace

Note:

select * from DBA_FREE_SPACE_COALESCED;

TABLESPACE_ NAME	TOTAL_ EXTENTS	EXTENTS_ COALESCED	PERCENT_ EXTENTS_ COALESCED	TOTAL_ BYTES	BYTES_ COALESCED	TOTAL_ BLOCKS	BLOCKS_ COALESCED	PERCENT_ BLOCKS_ COALESCED
SYSAUX	13	13	100	510590976	510590976	31164	31164	100
SYSTEM	2	2	100	338624512	338624512	20668	20668	100
UNDO	3	3	100	3145728	3145728	192	192	100
USERS	1	1	100	52232192	52232192	3188	3188	100

DBA_EXP_VERSION

DBA_EXP_VERSION displays the version number of the last export session.

Column	Datatype	NULL	Description
`EXP_VERSION`	`NUMBER(3)`	`NOT NULL`	Version number of the last export session

Note:

1. The DBA_EXP views are only used with incremental exports.

To use the DBA_EXP views to track exports, use the incremental option. Use SYS.INCEXP, SYS.INCFIL, and SYS.INCVID tables to get information regarding table, user, and full exports.

If you are doing an incremental export, and the DBA_EXP views are empty, ensure that the Export parameter RECORD has been set to Y. RECORD controls whether or not entries are made to these tables during incremental exports. The default value for RECORD is Y, but if it has been changed to N you could see this problem on an incremental export.

2. Related views:

DBA_EXP_FILES

DBA_EXP_OBJECTS

DBA_EXP_VERSION

Oracle data dictionary views

Oracle dynamic performance views

DBA_EXP_OBJECTS

DBA_EXP_OBJECTS describes objects that have been incrementally exported.

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of exported object
`OBJECT_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of exported object
`OBJECT_TYPE`	`VARCHAR2(12)`		Type of exported object
`CUMULATIVE`	`DATE`		Timestamp of last cumulative export
`INCREMENTAL`	`DATE`	`NOT NULL`	Timestamp of last incremental export
`EXPORT_VERSION`	`NUMBER(3)`	`NOT NULL`	The ID of the export session

Note:

1. The DBA_EXP views are only used with incremental exports.

To use the DBA_EXP views to track exports, use the incremental option. Use SYS.INCEXP, SYS.INCFIL, and SYS.INCVID tables to get information regarding table, user, and full exports.

2. Related views:

DBA_EXP_FILES

DBA_EXP_OBJECTS

DBA_EXP_VERSION

Oracle data dictionary views

Oracle dynamic performance views

DBA_EXP_FILES

DBA_EXP_FILES describes export files.

Column	Datatype	NULL	Description
`EXP_VERSION`	`NUMBER(3)`	`NOT NULL`	Version number of the export session
`EXP_TYPE`	`VARCHAR2(11)`		Type of export file: complete, cumulative, or incremental
`FILE_NAME`	`VARCHAR2(100)`	`NOT NULL`	Name of the export file
`USER_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of user who executed export
`TIMESTAMP`	`DATE`	`NOT NULL`	Timestamp of the export session

Note:

1. The DBA_EXP views are only used with incremental exports.

To use the DBA_EXP views to track exports, use the incremental option. Use SYS.INCEXP, SYS.INCFIL, and SYS.INCVID tables to get information regarding table, user, and full exports.

2. Related views:

DBA_EXP_FILES

DBA_EXP_OBJECTS

DBA_EXP_VERSION

Oracle data dictionary views

Oracle dynamic performance views

DBA_EPG_DAD_AUTHORIZATION

DBA_EPG_DAD_AUTHORIZATION describes the DADs (Database Access Descriptor) that are authorized to use different user's privileges.

Related View

USER_EPG_DAD_AUTHORIZATION describes the DADs that are authorized to use the user's privileges. This view does not display the USERNAME column.

Column	Datatype	NULL	Description
`DAD_NAME`	`VARCHAR2(64)`	NOT NULL	Name of DAD
`USERNAME`	`VARCHAR2(30)`	NOT NULL	Name of the user whose privileges the DAD is authorized to use

Oracle data dictionary views

Oracle dynamic performance views

DBA_ENCRYPTED_COLUMNS

DBA_ENCRYPTED_COLUMNS maintains encryption algorithm information for all encrypted columns in the database. Its columns are the same as those in "ALL_ENCRYPTED_COLUMNS".

Related Views

· ALL_ENCRYPTED_COLUMNS displays encryption algorithm information for the encrypted columns in the tables accessible to the current user.

· USER_ENCRYPTED_COLUMNS displays encryption algorithm information for the encrypted columns in the tables owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the table
`TABLE_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the table
`COLUMN_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the column
`ENCRYPTION_ALG`	`VARCHAR2(29)`		Encryption algorithm used to protect secrecy of data in this column: · `3 Key Triple DES 168 bits key` · `AES 128 bits key` · `AES 192 bits key` · `AES 256 bits key`
`SALT`	`VARCHAR2(3)`		Indicates whether the column is encrypted with SALT (`YES`) or not (`NO`)

Oracle data dictionary views

Oracle dynamic performance views

DBA_DATAPUMP_JOBS

DBA_DATAPUMP_JOBS identifies all active Data Pump jobs in the database, regardless of their state, on an instance (or on all instances for Real Application Clusters). It also show all Data Pump master tables not currently associated with an active job.

Related View

USER_DATAPUMP_JOBS displays the Data Pump jobs owned by the current user. This view does not display the OWNER_NAME column.

Column	Datatype	NULL	Description
`OWNER_NAME`	`VARCHAR2(30)`		User that initiated the job
`JOB_NAME`	`VARCHAR2(30)`		User-supplied name for the job (or the default name generated by the server)
`OPERATION`	`VARCHAR2(30)`		Type of job
`JOB_MODE`	`VARCHAR2(30)`		Mode of job
`STATE`	`VARCHAR2(30)`		Current job state
`DEGREE`	`NUMBER`		Number of worker processes performing the operation
`ATTACHED_SESSIONS`	`NUMBER`		Number of sessions attached to the job
`DATAPUMP_SESSIONS`	`NUMBER`		Number of Data Pump sessions participating in the job

Oracle data dictionary views

Oracle dynamic performance views

DBA_CPU_USAGE_STATISTICS

DBA_CPU_USAGE_STATISTICS displays database CPU usage statistics.

Column	Datatype	NULL	Description
`DBID`	`NUMBER`	`NOT NULL`	Database ID
`VERSION`	`VARCHAR2(17)`	`NOT NULL`	Database version
`TIMESTAMP`	`DATE`	`NOT NULL`	Time at which the CPU usage changed
`CPU_COUNT`	`NUMBER`		CPU count of the database
`CPU_CORE_COUNT`	`NUMBER`		CPU core count of the database
`CPU_SOCKET_COUNT`	`NUMBER`		CPU socket count of the database

Note:

select * from DBA_CPU_USAGE_STATISTICS;

DBID	VERSION	TIMESTAMP	CPU_COUNT	CPU_CORE_COUNT	CPU_SOCKET_COUNT
1234567890	10.2.0.3.0	2008-09-05 10:23:01 AM	4

Oracle data dictionary views

Oracle dynamic performance views

More Oracle DBA tips, please visit Oracle DBA Tips

Thursday, November 27, 2008

V$RESERVED_WORDS

V$RESERVED_WORDS displays a list of all SQL keywords. To determine whether a particular keyword is reserved in any way, check the RESERVED, RES_TYPE, RES_ATTR, and RES_SEMI columns.

Column	Datatype	Description
`KEYWORD`	`VARCHAR2(30)`	Name of the keyword
`LENGTH`	`NUMBER`	Length of the keyword
`RESERVED`	`VARCHAR2(1)`	Indicates whether the keyword cannot be used as an identifier (`Y`) or whether the keyword is not reserved (`N`)
`RES_TYPE`	`VARCHAR2(1)`	Indicates whether the keyword cannot be used as a type name (`Y`) or whether the keyword is not reserved (`N`)
`RES_ATTR`	`VARCHAR2(1)`	Indicates whether the keyword cannot be used as an attribute name (`Y`) or whether the keyword is not reserved (`N`)
`RES_SEMI`	`VARCHAR2(1)`	Indicates whether the keyword is not allowed as an identifier in certain situations, such as in DML (`Y`) or whether the keyword is not reserved (`N`)
`DUPLICATE`	`VARCHAR2(1)`	Indicates whether the keyword is a duplicate of another keyword (`Y`) or whether the keyword is not a duplicate (`N`)

Note:

1. Keywords contained in the view are expanding:

select count(*) from V$RESERVED_WORDS;

8.1.7.4.0	9.2.0.8.0	10.2.0.2.0	10.2.0.3.0	11.1.0.6.0
660	811	1133	1142	1733

2. Get the definition of V$RESERVED_WORDS:

SELECT view_definition

FROM v$fixed_view_definition

WHERE view_name = 'V$RESERVED_WORDS';

Result:

select inst_id, keyword, length,

decode(mod(trunc(type/2),2),0,'N',1,'Y','?') reserved,

decode(mod(trunc(type/4),2),0,'N',1,'Y','?') res_type,

decode(mod(trunc(type/8),2),0,'N',1,'Y','?') res_attr,

decode(mod(trunc(type/16),2),0,'N',1,'Y','?') res_semi,

decode(mod(trunc(type/32),2),0,'N',1,'Y','?') duplicate

from x$kwddef;

3. Query the reserved words in Oracle 11.1.0.6.0

select *

from V$RESERVED_WORDS

where reserved = 'Y'

order by keyword;

KEYWORD	LENGTH	RESERVED	RES_TYPE	RES_ATTR	RES_SEMI	DUPLICATE
!	1	Y	N	N	N	N
&	1	Y	N	N	N	N
(	1	Y	N	N	N	N
)	1	Y	N	N	N	N
*	1	Y	N	N	N	N
+	1	Y	N	N	N	N
,	1	Y	N	N	N	N
-	1	Y	N	N	N	N
.	1	Y	N	N	N	N
/	1	Y	N	N	N	N
:	1	Y	N	N	N	N
<	1	Y	N	N	N	N
=	1	Y	N	N	N	N
>	1	Y	N	N	N	N
@	1	Y	N	N	N	N
ALL	3	Y	N	N	N	N
ALTER	5	Y	N	N	N	N
AND	3	Y	N	N	N	N
ANY	3	Y	N	N	N	N
AS	2	Y	N	N	N	N
ASC	3	Y	N	N	N	N
BETWEEN	7	Y	N	N	N	N
BY	2	Y	N	N	N	N
CHAR	4	Y	N	N	N	Y
CHECK	5	Y	N	N	N	N
CLUSTER	7	Y	N	N	N	N
COMPRESS	8	Y	N	N	N	N
CONNECT	7	Y	N	N	N	N
CREATE	6	Y	N	N	N	N
DATE	4	Y	N	N	N	N
DECIMAL	7	Y	N	N	N	Y
DEFAULT	7	Y	N	N	N	N
DELETE	6	Y	N	N	N	N
DESC	4	Y	N	N	N	N
DISTINCT	8	Y	N	N	N	N
DROP	4	Y	N	N	N	N
ELSE	4	Y	N	N	N	N
EXCLUSIVE	9	Y	N	N	N	N
EXISTS	6	Y	N	N	N	N
FLOAT	5	Y	N	N	N	N
FOR	3	Y	N	N	N	N
FROM	4	Y	N	N	N	N
GRANT	5	Y	N	N	N	N
GROUP	5	Y	N	N	N	N
HAVING	6	Y	N	N	N	N
IDENTIFIED	10	Y	N	N	N	N
IN	2	Y	N	N	N	N
INDEX	5	Y	N	N	N	N
INSERT	6	Y	N	N	N	N
INTEGER	7	Y	N	N	N	Y
INTERSECT	9	Y	N	N	N	N
INTO	4	Y	N	N	N	N
IS	2	Y	N	N	N	N
LIKE	4	Y	N	N	N	N
LOCK	4	Y	N	N	N	N
LONG	4	Y	N	N	N	N
MINUS	5	Y	N	N	N	N
MODE	4	Y	N	N	N	N
NOCOMPRESS	10	Y	N	N	N	N
NOT	3	Y	N	N	N	N
NOWAIT	6	Y	N	N	N	N
NULL	4	Y	N	N	N	N
NUMBER	6	Y	N	N	N	N
OF	2	Y	N	N	N	N
ON	2	Y	N	N	N	N
OPTION	6	Y	N	N	N	N
OR	2	Y	N	N	N	N
ORDER	5	Y	N	N	N	N
PCTFREE	7	Y	N	N	N	N
PRIOR	5	Y	N	N	N	N
PUBLIC	6	Y	N	N	N	N
RAW	3	Y	N	N	N	N
RENAME	6	Y	N	N	N	N
RESOURCE	8	Y	N	N	N	N
REVOKE	6	Y	N	N	N	N
SELECT	6	Y	N	N	N	N
SET	3	Y	N	N	N	N
SHARE	5	Y	N	N	N	N
SIZE	4	Y	N	N	N	N
SMALLINT	8	Y	N	N	N	Y
START	5	Y	N	N	N	N
SYNONYM	7	Y	N	N	N	N
TABLE	5	Y	N	N	N	N
THEN	4	Y	N	N	N	N
TO	2	Y	N	N	N	N
TRIGGER	7	Y	N	N	N	N
UNION	5	Y	N	N	N	N
UNIQUE	6	Y	N	N	N	N
UPDATE	6	Y	N	N	N	N
VALUES	6	Y	N	N	N	N
VARCHAR	7	Y	N	N	N	N
VARCHAR2	8	Y	N	N	N	N
VIEW	4	Y	N	N	N	N
WHERE	5	Y	N	N	N	N
WITH	4	Y	N	N	N	N
[	1	Y	N	N	N	N
]	1	Y	N	N	N	N
^	1	Y	N	N	N	N
\|	1	Y	N	N	N	N
	0	Y	N	N	N	N

Oracle data dictionary views

Oracle dynamic performance views

Spatial DBA - Oracle and ArcSDE

Friday, November 28, 2008

DBA_FREE_SPACE_COALESCED

Oracle data dictionary views

DBA_EXP_VERSION

Oracle data dictionary views

DBA_EXP_OBJECTS

Oracle data dictionary views

DBA_EXP_FILES

Oracle data dictionary views

DBA_EPG_DAD_AUTHORIZATION

Oracle data dictionary views

DBA_ENCRYPTED_COLUMNS

Oracle data dictionary views

DBA_DATAPUMP_JOBS

Oracle data dictionary views

DBA_CPU_USAGE_STATISTICS

Oracle data dictionary views

Thursday, November 27, 2008

V$RESERVED_WORDS

Oracle data dictionary views

Popular Posts in Last 30 Days

About Me

My favorites

Blog Archive

Labels

Disclaimer