DBA Blogs

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

Categories: DBA Blogs

Regarding Listener

Tom Kyte - Mon, 2016-07-25 16:06
Hello sir, Currently i am working in Clover infotech as a ORACLE DBA...actually i wanted to know that is there any hard limit for number of Listeners in oracle . Means how many listeners we can configure in single database and also how many con...
Categories: DBA Blogs

Recover Catalog Manager

Tom Kyte - Mon, 2016-07-25 16:06
I understand that Recover Catalog Manager does has the metadata of the registered database, if the registered database has been recovered by doing restlogs(once we reset the logs we cannot use the old backups using the control file of the database as...
Categories: DBA Blogs

Difference between procedure and stored procedure.

Tom Kyte - Mon, 2016-07-25 16:06
Hi Tom, I want to know what is the difference between procedure and stored procedure? Thanks, Deekshith.
Categories: DBA Blogs

Interval partitioning

Tom Kyte - Mon, 2016-07-25 16:06
Hi Tom, I am trying to create a partitioned table so that a date-wise partition is created on inserting a new row for release_date column. But please note that release_date column is having number data type (as per design) and people want to create...
Categories: 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs