Tagged: database

PL/SQL Oracle

Nested table

In PL SQL exists an efficient and adaptable collection of data: Nested table. Nested table is similar to one dimensional array but with some differences : An array has declare number of element – Nested table not. The size of nested table can increase using extend method. An array has always consecutive elements – Nested table has consecutive elements initially but it can become sparse when elements are deleted. To create a nested table you have to use this syntax : type type_name is table of element_type[size element]; table_name type_name; Here below an example to creation and use of a...

Oracle Database

Oracle Database 18c: My preferred new features for DBAs

Oracle Databse 18c comes with some cool new features for DBAs. In this article I’ll list my preferred ones. Just one note: I think the name “Autonomous Database” is quite misleading, as it means that some of the new features COULD be used to configure your database as “autonomous” where previous versions required some maintenance, and this will be the direction of the next Oracle Database generations. 1. PRIVATE TEMPORARY TABLES You can create temporary tables with these characteristics: – NAME must be prefixed by “ORA$PTT_” (or the current value of PRIVATE_TEMP_TABLE_PREFIX initialization parameter) – DATA is automatically deleted at...

Oracle Database 12 c

Setup Oracle 12c Enterprise Manager Express for a PDB

There are many articles on the web explaining how to create, duplicate or plug/unplug a Pluggable Database (PDB) into a Container Database (CDB), e remarkable new feature in Oracle 12c Database. A few of them show how to open and use the Oracle 12c Enterprise Manager Express, the new web graphical administration application created “out-of-the-box” in every Oracle 12c database. When you create a CDB during SW installation or using DBCA, Oracle automatically configures Oracle EM Express on port 5500, so you can access it typing in your browser: https://porfirio.localdomain:5500/em where “porfirio.localdomain” is the name of your host. Well, although...

Oracle Database

Index Tuning

Oracle indexes can be tuned looking to three main characteristics: SELECTIVITY: you can calculate index selectivity using this formula: SELECT DISTINCT_KEYS/NUM_ROWS SELECTIVITY FROM DBA_INDEXES / Values range from 0 to 1, where 1 is the optimal value (UNIQUE indexes and PK), while with values near to 0 performance is lower CLUSTERING FACTOR: measures how many keys are stored in a single index block. This value is stored in DBA_INDEXES.CLUSTERING_FACTOR column. If it’s near to DBA_INDEXES_BLOCKS value then the index is good, if instead it’s near to DBA_INDEXES.NUM_ROWS performance is lower BLEVEL: Oracle indexes are stored in a B-TREE structure where...