In our project we have many instance running with Oracle in one solaris zone. We are in the process of cost reduction so planning to bring the CPU in shared pool and reduce them.
if we can bring all the NUP (Named user Perception) CPU on one shared pool. Will it be cost effective and is there any problems in performing such change.
Im planning to install OID. I will use it for Oracle Net name resolution and Enterprise User Security.
How is this product licensed? Is it included in a enterprise rdbms license?
I am using 11.2.0.1 database on windows 2008 server. I would like to know which are all the tools/components that have licences to use in my database. Can i get licence information through any query?
View 2 Replies View RelatedI downloaded the oracle 11g R2 from oracle site. I am using the database for tests in one machine there are 5 months and it works normally (with all features).
My question is, what limitations of this database? What's prevents me to use this db in production environment?
This will be my crontab entry
#!/bin/sh
* * 1 1,4,7,10 * . .profile; /root/bin/SubLicenseCounter.sh QUARTERLY >>/trace/SubLicenseCount.log 2>&1
SubLicenseCounter.sh
sqlplus owner/owner
select count(*) from tb_subscription s, tb_counter cnt where s.tb_destinationaddress is not null and s.tb_ID = cnt.tb_ID and cnt.tb_SENDVALUE>0;
11:53:51 SQL>select * from v$version
11:53:55 2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
All, I have an infuriating problem and I'm hoping for some advice. Well actually it is a number of problems but I'll use one example as a microcosm.I'm going to use as the example:
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 912K(100)| |
| 1 | HASH JOIN | | 3412K| 940M| 826M| 912K (1)| 03:02:28 |
| 2 | HASH JOIN | | 3412K| 787M| 699M| 748K (1)| 02:29:39 |
| 3 | HASH JOIN | | 3610K| 657M| 557M| 560K (1)| 01:52:07 |
[code]...
Now then, this query runs perfectly and as expected (direct reads/DB scattered reads) until it hit step 13 in the plan (FTS of T6). At step 13 it basically enters into a (rather fatal) process of reading almost nothing but undo exclusively.
Now then, I wonder why this might be. Of course the natural reaction is to say "they data is changing, its read consistency (optional rookie insult)". Now, I found this hard to believe that the volume of data could be so extensively changed, keeping in mind its a good sized table and it's doing a full access.
I checked the DBA_HIST_SEG_STAT(DB_BLOCK_CHANGES_DELTA) view for the period and in the run up, i.e. the explain plan steps 2-12 took approx 20 minutes. In this window, less than 1% of the blocks in the table T6 were changed (approx 3-4k out of a 500k block table).Nearly every access of this table is reading undo, yet since this query started, 99% of the table is unchanged. But the query is behaving as if everything it reads has altered since it began.
To be clear, I understand the read consistency etc and expect it, what I DONT expect is the virtually the ENTIRE table scan operation routing via undo when I'm accessing the full table, yet hardly any blocks are altered after I start running.
Note this query/application seems to fetch the rows in blocks of 1k, its almost as if its re-executing for each fetch, but I wouldn't have expected that.
In summary:
>Query starts
>hits final table
>almost exclusive undo reads
>under 1% of block changes in read table since query began
Is there any other way anyone knows of that might cause a session to read into undo beyond a session changing data after the query executed?
I have to breakdown the sum of qty into each line as single piece ,as example below how to achieve this using sql as i am doing it using a procedure.
CREATE TABLE OT_CUT_ITEM
(
CTI_SYS_ID NUMBER,
CTI_ITEM_CODE VARCHAR2(30 BYTE),
CTI_LEN NUMBER,
CTI_QTY NUMBER
);
[code]....
output required is
CTI_SYS_ID CTI_ITEM_CODE CTI_LEN CTI_QTY
---------- ------------------------------ ---------- ----------
1 A 7000 1
1 A 7000 1
1 A 7000 1
1 A 7000 1
2 B 6000 1
[code]....
Following query is giving me the required output but i want to use reg_exp from same output.
SELECT TO_NUMBER ((SUBSTR (narr,
INSTR (narr, '#') + 1,
(INSTR (narr, 'DT')) - (INSTR (narr, '#') + 1)
) "Bill No"
FROM (SELECT '25 NOS. BILL BOOK FOR FAISALABAD @80/- TH A.R. PRINTER AGST BILL#21 DT:31-01-2013 TH
[code]...
Required Output using Reg_Exp
SQL>/
Bill No
----------
21
9
I have a user in my oracle database, I would like to know details such as, how many times and by using which tools same user has got logged for past one week.
How do I do that. I have sql which shows the current session either he is logged in, if so then which application or not logged in. But I required the information for past one week How do I get those details.
I want to save ip address, os username, terminal information at the time of record insertion/updation therefore I am using sys_context function but it gives me error at the time of form compilation.
ERROR:
ORA-00600: internal error code, arguments: [17069],[134386616],[],[],[],[],[],[]
My user has already DBA privilege.
I have one table which has two columns name,qty and it has data like arif,3 pcs i want to display it in 3 lines if the qty is 3 and in 2 lines if the qty is 2 using sql query
View 6 Replies View RelatedHow can I find out a user session/connection details like ( his last connection time, his activity etc)
Here is the issue, a old dba_user_id who physically no longer exists is not removed from the database. At some time, a session is opened under his user_id and some activity runs and closes the session. So, checked scheduled jobs under him and disabled them. But not sure whether the scheduled jobs are creating a session under his user_id or some thing else.
I checked in v$session, but since I am not sure at what time his session opens, so I am not able to get his session details.
In AWR Report i found that there are few SQL queries that have full table scans how can we drill about this and find which table have full scan
yes with Explain plan we can do this but is there any other way in Oracle 11g
We are planning Database Migration From Windows to Linux Using RMAN..We required Oracle database 10.2.0.4 for Linux 64 bit.
View 3 Replies View Related i have following table
CREATE TABLE THREAD_SHADES
(
ITM_COD NUMBER NOT NULL,
ITM_DES VARCHAR2(250 BYTE) NOT NULL,
)
[Code]...
Result is
ITM_COD ITM_DES
80064186THREAD TEX-105 SHADE# 7921 (1500 MTRS)
80064187THREAD TEX-40 SHADE#7921 (3000 MTRS)
80114482THREAD TEX 40 SHADE C-8762 1500MTR LOCAL
80130541THREAD TEX-60 SHADE C8676 J&P COAST ASTRA 1000 MTRS
I want a query which should return only Sahde # from above data result must be as
7921
C-8762
C-8762
C8676
I have a procedure with 20 parameters, acutely it is to update a table and each param represents respective columns in a table. I want to update only few selected columns(random), as of now am passing Null as param values for remaining.
Is there any way to ignore the unnecessary parameters instead of passing NULL value.My Proc call looks like...
Exec MyProce(IN_ID, NULL, NULL, NULL,NULL,NULL,'SOME_VALUE', NULL,NULL,NULL,NULL,NULL,NULL,...);
Or
Exec MyProce(IN_ID, NULL, NULL, NULL,'SOME_VALUE' ,NULL,'SOME_VALUE', NULL,NULL,NULL,NULL,NULL,NULL,...);
Following on from this answered thread: (xmlagg(xmlelement) - Distinct Values Required..Whereby the last poster recommended the use of
RTRIM (
XMLAGG (
XMLELEMENT (
E,
XMLATTRIBUTES (segment1|| ',' AS "Seg")
)
ORDER BY segment1 ASC
).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),
','
)
To get a distinct list of elements. Works great, but I actually need to use the concept of "distinct immediately preceding".
Sample data:
create table xml_test
(emp_number number, seq number)
insert into xml_test values ('12345',1);
insert into xml_test values ('23456',2);
insert into xml_test values ('44323',3);
insert into xml_test values ('12345',4);
[code]....
I will be installing SOA Suite with OSB in Exalogic for testing. It is not for the production environment and very simple application will be used for testing. Database admin asked me what size database would be required but I did not find clear answer in the documentation anywhere.
View 0 Replies View Relatedwhen the tables are updated, the following detals must be correct to ensure that the links in the affected tables are in place.
PLUPDATE_NEW(PLUP_SAVE_SEQ field value) must be the same with PLUPDATE_BENEF_NEW (PLUP_NEW_BENEF_SAVE_SEQ field value)
PLUPDATE_OLD(PLUP_SAVE_SEQ field value) must be the same with PLUPDATE_BENEF_OLD (PLUP_OLD_BENEF_SAVE_SEQ field value)?
[Code]....
i tried this code, what should i do in the link for this tables?
i want to display employees details when i am passing Deptno and that department employees only display in Oracle Forms
View 16 Replies View RelatedI have tables dynamically created with dynamic number of columns. There will be 7 columns that would always be fixed with their names and data types. They will always be last 7 columns.
way to write a select statement on those dynamic tables where only those known 7 columns are not selected and I need to select all the rest columns but leave the 7 columns names I know.
Under what userid(privileges) does a scheduled DBMS Jos run, is it under the same user that scheduled the job?
Does oracle internally log in(opens a new session) in order to run a scheduled DBMS Job?
some reference websites where I can get some details about how the scheduled jobs are executed?
When I execute my form, on pressing key F5 it displays the all available block names in the form. I want to restrict this.
View 5 Replies View RelatedI have three tables as shown in the image. Need to get the details of each employee in a single row..
EMployee_id BaseSalary Bonus Hike shares
Get following output using user_source(or other) view?
Package_nameProcedure_nameline_startline_end
ABCXYZ1022
In procedure_name column it could be any procedure, function name or any object name exist in package body.
We have a scenario where we need to get the details of quota details on different tablespaces for a user. Is there any way to get this information directly using some views etc.
I tried with following option.. but not found in this... i think we can get this TOAD some other tools. but i never tried using SQL directly...
SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual;
Split a column with values like 1-2-21-3 into 001-002-21-003 (ie format required is 000-000-00-000) using sql.
View 3 Replies View RelatedTable Name: F_SCENARIO
System : Dataware house
Oracle version : 11g
Record Count : 2 Million records
Correct scenario records
F_Key F_Bridge_key Record_type
1 1 1
2 1 2
3 1 3
Wrong scenario records
F_Key F_Bridge_key Record_type
1 1 1
2 -5 2
3 -6 3
I want to write a Merge statement to update the negative values into 1.
i have a good query but I thought i know the solution but actually I didn't it's very simple and straight forward but i didn't catch the rope terminal to follow now I have the following code
CREATE TABLE TRANSLATORS
(Tr_code VARCHAR2(8),
Tr_name VARCHAR2(50),
Tr_age number(2),
Tr_location varchar2(25),
constraint PK_TR_CODE PRIMARY KEY(Tr_code)
);
[code]....
NOW I NEED A QUERY which will select the only translator who knows the languages written in where clause like if i specify two languages like 'italy' and 'english' the query should retrieve to me GH and SE also if i passed languages 'spainsh' and 'persia' it should return JH only
but if i passed languages like 'Italy' and 'Dutch' it should not return any thing (just : no rows selected)
Oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
create table plc_dw_dry_run_fic_rfsh (dry_run_fic_id number,ltr_code varchar2(10),
next_cpn_lvl_id1 number,next_intrvl_code varchar2(10), next_intrvl_value varchar2(120));
begin
insert into plc_dw_dry_run_fic_rfsh values (424740,'1','','LTML','48000');
insert into plc_dw_dry_run_fic_rfsh values (424736,'1','','LTML','32000');
insert into plc_dw_dry_run_fic_rfsh values (424738,'1','','LTML','128000');
insert into plc_dw_dry_run_fic_rfsh values (424783,'1','','LTML','96000');
insert into plc_dw_dry_run_fic_rfsh values (424789,'2','','LTML','96000');
insert into plc_dw_dry_run_fic_rfsh values (424750,'1',198,'LTML','10000');
insert into plc_dw_dry_run_fic_rfsh values (424760,'1',199,'LDFM','20000');
insert into plc_dw_dry_run_fic_rfsh values (424770,'1','','LTML','192000');
end;
commit;
Expected output
---------------
DRY_RUN_FIC_ID LTR_CODE NEW_LTR_CODE
424740 '1' '1b'
424736 '1' '1'
424738 '1' '1'
424783 '1' '1a'
424789 '2' '1a'
424750 '1' '1a'
424760 '1' '1a'
424770 '1' '1a'
Rules
---------
1) First next_cpn_lvl_id1 should be considered (next_intrvl_code can be anything), if next_cpn_lvl_id1 is 198 or 199 then new_ltr_code should have 1a.
2) If next_cpn_lvl_id1 is not 198 or 199, then next_intrvl_code should be considered
a) if next_intrvl_code is LTML and mod of next_intrvl_value and 96000 is zero then new_ltr_code should be 1a
b) if next_intrvl_code is LTML and mod of next_intrvl_value and 48000 is zero then new_ltr_code should be 1b
3) If 1 & 2 are not satisfied, ltr_code should be assigned to new_ltr_code.