Oracle DBA (12c/19c) Articles and Scripts

Oracle Database 11g TOP New Features for DBA 1) Automatic Diagnostic Repository 2) Database Replay 3) Automatic Memory Tuning 4) Case sensitive password 5) Virtual columns and indexes 6) Interval Partition and System Partition 7) The Result Cache 8) ADDM RAC Enhancements 9) SQL Plan Management and SQL Plan Baselines 10) SQL Access Advisor & Partition Advisor 11) SQL Query Repair Advisor 12) SQL Replay Advisor 13) DBMS_STATS Enhancements 14) The Result Cache

                      Oracle Database 12c New Features  Increased size limit for VARCHAR2, NVARCHAR2, and RAW datatypes to 32K  (from 4K). We can make a column invisible.  SQL> create table test (column-name column-type invisible); SQL> alter table table-name modify column-name invisible;  SQL> alter table table-name modify column-name visible; We can drop an index online. SQL> DROP INDEX ONLINE. We can drop an constraint online. SQL> DROP CONSTRAINT ONLINE . We can set unused columns online SQL> SET UNUSED COLUMN ONLINE. We can make an index visible of invisible to Optimizer. SQL> ALTER INDEX VISIBLE / INVISIBLE . Oracle Database 12c has new feature called "Identity Columns" which are auto-incremented at the time of insertion (like in MySQL).  SQL> create table dept (dept_id number generated as identity, dept_name varchar); SQL> create table dept (dept_id number generated as identity (start with 1 increment by 1 cache 20 noorder),

Oracle Instance Vs Oracle Database Today we will see the basic terms regarding the oracle database i.e. Oracle Instance, Oracle Database and Oracle Server. Many peoples are always having confusion between Oracle Instance, Oracle Database and Oracle Server. Below is the simple description for all these three terms. 1. Oracle Instance:- Oracle instance is combination of memory structures and background processes. When Oracle instance is started, System Global Area(SGA) is allocated and background processes are started. Instance can open and use only one database at a time. We can not access data from database without starting up oracle instance. 2. Oracle Database:- Oracle database consist operating system files known as database files which provides actual storage information. Oracle database has physical and logical structure. An oracle database consist of three types of files Data Files: These files contains actual data in the database. Redo Files: These files conta

The central set of read-only reference tables and views of each Oracle database is known collectively as the  data dictionary . The  dynamic performance views  are special views that are continuously updated while a database is open and in use. Overview of the Data Dictionary :  An important part of an Oracle database is its data dictionary, which is a read-only set of tables that provides administrative metadata about the database. A data dictionary contains information such as the following: The definitions of every  schema object  in the database, including default values for columns and integrity constraint information The amount of space allocated for and currently used by the schema objects The names of Oracle Database users, privileges and roles granted to users, and auditing information related to users The data dictionary is a central part of data management for every Oracle database. For example, the database performs the following actions: Accesses t