Available Balance
Oracle Tech Talk: Moving Oracle Datafiles from one location to another
Oracle Database StatisticsHow to gather oracle database statistics?

Need for moving Oracle Datafiles

Sometimes, when managing Oracle Database, we fall short of disk space on one or more of our disks. Often, approvals and finance comes in the way of fast acquisition or sanction of more disk space. There could be budget constraints or client billing issues etc. Moreover, such disks have one or more Oracle Datafiles(for layman: Files storing actual RDBMS data on disk; internally in form of fixed or variable format plain text files); which are growing at a good rate.
So a solution could be to relocate the files to a new location where more disk space is available.

Often this could be something which might requyire hours of discussion. Yet we have a simpler solution at hand.

Moving Datafiles manually in OFFLINE NORMAL Mode

We can do the above required task- i.e moving Oracle Datafiles from one disk , or disk system(may be racks) to another where more space is available by the below methodology:

1)Put the tablespace which has these datafiles to OFFLINE NORMAL mode:

ALTER TABLESPACE example01 OFFLINE NORMAL;

Here the Tablespace example01 has the desired Datafiles.

2)Now, we need to physically move the physical files say from D drive folder ora11g to E drive folder ora11g_new

3)Then, we use the ALTER TABLESPACE .. RENAME DATAFILE .. command to rename the datafile(s) :

— Rename datafile Db command

ALTER TABLESPACE example01
RENAME DATAFILE ‘D:\ora11g\oradata\inventorydb\example01.dbf’
TO ‘E:\ora11g_new\oradata\inventorydb\example01.dbf’ ;

The above steps makes sure that the Oracle Database system registers the altered location information of the file(s) of the tablespace and works without any further interruption.

4)Next we run the following :

ALTER TABLESPACE example01 ONLINE;

The last step makes sure that the tablespace is fully available to the users and they can work uninterruptedly.

The above does leads to some downtime, but it is much less than the time it could take to do a fully offline operation.

Source: Oracle Base site:
https://oracle-base.com/articles/misc/renaming-or-moving-oracle-files

and own hands-on

CLASS12_4 Gather Database Optimizer statistics using DBMS_STATS

 

Previously we have a discussion that why we need to collect optimizer statistics? How this work with our database to maintain best query execution plan?  We have learned that how can we collect or update database statistics using GUI interface (enterprise manager).

 

Now we are going to collect Oracle Database statistics using Package DBMS_STATS. 

 

dbms_stats.gather_table_stats

dbms_stats.gather_table_stats

 

In the above examples, I have gathered database statistics (GATHER_TABLE_STATS). As you can easily distinguish that first I’ve run following select query to extract info regarding NUM_ROWS & LAST_ANALYZED for TABLE_NAME ‘DEPT2’.

 

SELECT OWNER, TABLE_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME =’DEPT2’;

 

This gave the output of ‘NUM_ROWS’ and ‘LAST_ANALYZED’ columns as null. It means information is not updated, or stats are not gathered. Then I used the package DBMS_STATS in order to get manual statistics

 
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (OWNNAME =>’SYS’, TABNAME => ‘DEPT2’);
END;
 
DBMS_STATS Package has run successfully and then I run the first query again to check whether optimized statistics have updated or not. Hence, you see NUM_ROWS containing the same values which are contained by the dept2 table and last_analyzed date has been updated as well.Similarly, we can gather schema stats as well. Not even schema but we can also get following object statistics./*

TABLE, INDEX, SCHEMA, DATABASE, DATA DICTIONARY, FIXED TABLE

*/

Example:

BEGIN DBMS_STATS.gather_schema_stats(ownname => ‘SCOTT’

, options => ‘GATHER’);

Options can be equals to = (GATHER

GATHER AUTO

GATHER EMPTY

GATHER STALE)

 

GATHER = Gather all statistics from this schema,

Gather Auto = Oracle database automatically decides itself whether statistics are updated or not?

Gather Empty = collect statistics from those tables from  where statistics were not taken before I.e.  Table’s last_analyzed column is null.

Gather Stale =Gather statistics from those tables whose information made before but are not up to date.

 

CLASS12_3 Gather Oracle Database Statistics

In this lesson we will be discussing Database Statistics, why do we need to update or gather database Statistics?

Oracle Database Statistics

How to gather oracle database statistics?

Why it is necessary for database precisely for Oracle?

Let’s Dive into some of oracle’s basic concept behind the Database optimizer Statistics.  As we know, Oracle creates an execution plan before running any SQL query. It means that how oracle executes this query in a way that will reduce the CPU usage, memory usage and extract results rapidly. For efficient query execution plan, Oracle needs some estimation in order to determine.

Gather Oracle Database Statistics;

As we know that whenever we create a table all information of the table is available in DBA_Tables automatically. However, when we insert data into this particular table then the information is not quickly available to DBA_TABLES. Let’s make this concept more concrete by completing the following examples.

  • I’ve created the table Called ‘DEPT2’ using following query.

CREATE TABLE DEPT2

(DEPTNO NUMBER(7) PRIMARY KEY,

DNAME VARCHAR2(27),

LOC VARCHAR2(5));

Create table statement in oracle

Creating table in oracle database.

  • Information of this table can be seen in DBA_TABLES.

o   select owner, table_name, num_rows from dba_tables

                        Where TABLE_NAME =’DEPT2′;

o   As you can see Num_ROWS Column contains a null value which means, table does not have any data or row available.

select num_rows from dba_tables

select num_rows from dba_tables

  • Now I am going to insert some data into this newly created table.
oracle database statistics demo

oracle database statistics demo

  • By performing the above activity, we have  observed that NUM_ROWS column is not updating rapidly. Although we have inserted the data into DEPT2 tables that can be seen but Information of this data is not available in DBA_TABLES.
  • Hence, we’ve come to the conclusion that this information is static which we need to be automatic by using any tool. So, the question is what that mechanism we have for this?
  • So, the answer is that Oracle has tool called ‘Managed optimizer Statistics.’
  • There is an Oracle Managed maintenance Job, Who usually runs in the night and gather information regarding all statistics and update those into DBA_TABLES. Remember if your system or server is not 24/7 machine then most probably this job will not run on a regular basis that will lead the performance issues and static information in DBA_TABLES.
  • If you are not in 27/7 environment, then you have to take manual stats or change the timing of this job.

We can gather optimizer statistics via enterprise manager. Log on to the EM and go to Server > Tab Click Manage Optimizer Statistics under Query optimizer.

Manage Optimizer Statistics In Oracle 11g

Manage Optimizer Statistics In Oracle 11g

After clicking Manage Optimizer Statistics, you will be redirected to the new page. Where you need to select operations, we have selected Gather Optimizer Statistics.

Gather Optimizer Statistics in Oracle11g

Gather Optimizer Statistics in Oracle11g

Gather Optimizer Statistics link redirect us into following new page. Where you are ask to select object type from which you can collect stats.

Gather Optimizer Statistics wizard in oracle11g

Gather Optimizer Statistics wizard in oracle11g

As you can see above Information that indicates,’ For 11, Oracle recommends you enable automated maintenance task (Optimizer Statistics Gathering) to generate optimizer statistics regularly within maintenance windows. This wizard should only be used for cases where the task is inappropriate or disabled. For example, you may want to gather optimizer statistics immediately, or the task failed to execute within a maintenance window, or you want to customize options to gather optimizer statistics.’

Gone through all five steps, and you will be able to pick your required status.

Gather table optimizer statistics in oracle11g

Gather table optimizer statistics in oracle11g

Click on Add button and you will get this.

select table from oracle optimizer statistics

select table from oracle optimizer statistics

Click ok to move next.

This table contains the table objects for which optimizer statistics will be gathered. Click Add to add table objects to the table.

Gather optimizer statistics objects oracle11g

Gather optimizer statistics objects oracle11g

Gather optimizer statistics Schedule

Gather optimizer statistics Schedule

Choose Job Name, Description, Select Immediately and press next.

Gather optimizer Statistics Review oracle11g

Gather optimizer Statistics Review oracle11g

Job has been completed successfully now click on submit button. This will run immediately as of now and will run when it was scheduled. This activity will update all the latest information in NUM_ROW column in DBA_TABLES.  We have covered how to gather db table states using GUI.

select owner table_name num_rows last_analyzed from dba_tables

select owner table_name num_rows last_analyzed from dba_tables

This was the GUI process to gather Optimizer statistics we can also gather stats from scripting as well. If I am going to start scripting in this post then, this post will become much longer. So, I have created another post for this purpose.You can get detailed information about Oracle Cost-Based Optimizer (CBO) and Database Statistics as well.

 

Class 12_2 Oracle 11g: Alter Table Read Only
Alter Table READ-ONLY

In today’s discussion, we will talk about the following.

  • Alter table read only.Alter Table READ-ONLY

 

What is read only? Why we need to alter the table into read-only? These are some important question that comes into one’s mind.

As you know, you can alter the table as well as you can add, delete, and modify columns and their data types. However, today I’ll tell you that you can also convert the table in read-only mode. This read-only status of the table will disable the box (Object) from any modification of any record that is already available in the table.

Why we need to change table read only?

We usually do this cause we do not allow anyone to modify the information that we already have. Generally we do this for reporting tables.

Let’s make it more concrete by completing an example.

Examples:

ALTER TABLE SCOTT.EMP READ ONLY;
SELECT * FROM SCOTT.EMP;
UPDATE SCOTT.EMP SET SAL=1000 WHERE EMPNO=7369;
ALTER TABLE SCOTT.EMP READ WRITE;

First pick the table that you want to convert it into read-only. In our case we have chosen SCOTT.EMP3

ALTER TABLE SCOTT.EMP3 READ ONLY;

Note: Scott.emp3 is the table name you can replace with your table name.

Alter the table into read-only:

Alter the table into read-only:

Update Operation not allowed on table

Trying to delete the data from read-only table (SCOTT.EMP3) and getting following Error.

ERROR at line 1:

ORA-12081: update operation not allowed on table “SCOTT”.”EMP3″

Update read-only table in oracle

We are trying to update the read-only table and getting following error which means that this table is read-only.

ERROR at line 1:

ORA-12081: update operation not allowed on table “SCOTT”.”EMP3″

In order to resolve this error or back table into read and write mode, you need to run the following query if you have privileges otherwise contact you DBA.

ALTER TABLE SCOTT.EMP3 READ WRITE;

Note: Scott.emp3 is a table name you can replace with your table name.

Alter table into read-write in oracle

alter table table_name read write ;

I hope today we have covered this Read only feature.

Class 12_1 Get Table List From Oracle Schema
Get_oracle_table_list_from_Database_dictionary

Today we are going to learn some of the following features.

Get List From DBA_TABLES

Extracts Or Get List of Tables From Oracle Schema.

  • List down or Get the schema tables.

 

Let say you are a DBA, and you wanted to check that how many tables were created by HR Schema or User. You can check this by following two ways.

  • You can connect with HR Schema by providing HR User and Password in our case
    1. Conn HR/HR OR  Conn [email protected]
    2. Then query the TAB table using following statement. (Select * from tab;).
  • You can connect to “Sys” user using Sysdba privileges.
    1. Conn sys/password as sysdba, Or, Conn sys/password as [email protected]
    2. Then you can run following query
      1. select * from dba_tables where owner=’HR’;

Using above mentioned method, you can list down the tables of HR Schema. You can use this query to extract any schema and their tables.

Examples:

---LIST TABLES OF HR USER/SCHEMA
SELECT * FROM DBA_TABLES X WHERE X.OWNER='SH';
SELECT * FROM DBA_TABLES X WHERE X.OWNER='SCOTT';

 

SELECT * FROM DBA_TABLES

The above mentioned screen of terminal / Sqlplus tells you how to check the tables that were created by HR schema.

Select_from_dba_tables

Selecting dba_tables from oracle Directory via SQL Developer.

In this second view, you can check all columns of DBA_TABLES, the above picture has taken from Oracle SQL Developer.  However, you can see in this snap that we have used the same query as mentioned above for simple sqlplus but the only difference here is that here we are querying all columns of DBA_TABLES.

Have a look thoroughly on the TABLESPACE_NAME column, where one value appears null against COUNTRIES table_name. The first question that often arise in our mind that why Tablespace_name contains null values against any table? Does Countries table not have any Tablespace?  However, don’t worry it simply means this Countries tables contained by temporary tablespace.

I hope this will make you understand that how you can list down the table of any schema, and you can query the DBA_TABLE for further information regarding any table and its info.