What is the difference between Oracle base, Oracle Home, and Oracle inventory?

Hello, were you ever confused between these keywords – Oracle Home, Oracle Base, and Oracle inventory? Although these words are commonly used in the career of an Oracle database administrator, yet some find it tricky to answer the exact difference between the 3 words.

Today I will try to explain the difference between Oracle Home, Oracle base, and Oracle inventory.

Create DDL from dump file

We will see the Oracle 18c/19c Datapump- SQLFILE
This parameter is used in impdp operation. It will create sqlfile with DDL that could be executed in another database/schema to create the tables and indexes.
Datapump import does not perform an import of the contents of the dump file.  Rather, the sqlfile parameter create a script that contains all DDL from within the export dump file.

Restrictions

1. If SQLFILE is specified, then the CONTENT parameter is ignored if it is set to either ALL or DATA_ONLY.
2. To perform a Data Pump Import to a SQL file using Oracle Automatic Storage Management (Oracle ASM), the SQLFILE parameter that you specify must include a directory object that does not use the Oracle ASM + notation. That is, the SQL file must be written to a disk file, not into the Oracle ASM storage.
3. The SQLFILE parameter cannot be used in conjunction with the QUERY parameter.

Example

impdp oracle/oracle DIRECTORY=TESTDIR DUMPFILE=test.dmp LOGFILE=test.log sqlfile=test_fulldb.sql

Note that passwords are not included in the SQL file. For example, if a CONNECT statement is part of the DDL that was executed, then it will be replaced by a comment with only the schema name shown.
Therefore, before you can execute the SQL file, you must edit it by removing the dashes indicating a comment and adding the password for the schema.

ACL ddl script

Oracle Database provides classic database security such as row-level and column-level secure access by database users. It also provides fine-grained access control for table data and for resources in Oracle XML DB Repository, for secure access by Oracle Fusion users (who are not necessarily database users).
There are thus three dimensions: (1) which users can (2) perform which operations (3) on which data. We speak of :
1. principals,
2. privileges
3. objects.

You can make use of following views for seeing the information of all the ACLs in the system.

select * from dba_network_acls;
select * from dba_network_acl_privileges;

Oracle ACLs

Oracle Database provides classic database security such as row-level and column-level secure access by database users. It also provides fine-grained access control for table data and for resources in Oracle XML DB Repository, for secure access by Oracle Fusion users (who are not necessarily database users).
There are thus three dimensions: (1) which users can (2) perform which operations (3) on which data. We speak of :
1. principals,
2. privileges
3. objects.

Principals are users or roles. Principals and privileges (dimensions 1 and 2) are related in a declarative way by defining access control lists. These are then related to the third dimension, data, in various ways, either declaratively or procedurally. For example, you can protect an Oracle XML DB Repository resource or table data by using PL/SQL procedure DBMS_XDB.setACL to set its controlling ACL.

Setting a Context in PL/SQL

What is a context in Oracle

An application context is a set of (name-value|variable-value) pairs that Oracle Database stores in memory during a session.
Oracle provides a built-in namespace called USERENV, which describes the current session parameter.
All information related to context can be seen using the following query:

SELECT namespace,
schema,
package,
type
FROM dba_context order by schema;

Gather Statistics in Oracle

DBAs should gather statistics periodically for objects where the statistics become stale over time because of changing data volumes or changes in column values. New statistics should be gathered after a schema object’s data or structure are modified in ways that make the previous statistics inaccurate. For example, after loading a significant number of rows into a table, collect new statistics on the number of rows. After updating data in a table, you do not need to collect new statistics on the number of rows, but you might need new statistics on the average row length.

Because the cost-based approach relies on statistics, you should generate statistics for all tables and clusters and all indexes accessed by your SQL statements before using the cost-based approach. If the size and data distribution of the tables change frequently, then regenerate these statistics regularly to ensure the statistics accurately represent the data in the tables.

Oracle generates statistics using the following techniques:

• Estimation based on random data sampling
• Exact computation
• User-defined statistics collection methods

To perform an exact computation, Oracle requires enough space to perform a scan and sort of the table. If there is not enough space in memory, then temporary space might be required. For estimations, Oracle requires enough space to perform a scan and sort of only the rows in the requested sample of the table. For indexes, computation does not take up as much time or space.

How To Generate AWR Report In Oracle

The Automatic Workload Repository (AWR) collects and maintains statistics of the database.
The AWR is used to collect performance statistics including:

• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

Importing and Exporting Optimizer Statistics – II

When preparing to export optimizer statistics, note the following:

1. Before exporting statistics, you must create a table to hold the statistics. The procedure DBMS_STATS.CREATE_STAT_TABLE creates the statistics table.
2. The optimizer does not use statistics stored in a user-owned table. The only statistics used by the optimizer are the statistics stored in the data dictionary. To make the optimizer use statistics in user-defined tables, import these statistics into the data dictionary using the DBMS_STATS import procedure.
3. The Data Pump Export and Import utilities export and import optimizer statistics from the database along with the table. When a column has system-generated names, Original Export (exp) does not export statistics with the data, but this restriction does not apply to Data Pump Export

Read the part 1 here

To generate schema statistics and import them into a separate database:

• On the production host, start SQL*Plus and connect to the production database as administrator.
• Create a table to hold the production statistics.
  For example, execute the following PL/SQL program to create user statistics table tab_stats:

BEGIN   
    DBMS_STATS.CREATE_STAT_TABLE (ownname => 'omkar' ,   stattab => 'tab_stats' ); 
END; 
/

• Gather schema statistics. For example, manually gather schema statistics as follows:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('OM'); 

• Use DBMS_STATS to export the statistics. For example, retrieve schema statistics and store them in the tab_stats table created previously:

BEGIN   
   DBMS_STATS.EXPORT_SCHEMA_STATS (ownname => 'omkar' ,   stattab => 'tab_stats' ); 
END; 
/

• Use Oracle Data Pump to export the contents of the statistics table. For example, run the expdp command at the operating schema prompt:

expdp omkar DIRECTORY=dpump_dir DUMPFILE=stat.dmp TABLES=tab_stats

• Transfer the dump file to the test database host.
• Log in to the test host, and then use Oracle Data Pump to import the contents of the statistics table.For example, run the impdp command at the operating schema prompt:

impdp omkar DIRECTORY=dpump_dir DUMPFILE=stat.dmp TABLES=tab_stats

• On the test host, start SQL*Plus and connect to the test database as administrator .
• Use DBMS_STATS to import statistics from the user statistics table and store them in the data dictionary. The following PL/SQL program imports schema statistics from table tab_stats into the data dictionary:

BEGIN   
  DBMS_STATS.IMPORT_SCHEMA_STATS(ownname => 'omkar' ,   stattab => 'tab_stats'  ); 
END; 
/

Importing and Exporting Optimizer Statistics – I

You can export and import optimizer statistics from the data dictionary to user-defined statistics tables. You can also copy statistics from one database to another database.
Importing and exporting are especially useful for testing an application using production statistics.
Developers often want to tune query plans in a realistic environment before deploying applications. A typical scenario would be to use DBMS_STATS.EXPORT_SCHEMA_STATS to export schema statistics from a production database to a test database.

Mview Refresh Time.

Mview refresh is an important part of any Oracle Database Administrator daily routine.
There are times when we need to track the refresh times for various reasons or in case of troubleshooting any performance issue.
So, Oracle has a very good system table for monitoring the same.

DBA_MVIEW_ANALYSIS

For Complete Refresh, the refresh duration will be in the FULLREFRESHTIM column of the DBA_MVIEW_ANALYSIS.
For Fast Refresh duration, it will be in the INCREFRESHTIM column.
Both values are in seconds.