Learn Database Concepts

When to create control files ? =>  Create control files in situations when : 1. You have lost all your control files. 2. When you want to rename your database name (db_name); Note :although we can use utility called DBNEWID to change the DBNAME. DBNEWID can be used to change : -- Only the DBID of a database -- Only the DBNAME of a database -- Both the DBNAME and DBID of a database  How to create control files. You need a create controlfile script for recreating control files. Code: SQL*Plus: Release 11.2.0.0 - Production on Fri Feb 28 17:04:00 2003 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. Enter user-name: sys as sysdba Enter password: Connected to:  Enterprise Edition Release 11.2.0.0 - Production With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options SQL> alter database backup controlfile to trace; Database altered. SQL> This will create a trace file in the udump directory. In my case it was E:\oracle\ad...

When to create control files ? =>  Create control files in situations when : 1. You have lost all your control files. 2. When you want to rename your database name (db_name); Note :although we can use utility called DBNEWID to change the DBNAME. DBNEWID can be used to change : -- Only the DBID of a database -- Only the DBNAME of a database -- Both the DBNAME and DBID of a database  How to create control files. You need a create controlfile script for recreating control files. Code: SQL*Plus: Release 11.2.0.0 - Production on Fri Feb 28 17:04:00 2003 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. Enter user-name: sys as sysdba Enter password: Connected to:  Enterprise Edition Release 11.2.0.0 - Production With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options SQL> alter database backup controlfile to trace; Database altered. SQL> This will create a trace file in the udump directory. In my case it was E:\oracle\ad...

dbms_service.create_service :   We can call dbms_service.create_service procedure to create new service names , then start these service names for user connections. The procedure dbms_service.create_service requires the service name and service network name, use the service name to manage the service name. oracle@LINUX201:[~] $ sqlplus /"As sysdba" SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:17:50 2016 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select SERVICE_ID,name from V$SERVICES; SERVICE_ID NAME ---------- ----------------------------------------------------------------          4 ORCL          1 SYS$BACKGROUND          2 SYS$USERS ****************************** ****************************** LEST CRE...

---- DISABLE TABLE NAMES STARTING WITH TMP OR BAK in Database ---- Intially : create table tmp_test (fname varchar2(20)); After enabling below trigger, no more tables can be created in database starting with TMP or BAK: create or replace TRIGGER NO_TMP_TABS_TRIG BEFORE CREATE ON DATABASE DECLARE  x user_tables.table_name%TYPE; BEGIN   SELECT ora_dict_obj_name   INTO x   FROM DUAL;   IF SUBSTR(x, 0, 4) = 'TMP_' or SUBSTR(x, 0, 4) = 'BAK_' THEN     RAISE_APPLICATION_ERROR(-20099, 'TABLE NAMES CAN NOT START WITH THE WITH TMP% OR BAK%');   END IF; END NO_TMP_TABS_TRIG; Lets test it : create table tmp_test (fname varchar2(20)); Error : Error starting at line : 15 in command - create table tmp_test (fname varchar2(20)) Error at Command Line : 15 Column : 1 Error report - SQL Error: ORA-00604: error occurred at recursive SQL level 1 ORA-20099: TABLE NAMES CAN NOT START WITH THE WITH TMP% OR BAK%.

ARC1: STARTING ARCH PROCESSES COMPLETE ARC2: Becoming the heartbeat ARCH Sun Jun ** 10:46:08 **** Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 134217728 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query: select total_size,awr_flush_emergency_count from v$ash_info; SQL> select total_size/1024/1024,awr_flush_emergency_count from v$ash_info; TOTAL_SIZE/1024/1024 AWR_FLUSH_EMERGENCY_COUNT -------------------- -------------------------                  128                         1 This is not a dynamic paramter : sql > alter system set "_ash_size"=200M scope=spfile; SQL> select t...

Monitoring tracks the approximate number of  deletes, updates & inserts  operations for the table since the last time statistics were gathered. Information about how many rows are affected is maintained in the SGA, until periodically (about every three hours) SMON incorporates the data into the data dictionary.  Enabling table monitoring : create table TEST as select OBJECT_name from all_objects; table TEST created. select count(*) from test;   COUNT(*) ----------     120118 select table_name,inserts,updates,deletes,truncated,timestamp    from sys.dba_tab_modifications   where table_owner='ATOORPU' and table_name='TEST';   --- no rows selected   Enable FLUSH_DATABASE   exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;   anonymous block completed   ENABLE MONITORING ON TABLE:   alter table TEST monitoring;   select table_name,inserts,updates,deletes,truncated,timestamp    from sys.dba_tab_modifi...

                                        vboxmanage command line  For our scenario : Lets create shared disks (vdi) on Virtualbox for ASM setup.  If you are getting error like below make sure you have virtual-box set in your environmental path: In my case I have to add "C:\Program Files\Oracle\VirtualBox\" to my path. To add it to your environmental path goto : Startup >> computer (right click) >> properties >> advanced system settings >> environmental variables >>   Click on start Right click on Computer Click Properties Click Advanced system settings Click Environment Variables Go to system variables find Path and edit it add your file location at the end of the box. Add your virtual box installed path, usually c\programe files \ oracle Now test the vboxmanage cmd : You will get something like this if you have any virtual machines cr...

Oracle Database files that needed to be excluded in Anti-virus scan or any third party tools scan: If you do not exclude these files, there is maximum chance that your oracle files will be held into lock, while the scan is happening and oracle might have access on them when this scan happens and it might lead to i/o exceptions.  In fact this is applicable to any Database system. This might also lead to chances where your Database system might crash. Always exclude these files : Data files   Data files generally have a *.dbf extension Example : ....\oracle\oradata\*.dbf Redo files   Redo files have a *.log extension NOTE: Redo logs will exist if the Oracle Development toolkit or backup and recovery are used)   Example : ....\oracle\Inventory\logs\*.log Control files   Control files have a *.ctl extension Example : ....\oracle\oradata\*.ctl YOU CAN GET THE PATHS FROM BELOW QUERIES : select name from v$datafile; select name from v$...

Oracle Database files that needed to be excluded in Anti-virus scan or any third party tools scan: If you do not exclude these files, there is maximum chance that your oracle files will be held into lock, while the scan is happening and oracle might have access on them when this scan happens and it might lead to i/o exceptions.  In fact this is applicable to any Database system. This might also lead to chances where your Database system might crash. Always exclude these files : Data files   Data files generally have a *.dbf extension Example : ....\oracle\oradata\*.dbf Redo files   Redo files have a *.log extension NOTE: Redo logs will exist if the Oracle Development toolkit or backup and recovery are used)   Example : ....\oracle\Inventory\logs\*.log Control files   Control files have a *.ctl extension Example : ....\oracle\oradata\*.ctl YOU CAN GET THE PATHS FROM BELOW QUERIES : select name from v$datafile; select name from v$...

A general question among most of developers is  " HOW can I know when LAST DDL was done??" The best option will be to go and look at the table details.  While you can also query the same details from user_objects/dba_objects. Lets create a table test1 sql > create table test1 (id number); table TEST1 created. sql >   select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects where object_name='TEST1'; OBJECT_NAME            OBJECT_TYPE         CREATED           LAST_DDL         -------------------------------------------------    ---------------------     -------------- TEST1                              TABLE                     05-06-15 15:5...

A general question among most of developers is  " HOW can I know when LAST DDL was done??" The best option will be to go and look at the table details.  While you can also query the same details from user_objects/dba_objects. Lets create a table test1 sql > create table test1 (id number); table TEST1 created. sql >   select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects where object_name='TEST1'; OBJECT_NAME            OBJECT_TYPE         CREATED           LAST_DDL         -------------------------------------------------    ---------------------     -------------- TEST1                              TABLE                     05-06-15 15:5...