Is it possible to create a .sql script that, when executed, will determine which OS (i.e. Windows or Linux) Oracle is being hosted on? At the moment, all our scripts are written for Windows and I believe that for Linux the slashes must point the other way in order for the script to run.
Or, would the easiest thing be to create two copies of the script - one for Windows and one for Linux? :)
we have 96GB Memory on the UNIX server and 85% of its usage shows oracle processes I want to determine which Oracle processes are taking most of the memory
SGA is around 36G
SGA_TARGET is 40G
PGA is around 4G
the total of around 40-45 GB of usage is understandable but what other oracle process are chewing up the remaining 30-40 GB on the server is not known
load averages: 7.35, 6.46, 6.15; up 248+11:33:21 12:25:03
2202 processes: 2196 sleeping, 1 zombie, 5 on cpu
CPU states: 83.8% idle, 10.5% user, 5.8% kernel, 0.0% iowait, 0.0% swap
Memory: 96G phys mem, 15G free mem, 128G total swap, 128G free swap
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
21720 oracle 258 0 0 40G 40G cpu/48 215:28 2.04% oracle
10709 oracle 1 0 2 1816K 1448K cpu/9 0:02 0.90% res_conf_email_
[code]......
During ASM Disk Groups creation after the ASM instance creation, receive the following error: Disk Group ORAASMGROUP2 already exists. Cannot be created again
The Grid infrastructure was deinstall one time and still the same issue.
Suppose tablespace allocation_type is system then how oracle determines the initial extent and max extent size?
View 4 Replies View Relatedwhen i connected with oracle user i got error message like "
ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
We have the task to create to Materialized view from oracle 10g to 8i. - Will we face any kind of issue after creation of Materialized view. check what the prerequisite need to be verified is.
View 7 Replies View RelatedI have a table which if approc 5GB in size.I am trying to create a partition table from it. I created the new table,but when i am creating local indexes, i am getting an error when i create a unique key. I ll post the creation scripts:
CREATE TABLE A_PT_ORDREG
(
BUS_UNIT VARCHAR2(5) NOT NULL,
ORDER_NO VARCHAR2(15) NOT NULL,
PT_CODE VARCHAR2(15) NOT NULL,
REG_NO VARCHAR2(15) NOT NULL,
AS_APPLICATION VARCHAR2(5) NOT NULL,
[code].....
THE INDEX:
CREATE UNIQUE INDEX PK_A_PT_ORDREG ON A_PT_ORDREG (BUS_UNIT, ORDER_NO, SEQ_NO) local;
I am getting the following error:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
i have 1 xcel sheet in formatted data i want to import data in oracle table .. how do i do dat??
View 5 Replies View RelatedI want to know what is the difference between the file catproc that we run after creation of the database in the Oracle 9i,10g and 11g r2. I have run that file in 11g R2 but it is taking more time and more over there are many new things that i saw in 11g R2 than what were there in 9i and 10g.
View 11 Replies View Relatedhow to create database manually(without dbca).
View 2 Replies View Relatedwe have oracle 8i/win nt. (Prod Server running 24 X7)
On doing a explain plan on long running queries. i realized a couple of columns (that were used in the WHERE condition, were not having any indexes.)
More importantly, here we do not have a Test environment.
I would like to know if i can go ahead and create indexes for those 2 columns by issuing
CREATE INDEX index_name on table_name(Column_name);
on our PDB.
Would this be of any impact on our PDB? Do i check anything more prior to building these indexes. or Do have i create them at a down time of PDB.
I have a table that holds the definition of schedules, a schedule defines when a document should be submitted to a specific party. The schedule definition contains a start date, and end date, a recurrence type (is this submitted one time, or on a recurring schedule) and the frequency at which the document should be submitted. The second table provides a history of the submissions, this stores when it was due and when it was received. At the beginning of the month we pre-populate the submittal table with a list of records that will be due for the month. For instance, on September 1 we look through all of the schedules and determine which ones would have a record due at some point in 9/12 and then create a record in the submittal table.
I am having issues getting the calculated list of submittal records to work properly.
The DDL and DML will be in a follow-up post
Here is the query that I am currently using and fails to work properly.
with schedules as (
-- generate a list of valid permit schedules
select s.schedule_id,s.submittal_frequency_months,s.recurrence_type,
s.first_due_date,s.requires_approval,
round(round(months_between(to_date('09/01/2012','mm/dd/yyyy'),s.first_due_date))/decode(s.submittal_frequency_months,0,1,s.submittal_frequency_months)) recurrence_number
[Code]....
-- create a list of all potential due dates for these schedules
select submittal_id_seq.nextval,schedule_Id,8,requires_approval,
case
when recurrence_type='One Time'
then first_due_date
when recurrence_type='Recurring' and trunc(first_due_date)=to_date('09/01/2012','mm/dd/yyyy')
[Code]....
-- exclude those that already have a submittal record ;
Basically I found all possible records in the schedule table that could have a record due in September, then generate a result for all possible instances and then look at only those whose calculated due date is 09/01/2012. I've determined that the root problem I have right now is this line:
(select level iteration from dual connect by level <= (select max(schedules.recurrence_number) from schedules)) d
Schedule ID 469907 has a start date of 05/15/1992 and a frequency of every 2 months. I calculate what I call the recurrence number, which is the number of times the schedule has happened since its start date to now. I use that to do an add_months calculation from the start date and then eventually compare these calculted start dates with my target month (09/12). In this one records case the calculated recurrence number is 122. So when I generate the connect by level is does 122 records for every schedule, so I end up with duplicate records in the submittal table for many of the schedules. This current query could probably work if I could figure out a way to make the level be schedule ID specific, but I've failed at that thus far.
Aside from the fact that this is returning the wrong results, I am thinking there must be a better more efficient method to determine which records are due for a given month. I was thinking there is probably some cool way to use the model clause here, but I haven't got a grasp on that one yet.
If you run the following insert statement you'll see that it inserts over 2400 records:
insert into submittal (submittal_id,schedule_id,submittal_status_type_id,requires_approval,due_date,created_by,created_date,modified_by,modified_Date)
with schedules as (
-- generate a list of valid permit schedules
select s.schedule_id,s.submittal_frequency_months,s.recurrence_type,
s.first_due_date,s.requires_approval,
[Code]....
-- only submittals whose last due date has not passed, null last date included
and trunc(s.first_due_date,'mm') <= to_date('09/01/2012','mm/dd/yyyy') -- only valid start dates
--and round(round(months_between(to_date('09/01/2012','mm/dd/yyyy'),s.first_due_date))/decode(s.submittal_frequency_months,0,1,s.submittal_frequency_months)) >0
)
-- create a list of all potential due dates for these schedules
select submittal_id_seq.nextval,schedule_Id,8,requires_approval,
case
when recurrence_type='One Time'
then first_due_date
when recurrence_type='Recurring' and trunc(first_due_date)=to_date('09/01/2012','mm/dd/yyyy')
[Code]...
You can see the problem after words:
select schedule_id,count(0)
from submittal
where trunc(due_date,'mm')=to_date('09/01/2012','mm/dd/yyyy')
and submittal_status_type_id=8
having count(0) >1
group by schedule_id;Tony
How to find out who has dropped and when was the index created in database?
View 39 Replies View RelatedHow to calculate the number of TPS supported by any solaris server for example one server with below configuration .
Sun Blade X6270 with two 4-core processors
2 x 300 GB internal disk drives
Code to determine the data type of a variable in oracle 10g.
View 5 Replies View RelatedWe are investigating performance of SQL executions on a database server and we suspect I/O on the server is an issue
For example one particular statement accesses one row during execution (index access) and still takes 2.4 seconds out of which it does I/O for 1.9 seconds
which of the following sections in the AWR will give us the correct information about the I/O, it is slow or not?
1)
Load Profile
Logical reads per second
Physical reads per second
2)
Top 5 Timed Foreground Events
waits / time(s) for events like "db file sequential/scattered read"
average wait(ms) for events like "db file sequential/scattered read"
3)
Foreground Wait Events
db file sequential read
db file scattered read
4)
Wait Event Histogram
%of waits <1ms <2ms
Disk file operations I/O
db file sequential read
db file scattered read
5)
Wait Event Histogram Detail (64 msec to 2 sec)
Wait Event Histogram Detail (4 sec to 2 min)
6)
IOStat by Function summary
Buffer Cache Readsreads per sec
7)
File IO Stats
How to find the tables starting with smallest size and vice versa in schema level and database level?
View 4 Replies View RelatedOk assume there is a table (TableA) in this format
col1col2 col3col4col5col6
--------------------------------------------------------------
R1route1route1Description1AABBCC
R1route1route1Description1AACC
R1route1route1Description1CCBB
R2route2route1Description2GGKKLL
R2route2route1Description2GGLL
R2route2route1Description2LLKK
[Code]..
The data in the table was imported from a csv file and there is a relationship between the rows. Each combination of col1, col2 and col3 describes a full route of a journey. The row with an entry in col6 describes the full route and the other rows describes each leg in the route.
For example, for R1, the route is AA to BB via CC.
Another example for R4 the route is FF to SS via XX, PP, and OO.
What i would like to do is missing a route. For example the route for R3 is DD to EE via FF. There is an entry for DD to FF but is missing an entry for FF to EE.
The results should return the following rows which are incomplete
R3route3route1Description3DDEEFF
R3route3route1Description3DDFF
R5route5route5Description5RRTTUU|VV
What is the best way to do this?
Here is what i have come up with but it doesnt quite returned the correct result.
select * from tableA a
Where not exists(
select 1 from tableA b
where instr(col6,col4,1)>0 and instr(col6,col1,1)>0)
And a.col1=b.col1
And a.col2=b.col2
And a.col3=b.col3
)
Is there an easier way to achieve this?
I am trying to determine the number of times a value appears and display the count. However the value can only be counted once per 'trip' even though it may appear several times per trip.
for e.g.
Quote:trip table
-------
trip_id start_date end_date duration
445 01-jan-12 03-jan-12 3
Quote:pickup table
--------
pickup_id trip_id company
1 445 randomname
2 445 randomname
3 445 google.inc
4 878 randomname
with the above data the expected value would be two because the trip id appears twice so it was just the one trip - given a count of one. I am not sure how create a query to check this.
The db is 11.2.0.3 on a linux machine.I would like to know the "fetch size" of an application, but I was not able to find any related meteris in v$statname.
The application configruation is invisible to me.Do I need to do some calculations based on statistic metrics from v$statname?
If so, what meteris should be considered for the assumption for "fetch size" ?
The following is from manual, but the application configuration is invisible to me.
[URL]
Setting the Fetch Size
The following methods are available in all Statement, PreparedStatement, CallableStatement, and ResultSet objects for setting and getting the fetch size:
•void setFetchSize(int rows) throws SQLException
•int getFetchSize() throws SQLException
I'm trying to determine if/when a possible Hierarchical circular reference will occur in my data
Sample Hierarchical structure that I have
Emp -> Supv
A
BA
CB
DC
EC
[Code]....
Finally, to my question. It seems that I can detect the problem After it happens but do I need a trigger on the update statement to detect if/when a possible circular reference will occur?? or can I run a sql statement prior to update to detect possible circular reference?
Is this the right query to determine index fragmentation ?
SELECT AVG (tfrag)
FROM (SELECT /*+ ORDERED USE_NL(i) INDEX(i DR$TEXT_IDX$X) */
i.token_text,
(1
[code]...
The reason I am asking that before index rebuild it returned 86% but after rebuild (ALTER INDEX .. REBUILD) it returned
96% which does not make sense.
I did try ctx_report.index_stats but it takes more time to run.
rdbms:oracle 10gr2
os:windows
with reference to
[URL]......
Quote:
A.2.2 Writing Backup Scripts for Disk and Tape Scenarios
As in the disk-only scenarios, the backup scripts in this section are categorized based on database workload. as stated very clearly it depends on the workload, more precisely the rate of block change. The size of the database can be found out based on formula from
[URL]....
so how would I know the rate of block change in order to know which script is suitable for me? I try to find out the rate of block change for the database based on change tracking file but based on
[URL].....
Quote:
The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database. So how do I determine the rate of change? can the rate of block change based on size of archive logs?
I have the following information with me:
starting from 5/10/2011 0101
ending 5/18/2011 1114
this constitute to 9.5 days
F:
ecover_area>dir/w
1644 File(s) 27,942,770,176 bytes
2 Dir(s) 10,019,270,656 bytes free
average size of each file 27,942,770,176/1644
=16996818.841849148418491484184915
average size of each day's log = 27,942,770,176/9.5
=2941344229.0526315789473684210526
about 3G
If I have a database size of 92G, based on the archive log size of about 3G per day, can I conclude that a change of 3G/92G is considered as few block change?
determine if a function is worth pinning in memory? I want to come up with a percentage, implying that if the function is already im memory 80%+ of the time then it is not worth it.
View 5 Replies View RelatedI have a requirement to calculate the maximum number of concurrent calls from the following data:
Create_date connect_date_time disconnect_date_time duration ...
12/01/10 13:20:26 1263253551 1263254153 602
...
I have attempted to use the analytic function to keep a running total of the count of active calls based on the connect and disconnect times given for each record row.
e.g.
SELECT
count(*) calls,
avg(duration)/60 average_duration_mins,
max(duration)/60 max_duration_mins,
sum(duration)/60 total_mins,
(SUM(DURATION)/60)*0.04 total_cost_4c_per_min
[code]....
I'm using apex 4.2.1.00.08 and I cannot figure out how apex manages the static files and cannot find anything in the docs (other that some high level UI description). The application is serving some file and I cannot find which one it is in any easy way.
I have a workspace where there are several files that have the same name, and I cannot understand how apex figures out which one to serve, and also don't understand what is value of associating a file with an application.
There are files associated with application 0, which don't appear to show up in the "shared components", but can be seen as
SELECT *
FROM wwv_flow_files
WHERE flow_id = 0;
and can apparently only be deleted using "SQL Commands" inside apex. the URL called is something like
wwv_flow_file_mgr.get_file?p_security_group_id=13498126233076320&p_fname=myfile.css
so apparently the only parameters that matter are the workspace and the file name. The associated application is irrelevant. apparently files linked to flow_id 0 have precedence over all the other files..
I use following command to determine which table can benefit from shrinking
select * from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'))
order by reclaimable_space desc
then i give following command to get reclaimable space
alter table t1 enable row movement ;
alter table t1 shrink space cascade;
alter table t1 disable row movement ;
in table t1 427MB was shown as reclaimable space, after executing above commands, i run dbms_space procedure again to check the out come, but result was same.I understand tablespaces are by default ASSM in 11g, none of table has LONG datatype or LOB indexes or MVIEW with ON COMMIT.
I'd like to determine (from code defined in a button definition) which check box(es) on a Tabular Form are checked. Assuming only one check box is checked, I want to obtain one of the values in that row for further processing. How to interrogate the Tabular Form to find out which box is checked?
View 9 Replies View RelatedWhich is the correct method to calculate actual data size in a table? becaue when I serach in google, I saw the below line.
"Oracle thumb rule says (actual space required for a table + 30 % space) will calculate the original space requirement for a table."
Method 1:
actual space = num_rows*avg_row_len
Method 2:
actual space = (Num of rows in a table) * (Avg_row_len) + ((Num of rows in a table) * (Avg_row_len)* 0.3)
APEX 4.0 Sometimes, I am getting the below error when I tried to logging into APEX workspace (after giving workspace name, User name and password), Expecting p_company or wwv_flow_company cookie to contain security group id of application owner.
ErrorERR-7621 Could not determine workspace for application (:) on application accept. I found some threads related to this but in different way not exactly when I am getting this error.
After 2 or 3 times, this error will not come and I can able to logging into workspace What is the cause for this issue??