About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Tuesday, March 19, 2013

ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

Today I faced below mentioned Ora error when I tried to drop a user :-

SQL> drop user AMIT cascade;
drop user AMIT cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

There are queue tables present in the schema which needs to be dropped after that only we can drop the user.

Find out the queue tables in a schema :-

SQL> col OBJECT_NAME for a40

SQL> set lin 400
SQL> select object_name,object_type from dba_objects where owner='AMIT' and object_name like '%AQ%';

OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -------------------
DEF$_AQCALL                              TABLE
DEF$_AQERROR                             TABLE

SQL> conn AMIT/amit
Connected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'DEF$_AQCALL' , force=>TRUE);

PL/SQL procedure successfully completed.

SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'DEF$_AQERROR' , force=>TRUE);

PL/SQL procedure successfully completed.

SQL> select object_name,object_type from dba_objects where owner='THINCLIENT' and object_name like '%AQ%';

no rows selected

SQL> conn /as sysdba
Connected.
SQL> drop user AMITcascade;

User dropped.

I hope this article helped you.

Regards,
Amit Rath

Thursday, March 7, 2013

DBMS_SCHEDULER in Oracle Database

Scheduler in  Oracle Database is done by DBMS_SCHEDULER package. This package can be used to create different types of jobs :-

1. Jobs having inline schedule and inline Program.
2. Jobs having Program and Schedule already defined.
3. Jobs having Program already defined and inline schedule.
4. Jobs having inline Program and Schedule already defined.

1. Jobs having Program and Schedule already defined :-

Schedule for Job :-

BEGIN
-- Create 24x7 Every 1 mins..
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'Every_1_mins_24x7',     --######## Not more than 30 words########
start_date => trunc(sysdate)+ 0/24,
repeat_interval => 'freq=MINUTELY;interval=1',
end_date => NULL,
comments => 'Runtime: Every day after every 1 minutes');
END;

Program for Job :-

begin 
-- Call a procedure of a database package 
dbms_scheduler.create_program 
(program_name=> 'PROG_COUNT_NUM_OF_INACTIVE_SES',     --######## Not more than 30 words########
 program_type=> 'STORED_PROCEDURE', 
 program_action=> 'amit.count_inactive_sessions_prc', 
 enabled=>true, 
 comments=>'Procedure to count inactive sessions' 
 ); 
end; 

Job having schedule and Program already defined :-

begin 
-- Connect both dbms_scheduler parts by creating the final job 
dbms_scheduler.create_job 
 (job_name => 'JOB_COUNT_INACTIVE_SESIONS',      --######## Not more than 30 words########
  program_name=> 'PROG_COUNT_NUM_OF_INACTIVE_SES', 
  schedule_name=>'Every_1_mins_24x7', 
  enabled=>true, 
  auto_drop=>false, 
  comments=>'Job to count inactive sessions'); 
end;

2. Jobs having inline schedule and inline Program :-

BEGIN
  -- Job having inline schedule and inline program.
  DBMS_SCHEDULER.create_job (
    job_name        => 'JOB_COUNT_INACTIVE_SESIONS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN count_inactive_sessions_prc; END;',
    start_date      => trunc(sysdate)+ 0/24,           -- start_date => 'SYSTIMESTAMP'
    repeat_interval => 'freq=hourly; interval=1',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job having inline schedule and inline program.');
END;
/

3. Jobs having Program already defined and inline schedule

BEGIN
DBMS_SCHEDULER.create_job (
    job_name        => 'JOB_COUNT_INACTIVE_SESIONS',
    program_name    => 'PROG_COUNT_NUM_OF_INACTIVE_SES',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; interval=1',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined by Program already defined and inline schedule.');
END;
/

4. Jobs having inline Program and Schedule already defined.

BEGIN

DBMS_SCHEDULER.create_job (
     job_name      => 'JOB_GATHER_STAT_TABLE',
     schedule_name => 'Every_1_hour_24x7',
     job_type      => 'PLSQL_BLOCK',
     job_action    => 'BEGIN DBMS_STATS.gather_table_stats(''AMIT'',''COUNT_INACTIVE'',granularity=>''ALL''); END;',
     enabled       => TRUE,
     comments      => 'Job defined by inline Program and Schedule already defined.');
END;
/

How to find out details of a JOB which has been run :-

select * from user_scheduler_job_run_details where  JOB_NAME='JOB_GATHER_STAT_TABLE';

select * from user_scheduler_job_log where  JOB_NAME='JOB_GATHER_STAT_TABLE' order by log_date desc;

Different Types of Programs :-

1. Stored Procedure

begin 
-- Call a procedure of a database package 
dbms_scheduler.create_program 
(program_name=> 'PROG_COUNT_NUM_OF_INACTIVE_SES',     --######## Not more than 30 words########
 program_type=> 'STORED_PROCEDURE', 
 program_action=> 'amit.count_inactive_sessions_prc', 
 enabled=>true, 
 comments=>'Procedure to count inactive sessions' 
 ); 
end; 
/

2. PLSQL Block :-

begin 
-- Call a procedure of a database package 
dbms_scheduler.create_program 
(program_name=> 'PROG_COUNT_NUM_OF_INACTIVE_SES',     --######## Not more than 30 words########
 program_type=> 'PLSQL_BLOCK', 
 program_action=> 'BEGIN DBMS_STATS.gather_table_stats(''AMIT'',''COUNT_INACTIVE'',granularity=>''ALL''); END;', 
 enabled=>true, 
 comments=>'Procedure to count inactive sessions' 
 ); 
end; 
/

3. Shell Script

begin 
-- Executing a shell file
dbms_scheduler.create_program 
(program_name=> 'PROG_COUNT_NUM_OF_INACTIVE_SES',     --######## Not more than 30 words########
 program_type=> 'EXECUTABLE', 
 program_action=> '/u01/oracle/count_inactive.sh', 
 enabled=>true, 
 comments=>'shell script to count inactive sessions' 
 ); 
end; 
/

How to find out details of a Program :-

SELECT owner, program_name, enabled FROM dba_scheduler_programs;

How to Drop a Program/Job/Schedule :-

exec dbms_scheduler.drop_job('JOB_GATHER_STAT_TABLE', TRUE);
exec dbms_scheduler.drop_program('PROG_COUNT_NUM_OF_INACTIVE_SES', TRUE);
exec dbms_scheduler.drop_SCHEDULE('Every_1_hour_24x7', TRUE);

How to Restart a JOB :-

Begin 

dbms_scheduler.disable('JOB_GATHER_STAT_TABLE'); 
dbms_scheduler.enable('JOB_GATHER_STAT_TABLE'); 
end; 

How to run a JOB manually :-

begin 
dbms_scheduler.run_job('JOB_GATHER_STAT_TABLE',TRUE); 
end;

I hope this article helped you.

Regards,
Amit Rath

Tuesday, March 5, 2013

Renaming SYSTEM datafiles of Oracle Database

System tablespace is the most critical tablespace in Oracle database. To rename datafiles related to SYSTEM tablespace , we cannot proceed as we proceed for normal ones. It has to be handled with care.

When we try to offline your system tablespace we will get below mentioned error :-

SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

Renaming datafiles of SYSTEM tablespace:-

1. Shut down the database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down. 

2. Start the database in mount mode :-

SQL> startup mount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             226495364 bytes
Database Buffers          377487360 bytes
Redo Buffers                7135232 bytes
Database mounted.

3. Rename the system datafile of database:-
SQL> alter database rename  file 'C:\oracle\product\10.2.0\oradata\Amit\SYSTEM01.DBF' to 'C:\oracle\product\10.2.0\oradata\Amit\SYSTEM012.DBF' ;

Database altered.

4. Open the database :-
SQL> alter database open;

Database altered.

I hope this article helped you.

Regards,
Amit Rath

CREATE or ALTER a Tablespace

A tablespaces is made up of one or more database datafiles. Tablespaces are the logical components of a database , datafiles which it's made of are physical componets of database.

To alter a tablespace attributes there are some prerequesites :-

We need to have ALTER TABLESPACE system privilege to alter a tablespace. Having ALTER TABLESPACE system privilege we can perform any operation regarding tablespace, but if we have MANAGE TABLESPACE system privilege then we can only perform ONLINE/OFFLINE a tablespace, BEGIN/END a backup of tablespace, Make the tablespace readonly/readwrite.

To alter SYSAUX tablespace we need SYSDBA privilege.

How to create a tablespace :-

SQL> create tablespace test datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test01.dbf' size 100m;

SQL> create tablespace test datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test02.dbf' size 100m autoextend on;

How to rename a tablespace :-

SQL>  alter tablespace test1 rename to test2;

Renaming a datafile of a tablespace :-

To rename a datafile of a tablespace , we have to do that in three steps :-

1. Make the Tablespace Offline :- we cannot make a system tablespace offline, if necessary we have to shut down the database.

SQL> alter tablespace test offline normal;

2. Rename the datafile:-

SQL> alter tablespace test rename datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test01.dbf' to 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test05.dbf'; 

3. Make the tablespace online :-

SQL> alter tablespace test online;

How to add a datafile to a Tablespace :-

SQL> alter tablespace test2 add datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test03.dbf' size 100m;

How to drop a datafile of a tablespace :-

Datafile which you wants to drops has to be empty means no extent allocated to it.

SQL> alter tablespace test2 drop datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test03.dbf';

SQL> alter database tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\TEMP01.DBF' drop including datafiles;

How to resize a datafile of a tablespace :-

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test02.dbf' resize 200m;

Details of tablespaces :-

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 from dba_segments group by tablespace_name;
                                                        OR

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 from dba_data_files group by tablespace_name;
                                                         OR 

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 from dba_free_space group by tablespace_name;
                                                          OR

SQL> select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used", round((fs.freespace/t.totalspace)*100,2) as "% Free" from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t, (select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;

Autoextend Parameter :-

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\TEST01.DBF' autoextend OFF;

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\TEST01.DBF' autoextend ON;

Logging Attribute :-

SQL> Alter tablespace TEST1 logging;

SQL> Alter tablespace TEST1 nologging;

How to DROP a tablespace :-

SQL> drop tablespace test including contents and datafiles;

It will drop the tablespace with data and datafiles.

SQL> drop tablespace amit including contents;

It will drop only tablespace and data from database but not that physical file from OS. we can again reuse that file to create a tablespace :-

SQL> create tablespace amit datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\amit01.dbf' size 1g reuse;

I hope this article helped you.

Regards,
Amit Rath

Monday, March 4, 2013

Moving LOB(CLOB or BLOB or LONG) columns to different tablespace

Move tables having column as CLOB datatype :-

SQL> desc table_clob;
 Name              Null?     Type
 -----------------   --------     ------------
 ID                                NUMBER(1)
 NAME                         VARCHAR2(2)
 DATA                          CLOB


SQL> select segment_name,tablespace_name from user_segments where segment_name='TABLE_CLOB';

SEGMENT_NAME                              TABLESPACE_NAME
-------------------------------------------------         ----------------------
TABLE_CLOB                                       AMIT

SQL> SELECT INDEX_NAME,TABLESPACE_NAME,status  FROM USER_indexes WHERE TABLE_NAME='TABLE_CLOB';

INDEX_NAME                               TABLESPACE_NAME                  STATUS
----------------------------------------                ------------------------------                   --------
SYS_IL0000053543C00003$$          AMIT                                            VALID
TEST_IDX                                     AMIT                                            VALID


SQL>  alter table TABLE_CLOB move tablespace system;

Table altered.


SQL> select segment_name,tablespace_name from user_segments where segment_name='TABLE_CLOB';

SEGMENT_NAME                                          TABLESPACE_NAME
---------------------------------                                    ------------------------------
TABLE_CLOB                                                     SYSTEM

SQL> SELECT INDEX_NAME,TABLESPACE_NAME,status  FROM USER_indexes WHERE TABLE_NAME='TABLE_CLOB';

INDEX_NAME                                   TABLESPACE_NAME                STATUS
------------------------------ -                    -----------------------------                          --------
SYS_IL0000053543C00003$$             AMIT                                            VALID
TEST_IDX                                         AMIT                                         UNUSABLE

Index associated to the table becomes unusable and column having clob datatype still remains in AMIT tablespace, it has to be moved manually.


SQL> ALTER TABLE TABLE_CLOB MOVE LOB(DATA) STORE AS (TABLESPACE SYSTEM);

Table altered.


SQL>  alter index TEST_IDX rebuild tablespace system;

Index altered.


SQL> SELECT INDEX_NAME,TABLESPACE_NAME,status  FROM USER_indexes WHERE TABLE_NAME='TABLE_CLOB';

INDEX_NAME                                TABLESPACE_NAME                STATUS
------------------------------                         ------------------------------                  --------
TEST_IDX                                       SYSTEM                                      VALID
SYS_IL0000053543C00003$$           SYSTEM                                     VALID

Move tables having column as BLOB datatype :-

SQL> desc test_blob
 Name              Null?    Type
 ----------------- --------   ------------
 ID                                NUMBER(12)
 NAME                        VARCHAR2(22)
 DATA                         BLOB


SQL> select segment_name,tablespace_name from user_segments where segment_name='TEST_BLOB';

SEGMENT_NAME                                        TABLESPACE_NAME
--------------------------------------                             ------------------
TEST_BLOB                                                  USERS


SQL> SELECT INDEX_NAME,TABLESPACE_NAME,status  FROM USER_indexes WHERE TABLE_NAME='TEST_BLOB';

INDEX_NAME                                         TABLESPACE_NAME                STATUS
------------------------------                              ------------------------------                          --------
SYS_IL0000053551C00003$$                    USERS                                         VALID
TEST_BLOB_IDX                                    USERS                                         VALID


SQL> alter table TEST_BLOB move tablespace amit;

Table altered.

SQL> select segment_name,tablespace_name from user_segments where segment_name='TEST_BLOB';

SEGMENT_NAME                                            TABLESPACE_NAME
----------------------------------------                              ------------------------------
TEST_BLOB                                                       AMIT

SQL>  SELECT INDEX_NAME,TABLESPACE_NAME,status  FROM USER_indexes WHERE TABLE_NAME='TEST_BLOB';

INDEX_NAME                                     TABLESPACE_NAME                STATUS
------------------------------                           ------------------------------                  --------
SYS_IL0000053551C00003$$                USERS                                      VALID
TEST_BLOB_IDX                                USERS                                         UNUSABLE

Index associated to the table becomes unusable and column having clob datatype still remains in AMIT tablespace, it has to be moved manually.

SQL> alter index TEST_BLOB_IDX rebuild tablespace AMIT;

Index altered.

SQL> ALTER TABLE TEST_BLOB MOVE LOB(DATA) STORE AS (TABLESPACE amit);

Table altered.

SQL> SELECT INDEX_NAME,TABLESPACE_NAME,status  FROM USER_indexes WHERE TABLE_NAME='TEST_BLOB';

INDEX_NAME                                       TABLESPACE_NAME                STATUS
------------------------------                             ------------------------------                  --------
TEST_BLOB_IDX                                 AMIT                                            VALID
SYS_IL0000053551C00003$$                AMIT                                             VALID

Move columns having column as LONG datatype :-

Long Datatype becomes depricated datatype in Oracle latest releases as with Long as datatype there are too many restrictions. If you have a column in your table as LONG datatype and you want to move the table to another tablespaces, then through move you cannot do that , you will below mentioned error:-

SQL> alter table TEST_LONG move tablespace users;
alter table TEST_LONG move tablespace users
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

Solution:-

1. Export the table having column as LONG datatype using datapump.
2. drop the table.
3. Import the table again with remap_tablespace parameter using impdp.

I hope this article helped you.

Regards,
Amit Rath

How to move partitioned tables to different tablespace

Yesterday I was trying to move a partitioned table to different tablespace using move command I got the following error:-

SQL> alter table partition move tablespace users parallel 10;
alter table partition move tablespace users parallel 10
            *
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object


Default tablespace of partitioned table :-

SQL> select TABLE_NAME,PARTITIONING_TYPE,DEF_TABLESPACE_NAME from dba_part_tables where table_name='PARTITION';

TABLE_NAME                     PARTITIONING_TYPE                 DEF_TABLESPACE_NAME
----------------------------------------------------------------------------------------------------------------
PARTITION                          LIST                                               SYSTEM

Changing the default tablespace of Partition table. Now new partitions will be created in the new default tablespace, but old partitions will remain in the old default tablespace :-


SQL> alter table partition modify default attributes tablespace users;

Table altered.

SQL> select TABLE_NAME,PARTITIONING_TYPE,DEF_TABLESPACE_NAME from dba_part_tables where table_name='PARTITION';

TABLE_NAME                     PARTITIONING_TYPE                             DEF_TABLESPACE_NAME
----------------------------------------------------------------------------------------------------------------
PARTITION                          LIST                                               USERS

SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='PARTITION';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
----------------------------------------------------------------------------------------------------------------------------
PARTITION                          PAR1                                       SYSTEM
PARTITION                          PAR2                                       SYSTEM

SQL> SELECT * FROM PARTITION;

        ID NAME
---------- ---------------------
         1 d
         3 f
         7 y
         8 t

Analyzing above select statements, table partition have 4 records but records won't reflect in the NUM_ROWS column of USER_TAB_PARTITIONS  view. We need to gather the stats of Table "PARTITION" to reflect the records in NUM_ROWS column.


SQL> SHOW USER
USER is "SYS"
SQL> EXEC DBMS_STATS.gather_table_stats('SYS', 'PARTITION', granularity=>'ALL');

PL/SQL procedure successfully completed.


SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='PARTITION'

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
-------------------------------------------------------------------------------------------------------------------------
PARTITION                         PAR1                                         SYSTEM                                  2
PARTITION                         PAR2                                         SYSTEM                                  2

Moving OLD partitions to different tablespace :-


SQL> select 'alter table ' || table_name || ' move partition ' || partition_name|| ' tablespace users parallel 10;' "PARTITION_MOVE_SCRIPT" from user_tab_partitions where table_name='PARTITION';

PARTITION_MOVE_SCRIPT
----------------------------------------------------------------------------------------------------------------------
alter table PARTITION move partition PAR1 tablespace users parallel 10;
alter table PARTITION move partition PAR2 tablespace users parallel 10;

After moving a table or partitioned table to different tablespace , indexes associated to the tablespace become unusable. We need to rebuild the associated indexes to make them usable.

Status of Indexes before moving a table :-


SQL>  select index_name,PARTITION_NAME,status from user_IND_PARTITIONS where TABLESPACE_NAME='SYSTEM';

INDEX_NAME                     PARTITION_NAME                 STATUS
----------------------------------------------------------------------------------------
PAR_IDX                             PAR1                                        USABLE
PAR_IDX                            PAR2                                         USABLE

 SQL> alter table PARTITION move partition PAR1 tablespace users parallel 10;

Table altered.

SQL> alter table PARTITION move partition PAR2 tablespace users parallel 10;

Table altered.

SQL>
SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='PARTITION';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
--------------------------------------------------------------------------------------------------------------------------
PARTITION                          PAR1                                        USERS                                   2
PARTITION                          PAR2                                        USERS                                   2

SQL> select index_name,PARTITION_NAME,status from user_IND_PARTITIONS where TABLESPACE_NAME='SYSTEM';

INDEX_NAME                     PARTITION_NAME                 STATUS
-----------------------------------------------------------------------------------------
PAR_IDX                              PAR1                                       UNUSABLE
PAR_IDX                              PAR2                                       UNUSABLE

It seems that the indexes becomes unusable. We need to rebuild the indexes to make them usable.

SQL> select 'alter index ' || a.index_name ||  ' rebuild partition ' || a.PARTITION_NAME || ' tablespace USERS parallel 10;' from user_ind_partitions a, user_tab_partitions b where a.partition_name=b.partition_name and b.table_name='PARTITION';


'ALTERINDEX'||A.INDEX_NAME||'REBUILDPARTITION'||A.PARTITION_NAME||'TABLESPACEUSERSPARALLEL10;'
-------------------------------------------------------------------------------------------------------------------------
alter index PAR_IDX rebuild partition PAR1 tablespace USERS parallel 10;
alter index PAR_IDX rebuild partition PAR2 tablespace USERS parallel 10;

SQL> select index_name,PARTITION_NAME,status from user_IND_PARTITIONS where TABLESPACE_NAME='USERS';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------------------------------------------------------------------
PAR_IDX                              PAR1                                        USABLE
PAR_IDX                              PAR2                                        USABLE

This way we can move a partitioned table having n number of partitions to different tablespace.

I hope this article helped you.

Regards,
Amit Rath