DBA Blogs

Sending Mail Through Oracle Which Has HTML Table Content Or Sending mail Through Attachment Using Oracle

Tom Kyte - Tue, 2017-01-17 23:46
<code>Team, Good Monday Morning !!!!!! we are facing difficulty in sending mail through oracle code, for the data being retrieved from one or more data base tables .(Issue arises when the data is huge) The records fetched from the query sha...
Categories: DBA Blogs

SQL loader to load data from excel spreadsheet

Tom Kyte - Tue, 2017-01-17 23:46
Hi Team, The scenario is. we have saved an excel file in a folder. how can i load the data from excel sheet to oracle table. i am using oracle 11g. how can we approach this using SQL loader. please help us guys please post if any lin...
Categories: DBA Blogs

Run bash scripts from application server for DBA tasks on DB server

Tom Kyte - Tue, 2017-01-17 23:46
Hello Tom, We do not want to DBA enterprise manager and instead want to create a bash scripts to perform DBA tasks. The scripts will be residing on another server and we have a separate DB server. Can we perform below DBA tasks from remote se...
Categories: DBA Blogs

Insert statements

Tom Kyte - Tue, 2017-01-17 23:46
Hi, An Application has been sending insert statements to DB continuously from past two days. I would like to know the time gap between the insert statements. Thanks & Regards Krishna.
Categories: DBA Blogs

oracle data block corrupted(file # 4,block # 3676)

Tom Kyte - Tue, 2017-01-17 05:26
[REP_12014] An occur occurred while accessing the repository ORA-01578:ORACLE data block corrupted(file # 4, block # 3676) ORA-01110:data file:'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF' Database driver error... Function Name:Execute S...
Categories: DBA Blogs

to_char manipulates DST times ?

Tom Kyte - Tue, 2017-01-17 05:26
Hello Tom, I have one column INTERVAL_TIME with datatype as TIMESTAMP(0) and stores data in 15 mins interval time. Since there was DST on 13 March 2016 , the interval was store at 1:45 AM and next interval at 3:00 AM. But when i am applying to...
Categories: DBA Blogs

ORA-01460: unimplemented or unreasonable conversion requested

Tom Kyte - Tue, 2017-01-17 05:26
Hi, We are running PeopleSoft and ocassionally we get error: ORA-01460: unimplemented or unreasonable conversion requested. There is nothing in the alert.log file and we can move past the error by just restarting the failed process without making...
Categories: DBA Blogs

How do I use UTL_SMTP to automatically send an email when a database table column value change?

Tom Kyte - Tue, 2017-01-17 05:26
How do I use UTL_SMTP to send an automated email after my database table (A.K.A. user_objects) column (A.K.A status) value change from "Valid" to "Invalid"? --Example: I would like an automated email sent when the query listed below has an invalid...
Categories: DBA Blogs

Change all procedures in a schema

Tom Kyte - Mon, 2017-01-16 11:06
Is it possible to change a string to another string in all procedures in a schema? Use case: We have a function, say F_A. We want to place it within a package, say PG_B. Now we would like to change all calls to F_A in all procedures with PG_B.F...
Categories: DBA Blogs

Privilege required to execute a stored procedure

Tom Kyte - Mon, 2017-01-16 11:06
Hi, There is a procedure named 'Update_emp_details' in our database which actually update the employees table present in the same database. I needs to give execute permission for a another user to this procedure 'Update_emp_details'. Is giving...
Categories: DBA Blogs

Locking issues on Table A with FKs on table X while a procedure inserts records in table B with FKs on table X too

Tom Kyte - Mon, 2017-01-16 11:06
Hello, we use oracle 11.2.0.3 and following topic i would like to discuss here: Setup like this: Table A (partitioned) with an article column and FK defined to masterdata (article) keytable X Table B also with article column and FK define...
Categories: DBA Blogs

Spool file hide SQL statement

Tom Kyte - Mon, 2017-01-16 11:06
I would like to know what Statement makes it, that the the SQL Statement is not printet in the spool file? The column names still should be printed. I'm calling the script from the SQL developer. I have the following script: <code>set CO...
Categories: DBA Blogs

Unused indexes Identification

Tom Kyte - Mon, 2017-01-16 11:06
Hello Tom, i was looking for options available in oracle 12c by which i can find out Unused indexes in database. I have used v$object_usage. but it only gives me index is used? it does not display how many times it has been used. also, if i would ...
Categories: DBA Blogs

Grant Compile Packaged to Other User

Tom Kyte - Mon, 2017-01-16 11:06
<b>Hi, I have a package in schema A. I want schema B to be able to compile the code for just this package and not any other packages that schema A owns. Can this be achieved? </b>
Categories: DBA Blogs

Self full outer join with additional predicateds

Tom Kyte - Mon, 2017-01-16 11:06
Hi Tom, I was asked to create a query, counting the number of the columns with different names in 2 tables. Say in "Employees" and "Departments" we have DEPARTMENT_ID and MANAGER_ID columns with equal names, and the rest of the columns differs in na...
Categories: DBA Blogs

rouning inconsistency in oracle db

Tom Kyte - Mon, 2017-01-16 11:06
Hi Tom, We use the below rounding logic in our applications but unfortunately the first query was rounding incorrectly. This we can see only when this code was executed from the application. But when I execute it in sql*plus it giving me correct o...
Categories: DBA Blogs

Help Me Help You!

VitalSoftTech - Mon, 2017-01-16 09:45
Hello everyone, I hope the holidays went well for you, best of wishes from my family to yours! It is a new year and we all have new year resolutions! After years of creating blogs for you all, I have decided to turn it up a notch! As many of you already know, I am […]
Categories: DBA Blogs

Links for 2017-01-15 [del.icio.us]

Categories: DBA Blogs

12cR1 RAC Posts -- 3 : Convert PolicyManaged DB back to AdminManaged

Hemant K Chitale - Sun, 2017-01-15 20:35
In the previous post on 12cR1 RAC, I had converted my AdminManaged Database to PolicyManaged.

Here, I convert it back to AdminManaged.

First, I verify that the database is shutdown (note that I have only 1 node of the cluster currently up and running, I don't need both nodes and instances up).

[oracle@collabn1 ~]$ srvctl status database -d RAC
Instance RAC_1 is running on node collabn1
[oracle@collabn1 ~]$ srvctl stop database -d RAC
[oracle@collabn1 ~]$ ps -fuoracle |grep smon
oracle 3422 1 0 09:49 ? 00:00:00 asm_smon_+ASM1
oracle 4882 1 0 09:50 ? 00:00:00 mdb_smon_-MGMTDB
oracle 16889 9821 0 10:08 pts/0 00:00:00 grep smon
[oracle@collabn1 ~]$


Next, I remove the database from the Cluster Registry.

[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file:
Domain: racattack
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: MyPool
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is policy managed
[oracle@collabn1 ~]$ srvctl remove database -d RAC
Remove the database RAC? (y/[n]) y
[oracle@collabn1 ~]$ srvctl config database -d RAC
PRCD-1120 : The resource for database RAC could not be found.
PRCR-1001 : Resource ora.rac.db does not exist
[oracle@collabn1 ~]$


I then remove the defined Server Pool that I used for this database.

[oracle@collabn1 ~]$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: MyPool
Importance: 100, Min: 1, Max: 2
Category:
Candidate server names: collabn1,collabn2
[oracle@collabn1 ~]$ srvctl remove srvpool -serverpool MyPool
[oracle@collabn1 ~]$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
[oracle@collabn1 ~]$


I then add the database back into the Cluster Registry.

[oracle@collabn1 ~]$ srvctl add database -d RAC \
> -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 \
> -pwfile +DATA/RAC/PASSWORD/pwdrac.277.931824933
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file: +DATA/RAC/PASSWORD/pwdrac.277.931824933
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is administrator managed
[oracle@collabn1 ~]$


I start the second node of the cluster before I configure the instances (Note : I have the $ORACLE_HOME/dbs pfiles created in advance).

[oracle@collabn1 ~]$ srvctl add instance -d RAC -i RAC1 -n collabn1
[oracle@collabn1 ~]$ srvctl add instance -d RAC -i RAC2 -n collabn2
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file: +DATA/RAC/PASSWORD/pwdrac.277.931824933
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: RAC1,RAC2
Configured nodes: collabn1,collabn2
Database is administrator managed
[oracle@collabn1 ~]$


I am now ready to start the database (instances).

[oracle@collabn1 ~]$ srvctl start database -d RAC
[oracle@collabn1 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node collabn1
Instance RAC2 is running on node collabn2
[oracle@collabn1 ~]$ ps -fuoracle |grep smon
oracle 3422 1 0 09:49 ? 00:00:00 asm_smon_+ASM1
oracle 4882 1 0 09:50 ? 00:00:00 mdb_smon_-MGMTDB
oracle 25431 1 0 10:30 ? 00:00:00 ora_smon_RAC1
oracle 27533 9821 0 10:33 pts/0 00:00:00 grep smon
[oracle@collabn1 ~]$
[root@collabn2 ~]# ps -fuoracle |grep smon
oracle 3460 1 0 10:19 ? 00:00:00 asm_smon_+ASM2
oracle 9561 1 0 10:30 ? 00:00:00 ora_smon_RAC2
[root@collabn2 ~]#
[oracle@collabn1 ~]$ env |grep SID
ORACLE_SID=RAC1
[oracle@collabn1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 16 10:34:15 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from v$containers;

CON_ID DBID CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME OPEN_MODE RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
1 2519807290 1 FD9AC20F64D344D7E043B6A9E80A2F2F
CDB$ROOT READ WRITE NO
16-JAN-17 10.31.34.014 AM +08:00
0 0 8192 ENABLED 0


CON_ID DBID CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME OPEN_MODE RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
2 2061548092 2061548092 44BB5E17F41A2618E053334EA8C006B9
PDB$SEED READ ONLY NO
16-JAN-17 10.31.34.859 AM +08:00
1594413 859832320 8192 ENABLED 0


CON_ID DBID CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME OPEN_MODE RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
3 1857084550 1857084550 44BBC69CE8F552AEE053334EA8C07365
PDB MOUNTED

1755977 0 8192 ENABLED 0


SQL> alter pluggable database PDB open;

Pluggable database altered.

SQL>
SQL> select con_id, name, open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDB READ WRITE

SQL>


Thus, I converted my PolicyManaged database to AdministratorManaged.
.
.
.

Categories: DBA Blogs

Procedure accepting more than 32K text as a parameter and inserting to CLOB coumn

Tom Kyte - Sun, 2017-01-15 16:46
Hi Team, We have one requirement where: 1) we have to write one proceudre which will insert into a clob column meant for email body. This procedure should accept text more than 32K. 2) the application or routine will execute this procedure by p...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs