SQL & PL/SQL :: Determine Index Drop?
Apr 3, 2012How to find out who has dropped and when was the index created in database?
View 39 Replies
ADVERTISEMENT
Text :: Query To Determine Index Fragmentation?
Mar 27, 2013Is 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.
PL/SQL :: Drop Context Index
Nov 9, 2012i tried creating an context index. But it is aborted in the middle. do to reuse the spce i want to drop the index. But it shows an error that " cannot drop index marked as process" how to delete the index.
View 1 Replies View RelatedSQL & PL/SQL :: Drop Index Which Has Constraint On Table?
May 18, 2011How to drop an index which has a constraint on the table?
while droping the index on the table wheather i should remove the
constraint or disable the constraint or without droping the
constraint
Server Administration :: Drop Index Hangs On SAP?
Apr 14, 2010On a SAP system, am trying to drop six indexes, largest is 300MB and smallest is 50MB.
I tried running drop index sapusername.index_name on the 50MB index via SQL*Plus and it seems to be taking forever. anything I can check on the database on why it is taking such a long time?
I can leave it to run overnight but worried that when I come back the next day, it will still be hanged. Is there any quick way of dropping the index, .i.e. drop immediate ...
Am not using SAP's BRTOOLs as it is also hanging from there and the SAP-ADMIN had approved for the DBA to drop it from our end instead.
Drop Index Tablespace - File Is Non-empty
Oct 15, 2012db 10.2.0.4
AIX 5.2
I am trying to drop a index tablespace I moved all indexes to new tablespace there is no indexes in that tablespace even any objects but still it is not dropping.it is giving error as below.
SQL> drop tablespace ret_index including contents;
drop tablespace ret_index including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> alter tablespace ret_index drop datafile '/path/index3.dbf';
alter tablespace ret_index drop datafile '/path/ret_index3.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty
try to resize
SQL> alter database datafile 4 resize 100m;
alter database datafile 4 resize 100m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Tablespace size is 280gb
PL/SQL :: Index-Organized Table Truncate Vs Lock / Stage / Drop / Recreate?
Apr 24, 2013I ran into an issue in a project where a function is recreating an index-organized table by doing:Table Structure:
CREATE TABLE table_iot(
...)
ORGANIZATION INDEX
OVERFLOW ...;
Recreate Steps:
1) Populate global temporary staging table (gtt) with data
-- where gtt is staging for target index-organized table (iot)
2) Lock the target index-organized table (iot)
3) Copy old iot data to gtt
-- gtt now contains old and new data
4) Create new index-organized table (iot2) from gtt
-- iot2 now contains old and new data
[code]...
Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.The following statement rebuilds the index-organized table admin_docindex:
ALTER TABLE admin_docindex MOVE;
PL/SQL :: Determine Next Due Date
Sep 12, 2012I 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 Determine Number Of TPS Supported On Solaris
Mar 24, 2013How 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
SQL & PL/SQL :: How To Determine The Data Type Of A Variable
Sep 15, 2010Code to determine the data type of a variable in oracle 10g.
View 5 Replies View RelatedPerformance Tuning :: How To Determine Slowness In I/O From AWR
Aug 9, 2012We 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
SQL & PL/SQL :: Determine Table Size Order By
Mar 28, 2012How to find the tables starting with smallest size and vice versa in schema level and database level?
View 4 Replies View RelatedSQL & PL/SQL :: Determine Rows Based On Their Grouping
Jul 9, 2010Ok 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?
SQL & PL/SQL :: Determine Number Of Times A Value Appears
Nov 11, 2012I 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.
11.2.0.3 DB - How To Determine Fetch Size Of Application
Feb 18, 2013The 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
SQL & PL/SQL :: Determine If / When Hierarchical Circular Reference Will Occur?
May 6, 2011I'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?
PL/SQL :: SQL Script Creation / Determine Which OS Oracle Is Being Hosted On?
Jan 30, 2013Is 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? :)
Backup & Recovery :: How To Determine The Rate Of Block Change
Aug 3, 2011rdbms: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?
Performance Tuning :: How To Determine Memory Usage For A Function
Jul 24, 2013determine 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 RelatedSQL & PL/SQL :: Using Analytic Function To Determine Maximum Concurrent Calls?
Apr 27, 2010I 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]....
Application Express :: How Does Apex Determine Which Files To Serve
Apr 11, 2013I'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..
Server Administration :: Command To Determine Which Table Can Benefit From Shrinking
Apr 14, 2010I 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.
Application Express :: How To Determine Which Tabular Form Check Box(es) Are Checked
Oct 1, 2012I'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 RelatedText :: Index For Domain Index With Composite Domain Index (CDI) Very Slow
Jun 27, 2012I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
Performance Tuning :: Correct Method To Determine Table Actual Size
Aug 9, 2012Which 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)
Performance Tuning :: How To Determine System Memory Usage By Oracle Processes
Jun 20, 2012we 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]......
Performance Tuning :: Local Index Versus Global Index On Partitioned Table
Jun 28, 2011I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies View RelatedServer Administration :: Convert Global Index To Local Index
Jun 23, 2011I have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.
I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.
SQL & PL/SQL :: ORA-01502 - Index Or Partition Of Such Index Is In Unusable State?
Nov 29, 2010I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.
Performance Tuning :: Index With NVL / Query Is No Longer Using Index
Nov 19, 2010I have a query which had a join:
a.c1=b.c1 and a.c2=@var
where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like
a.c1(+)=b.c1 and nvl(a.c2,@var)=@var
This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')
But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?