Create Partitioned Table Using CTAS

Leave here an example of how to create a partitioned table from a non partitioned table ( in this case with organization head). Remember that CTAS copy the NULL or NOT NULL constraint, but not as DEFAULT VALUES conditions.

 

 CREATE TABLE SIEBEL.USERS_PARTITIONATED
PARTITION BY RANGE (F_ALTA)
(
   PARTITION P1 VALUES LESS THAN (TO_DATE('01-01-2013','DD-MM-YYYY')),
   PARTITION P2 VALUES LESS THAN (TO_DATE('01-01-2014','DD-MM-YYYY')),
   PARTITION P3 VALUES LESS THAN (TO_DATE('01-01-2015','DD-MM-YYYY'))
)
TABLESPACE WORK
AS SELECT * 
FROM  SIEBEL.USERS
/

 

 

HTH – Antonio NAVARRO

 

Advertisements

ORA-20011 And ORA-29400 Erros executing GATHER_STATS_JOB

Last night the job of statistics has failed. The error is as follows;

 

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /sibdos/prod/trace/diag/rdbms/sibdos/SIBDOS/trace/sibdos_j000_16132.trc:
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file /oracle_indexes/sibdos/trace/bdump/ALERT_LOG_16132.log

 

After researching the topic I see the problem is that there is due to a external table created. in this case the database was migrated a couple of days ago. External table is in a location (path) that no longer exists. The problem is solved by deleting the external table. The following query shows how view external tables we have created.

 

 SELECT OWNER, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
FROM DBA_EXTERNAL_TABLAS
ORDER BY 1,2
/ 

 

HTH – Antonio NAVARRO

 

Can’t Open Your Crontab File

When trying to create a scheduled job gives me the following error;

sybase12@pwc/tmp#crontab -l
crontab: can’t open your crontab file.

 

Reviewing /etc/cron.d/cron.deny file, not appear that my user is denied;

sybase12@pwc/etc/cron.d#cat cron.deny
daemon
bin
nuucp
listen
nobody
noaccess

 

Looking for that there is a cron file, no file associated for the sybase12 user;

sybase12@pwc/etc/cron.d#ls -lrt /usr/spool/cron/crontabs/
total 11
-rw——- 1 root sys 308 ago 6 2010 sys
-rw——- 1 root sys 190 ago 6 2010 adm
-r——– 1 root root 452 ago 6 2010 lp
-r——– 1 root sys 404 sep 29 12:55 uucp
-rw——- 1 root root 742 nov 14 11:02 root

 

 
From Doc;

 
If you are creating or editing a crontab file that belongs to root or another user you must become root.

You do not need to become root to edit your own crontab file.

Create a new crontab file, or edit an existing file.

crontab -e [username]

where username specifies the name of the user’s account for which you want to create or edit a crontab file. You can create your own crontab file without superuser privileges, but you must have superuser privileges to creating or edit a crontab file for root or another user.

 

HTH – Antonio NAVARRO

How To Check Rman Syntax

In the following example we will see how to check the syntax of an Rman script. The example is correct so must not return any error.

 racsolnode-2@:/orarac11/anr> rman checksyntax @todo

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Nov 12 19:04:43 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target *
2> connect rcvcat *
3>
4>
5> run {
6>
7>
8>   sql 'alter system checkpoint';
9>
10>   sql 'alter system switch logfile';
11>
12>   shutdown abort;
13>
14>   startup;
15>
16>   shutdown immediate;
17>
18>   startup mount;
19>
20>   resync catalog;
21>
22>          allocate channel t1 type 'SBT_TAPE'
23>          parms 'ENV=(NSR_DATA_VOLUME_POOL=Historic,
24>                                      NSR_CLIENT=racsol.windsort.com,
25>                                      NSR_SERVER=serverbck.yellow.int)';
26>          allocate channel t2 type 'SBT_TAPE'
27>          parms 'ENV=(NSR_DATA_VOLUME_POOL=Historic,
28>                                      NSR_CLIENT=racsol.windsort.com,
29>                                      NSR_SERVER=serverbck.yellow.int)';
30>          allocate channel t3 type 'SBT_TAPE'
31>          parms 'ENV=(NSR_DATA_VOLUME_POOL=Historic,
32>                                      NSR_CLIENT=racsol.windsort.com,
33>                                      NSR_SERVER=serverbck.yellow.int)';
34>          allocate channel t4 type 'SBT_TAPE'
35>          parms 'ENV=(NSR_DATA_VOLUME_POOL=Historic,
36>                                      NSR_CLIENT=racsol.windsort.com,
37>                                      NSR_SERVER=serverbck.yellow.int)';
38>
39>          backup as backupset database format 'dbracsol_%U' TAG=BCK_END_RACSOL keep forever;
40>          backup current controlfile format 'ctracsol_%U' TAG=BCK_END_RACSOL keep forever;
41>
42>
43>
44>          release channel t1;
45>          release channel t2;
46>          release channel t3;
47>          release channel t4;
48>
49>     }
50>
51>
The cmdfile has no syntax errors

Recovery Manager complete.

 
It will return errors like below if the script have any error. We change the line “release channel t2” to “channel t2 relase”. It need a “e” character.

 

 34>          release channel t1;
35>          relase
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "advise, allocate, alter, backup, @, catalog, change, configure, convert, copy, crosscheck, delete, duplicate, execute, flashback, host, mount, open, recover, release, repair, report, restore, resync, send, set, show, shutdown, sql, startup, switch, transport, validate, }"
RMAN-01008: the bad identifier was: relase
RMAN-01007: at line 35 column 10 file: todo

 
HTH – Antonio NAVARRO

 

How To Know If Partition Feature Is Installed

In Oracle to see if this partitioning option is installed you must run the following query, but returns zero rows is not installed.

select * from v$option where parameter = ‘Partitioning’ and value =’TRUE’;

 
Apart ought to be in a version Enterprise, this can be seen with this sentence;

select * from v$version where rownum <= 1 and banner like ‘%Enterprise%’;

 
If the above queries returns rows, a final test is to create a table with partitioning, as the following example;

 

 CREATE TABLE TAB1
(
 col1     NOT NULL,
 col2     DATE NOT NULL
)
PARTITION BY RANGE (col2)
(
 PARTITION quarter_1 VALUES LESS THAN (TO_DATE('01/04/2014', 'DD/MM/YYYY')) TABLESPACE work,
 PARTITION quarter_2 VALUES LESS THAN (TO_DATE('01/07/2014', 'DD/MM/YYYY')) TABLESPACE work,
 PARTITION quarter_3 VALUES LESS THAN (TO_DATE('01/09/2014', 'DD/MM/YYYY')) TABLESPACE work,
 PARTITION quarter_4 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')) TABLESPACE work
)
/

 

If you have not installed this feature, when creating the table you will return the following error;

ERROR at line 1:
ORA-00439: feature not enabled: Partitioning

 

HTH – Antonio NAVARRO

 

ORA-15032 And ORA-15027 Executing Dismount

Putting a diskgroup offline gives the following errors.

 SQL> alter diskgroup ocrmus_dg dismount
  2  ;
alter diskgroup ocrmus_dg dismount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "OCRMUS_DG" precludes its dismount

 

We look to see if there is something that are using this diskgroup and in this case there is nothing like showing the lsof command (from the prompt ASMCMD). Also you can see from the ASM instance perform query from v$asm_client view;

C:\app\11.2.0\grid\BIN>asmcmd -p
ASMCMD [+] > lsof
DB_Name Instance_Name Path
ASMCMD [+] >

 

The problem is the spfile of the ASM instance, the spfile reside in this diskgroup, also in this case is where the OCR (Oracle Cluster Repository) are;

 SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +OCRMUS_DG/muswin/asmparam
                                                 eterfile/registry.253.86276725
                                                 7

 

HTH – Antonio NAVARRO

 

How To Get A Job DDL

Let’s see an example of how to get the Data Definition Language (DDL)  or create job script (with Oracle Scheduler) from a one already existing.

Note; DDL to get a job scheduler, pass as the first parameter the string “PROCOBJ”.

 

 SBL*/\/\/> SELECT DBMS_METADATA.GET_DDL ('PROCOBJ','LOCKSTATS_2624674') FROM DUAL;

DBMS_METADATA.GET_DDL('PROCOBJ','LOCKSTATS_2624674')
________________________________________________________________________________

BEGIN
dbms_scheduler.create_job('"LOCKSTATS_2624674"',
job_type=>'PLSQL_BLOCK', job_action=>
'
begin
dbms_stats.lock_table_stats(ownname=> ''SIEBEL'', tabname=> ''S_ACT_EMP'' );
dbms_stats.lock_table_stats(ownname=> ''SIEBEL'', tabname=> ''S_EVT_MAIL'' );
end;'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('20-JUN-2012 08.00.35,983830000 PM UTC','DD-MON-RRRR
HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
NULL
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>FALSE,comments=>
NULL
);
COMMIT;
END;

 

HTH – Antonio NAVARRO