DBA Blogs

How to use case statement inside xml table

Tom Kyte - Mon, 2016-07-25 16:06
Hi, I have a table like below create table emptable ( id number primary key, emps varchar2 ( 500 )) ; with this data in it insert into emptable values ( 1, '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><employee emp_no="...
Categories: DBA Blogs

Copy table data from one database to another with table partitions and references

Tom Kyte - Mon, 2016-07-25 16:06
Hi Guys, Need your help. I have 4-5 different tables in Oracle database(11g). I have to copy the data from all these tables from there(1st DB) to another database(2nd DB). Source database is having partitioned tables and data in one table could...
Categories: DBA Blogs

Export into pre-existing schema

Tom Kyte - Sun, 2016-07-24 21:46
Hi tom, I have a schema export with expdp with this - expdp user/passwors@test directory=dump_dir dumpfile=user.dmp logfile=user.log schemas=user User have 100 tables 50 procedures and triggers now I want to import this on dB how to import i...
Categories: DBA Blogs

does partition volume impact ad-hoc queries

Tom Kyte - Sun, 2016-07-24 21:46
Hi Tom, We are upgrading from Oracle 8i which is running on a standalone Unix server to 11g release 2 to Exadata. We are migrating Data Warehouse database with large partitioned tables (1000 million and more rows) partitioned monthly,quarterly a...
Categories: DBA Blogs

schema comparison

Tom Kyte - Sun, 2016-07-24 21:46
I learnt that some of the production objects were missing under particular schema. At that point I decided to change the password of the schema in production so that the comparison can be done with the Stage environment and the extent pf damage to th...
Categories: DBA Blogs

Why there is so much logging for "insert /+* append */ into tab select"?

Tom Kyte - Sun, 2016-07-24 21:46
Hi Tom, Q1 ~~~~ I was executing the following sql statement with the table in nologging mode. But it seems the changes are logged (not just the dictionary changes). Please have a look at the following. Case1: ======== SQL> insert /*+...
Categories: DBA Blogs

CODE : Persistent Variables via PL/SQL Package and DBMS_APPLICATION_INFO

Hemant K Chitale - Sat, 2016-07-23 10:43
I am now introducing some code samples in my blog.  I won't restrict myself to SQL but will also include PL/SQL  (C ? Bourne/Korn Shell ? what else ?)

This is the first of such samples.


Here I demonstrate using a PL/SQL Package to define persistent variables and then using them with DBMS_APPLICATION_INFO.  This demo consists of only 2 variables being used by 1 session.  But we could have a number of variables in this Package and invoked by multiple client sessions in the real workd.

I first :

SQL> grant create procedure to hr;

Grant succeeded.

SQL>


Then, in the HR schema, I setup a Package to define variables that can persist throughout a session.  Public Variables defined in a Package, once invoked, persist throughout the session that invoked them.

create or replace package
define_my_variables
authid definer
is
my_application varchar2(25) := 'Human Resources';
my_base_schema varchar2(25) := 'HR';
end;
/

grant execute on define_my_variables to hemant;
grant select on employees to hemant;


As HEMANT, I then execute :

SQL> connect hemant/hemant  
Connected.
SQL> execute dbms_application_info.set_module(-
> module_name=>HR.define_my_variables.my_application,-
> action_name=>NULL);

PL/SQL procedure successfully completed.

SQL>


As SYSTEM, the DBA can monitor HEMANT

QL> show user
USER is "SYSTEM"
SQL> select sid, serial#, to_char(logon_time,'DD-MON HH24:MI:SS') Logon_At, module, action
2 from v$session
3 where username = 'HEMANT'
4 order by 1
5 /

SID SERIAL# LOGON_AT MODULE
---------- ---------- ------------------------ ----------------------------------------------------------------
ACTION
----------------------------------------------------------------
1 63450 23-JUL 23:24:03 Human Resources



SQL>


Then, HEMANT intends to run a query on the EMPLOYEES Table.

SQL> execute dbms_application_info.set_action(-
> action_name=>'Query EMP');

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.employees where job_id like '%PROG%'
2 /

COUNT(*)
----------
5

SQL>


SYSTEM can see what he is doing with

SQL> l
1 select sid, serial#, to_char(logon_time,'DD-MON HH24:MI:SS') Logon_At, module, action
2 from v$session
3 where username = 'HEMANT'
4* order by 1
SQL> /

SID SERIAL# LOGON_AT MODULE
---------- ---------- ------------------------ ----------------------------------------------------------------
ACTION
----------------------------------------------------------------
1 63450 23-JUL 23:24:03 Human Resources
Query EMP


SQL>


Returning, to the HR login, I can see :

SQL> show user
USER is "HEMANT"
SQL> execute dbms_output.put_line(-
> 'I am running ' || hr.define_my_variables.my_application || ' against ' || hr.define_my_variables.my_base_schema);
I am running Human Resources against HR

PL/SQL procedure successfully completed.

SQL>


So, I have demonstrated :
1.  Using a PLSQL Package Specification (without the need for a Package Body) to define variables that are visible to another session.

2.  The possibility of using this across schemas.  HR could be my "master schema" that setups all variables and HEMANT is one of many "client" schemas (or users) that use these variables..

3. The variables defined will persist throughout the client session once they are invoked.

4.  Using DBMS_APPLICATION_INFO to call these variables and setup client information.


Note :  SYSTEM can also trace HEMANT's session using DBMS_MONITOR as demonstrated in Trace Files -- 2 : Generating SQL Traces (another session)

.
.
.

Categories: DBA Blogs

Visibility on package objects

Tom Kyte - Fri, 2016-07-22 14:46
Hello, I have package that create objects and variables on the package header spec, such as: CREATE OR REPLACE PACKAGE Test_Package AS bVariable BOOLEAN := FALSE; END Test_Package; Am I correct in assuming that Test_Package.bVari...
Categories: DBA Blogs

Determining current database name

Tom Kyte - Fri, 2016-07-22 14:46
Which (if either) is preferable for determining the current database name: ORA_DATABASE_NAME or sys_context('USERENV', 'DB_NAME') ? Thanx, D
Categories: DBA Blogs

Inserting a decimal which has no decimal into the table with a decimal point

Tom Kyte - Fri, 2016-07-22 14:46
Hi Tom, I want to insert a decimal number which has no decimal point into a table with a decimal point. Is there a predefined function or a way to do this? Ex: (46926831654) should be inserted as (469268.31654) I dont want to use the string...
Categories: DBA Blogs

executing stored procedures after context change

Tom Kyte - Fri, 2016-07-22 14:46
Hi, I need to execute a stored procedure that exists in many schema, on demand, in any given schema. I am trying to change the current_schema then execute the procedure (GS_LOAD_FDB_PKG is the package in each of our schemas and gst_disease_dxid_u...
Categories: DBA Blogs

RMAN and Archivelog

Tom Kyte - Fri, 2016-07-22 14:46
Hi, Maybe this question was already answered somewhere but there so many question that I can not find the solution on this site. It is rather a simple one (I hope not a stupid one). Strictly speaking, when I make a "backup database" with Rman, do...
Categories: DBA Blogs

Restricting datafile addition to a filesystem (ASM)

Tom Kyte - Fri, 2016-07-22 14:46
Hi Tom The normal method of adding datafiles to a tablespace is to login to the database as "sqlplus / as sysdba" and then executing the "alter tablespace add datafile.." command. Is there a way to prevent / prompt the DBAs from accidentally adding ...
Categories: DBA Blogs

User defined Year-Month data type

Tom Kyte - Fri, 2016-07-22 14:46
Hi Answers Team. I keep seeing my maintenance / design / development teams abusing dates everywhere. One example is the use of YYYYMM as a way to extract a month from some piece of data what starts out as a date. 1. database design will cre...
Categories: DBA Blogs

Links for 2016-07-21 [del.icio.us]

Categories: DBA Blogs

Selectively purge the SQLAREA... terrible idea?

Tom Kyte - Thu, 2016-07-21 20:26
Hi "Tom"- We have an outside vendor's product hitting our database and never using bind variables. Every statement is unique. We can't change it, we're stuck with it. This outside program is a very high volume. These constantly unique statem...
Categories: DBA Blogs

parallel statement queueing - how to synchronize sessions?

Tom Kyte - Thu, 2016-07-21 20:26
Hi Tom-Team, I want to process statements in parallel by setting "parallel_degree_policy=auto". They are a mixture of DML and DDL statements. In this context, the order in which statements execute, is undefined. I know that I can amend that (parti...
Categories: DBA Blogs

Identifying ASM "orphans"

Tom Kyte - Thu, 2016-07-21 20:26
We've been migrating several DBs from their old Oracle 10G platforms to our new Oracle SuperClusters (SC). The older DBs were flatfile based, but the new SC-based DBs are using ASM. Several of the migrations initially failed, and some of the older DB...
Categories: DBA Blogs

PMON

Tom Kyte - Thu, 2016-07-21 20:26
what is PMON?
Categories: DBA Blogs

DBMS_SCHEDULER jobs - How Job_Priority attribute works?

Tom Kyte - Thu, 2016-07-21 20:26
Hi, Request your help to understand working of Job_Priority attribute that can be set for jobs created via DBMS_SCHEDULER. From my tests it seems that the attribute behavior is somehow linked to the number of jobs scheduled. However, looks like...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs