if there are any problems with updating statistics? My manager told me that they had big performance issue when they updated the statistics of whole database. Is this a rare case?
I know the advantages of updating statistics,but are there any dis-advantages?
how many rows certain tables have.
updating the statistics for a table (with GATER_TABLE_STATS) and using NUM_ROWS then. This works fine for me as long as I am the owner of the table, but when someone else is, I always get this error: ORA-20000: Table does not exist or insufficient privileges.what privileges do I need to use GATHER_ TABLE_ STATS on all Tables, which were created by Users?
when I tried to use ANALYZE TABLE TEST_TABLE COMPUTE STATISTICS on a certain table I got the following error: a view is not appropriate here. The strange thing is, TEST_TABLE is not a view (at least it is not listed in ALL_VIEWS and is listed in ALL_TABLES, so it cant be a view right?).
Besides, is there another way to gather Table Statistics (not using Analyze Table or Gather_Table_Stats)?
What privileges is required to gather statistics of oracle database using DBMS_STATS/ANALYZE command.
View 1 Replies View RelatedI want to get the stale stats for Table resides at APPS schema. Is there is any table or view exists to get the details like DBA_STALE_STATS or anything? Currently I am checking LAST_ANALYZED column from DBA_TABLES?
View 2 Replies View Related,I need to view database statistics after executing SQL> exec dbms_stats.gather_database_stats;is that really possible? how to check it?
View 7 Replies View RelatedWhy do we gather table statistics manually ?Is it because of database performance.
I know In Oracle Database 10g, Automatic Optimizer Statistics Collection reduces the likelihood of poorly performing SQL statements due to stale or invalid statistics and enhances SQL execution performance by providing optimal input to the query optimizer.
Optimizer gathers statistics when 10% table rows have been changed.
Is there any script that give us output of all Stale Statistics in Oracle Database
View 8 Replies View RelatedWhat is the scenario, we should use the new feature of 11g called-Pending Statistics.
Eg:-
EXEC DBMS_STATS.set_table_prefs ('SCOTT', 'EMP', 'PUBLISH', 'false');
I use the following query to find out the remaining time to complete the table statistics which is running currently.
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,username,context,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;
SOFAR column shows 9325 and totalwork column shows 12287.How to calculate the columns in terms of hours and minutes ?
And also sometime the query never shows the output of the query for the current running query.
the importance of refreshing the stale statistics in oracle database and also how often we should do this?
View 3 Replies View RelatedI am connect to remote database with a user named 'TEST', this user has dba privileges. I am not able to gather the statistics of neither test schema nor for any table that exists in this schema.
SQL> EXEC dbms_stats.gather_schema_stats('TEST', cascade=>TRUE);
BEGIN dbms_stats.gather_schema_stats('TEST', cascade=>TRUE); END;
*
ERROR at line 1:
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1
[code]....
I am looking at a performance issue at the moment and trying to replicate on a test system. I am initially looking at the impact of upto-date statistics on the main schema's objects.
For this I wanted to:
first run the batch with whatever stats were present in the database Flashback the db to before the batch . Gather stats Re-run the batch with updated stats and compare results.
However, I inadvertently ran the stats job before running the load the first time! I have the SCN from when the environment was set up like production (ie before the stats were run) so am I correct in saying that if I flashback to this point then the stats will be "old" and I can just run the batch then? I know I can verify this when I Flashback the database by looking at LAST_ANALYZED on tables etc but it would be good to know this before hand as it's a 12 hour batch.
In Process of upgrading Oracle EBS 12.0.4 to 12.1.1 I updated the $IAS_ORACLE_HOME/appsutil/jdk to jdk 6u24. After replacing the original jdk with jdk 6u24 when I attempted to start the apps adoacorectl.sh failed with below error.
Quote:ld.so.1: /d4/app/oracle/apps/12.0.0/vdd700/web/10.1.3/appsutil/jdk/bin/java: fatal: libjli.so: open failed: No such file or d
irectory
I followed below steps to update the jdk
1. extracted the jdk-6u24-solaris-sparcv9.tar.Z to $IAS_ORACLE_HOME/appsutil/jdk
2. Copied lib from old jdk to $IAS_ORACLE_HOME/appsutil/jdk
3. Copied lib from oldjdk/jre to $IAS_ORACLE_HOME/appsutil/jdk/jre
4. The java was not infact in jdk/bin rather it was /bin/sparcv9 so I copied them to /jdk/bin. The same way copied the executables from /jdk/jre/bin/sparcv9 to /jdk/jre/bin .
I have a table with counter value which will be incremented or decremented by several application servers.
SQL> select * from test;
COUNTER
----------
10
Application servers(multiple servers) will be running update against this row for increasing the counter value or decreasing the counter value.
update test set counter=counter+1;
update test set counter=counter-1;
update test set counter=counter+1;
update test set counter=counter+1;
So when update happens concurrently to this table will the counter value gets messed up?
I did a small test by opening multiple sessions for running update and the result I got for above update statement was 11,10,11,12.
But our developer is bit skeptical about this approach and he is using select for update and then updating the row.
Which approach will be better?
We are using oracle 10g in linux machine which runs as server. And there are around 25 clients which run at the same time parallel to each other. I am new to oracle 10g and also for client-server version of database. I faced some problems when i first put the server into actual use.
-> Users have been created and tablespace is USERS and temporary space is TEMP. RESOURCE privilege is given.
-> Some times login fails for some clients.
-> Some times all the client login will be successful.
-> Query execution will be sometimes fast, but some times it takes too much time.
-> Some times the connection will be lost and again I have bring UP the server.
to maintain the oracle server.
-> Should I have to make any configuration settings in the server.
-> Should I change any values in the server.
-> Is there any way to properly maintain the server.
OS authentication is not working
Does 11g need any different setting?
Following are the settings
1)
SQL> show parameter os_a
NAME TYPE VALUE
------------------------------------ ----------- --------------
os_authent_prefix string OPS$
remote_os_authent boolean FALSE
2) sqlnet.ora has following setting
SQLNET.AUTHENTICATION_SERVICES = (NTS)
3) I have created user like following
sql> create user OPS$CLASS
2 identified externally
3 default tablespace user_data
4 temporary tablespace temporary_data
5 profile clas_prof
6 account unlock;
user created.
sql> grant connect to OPS$CLASS;
Grant succeeded.
With the same settings it successfully connects from my 9i client to the 9i database
(DB01, below)
C:Documents and SettingsCLASS_user>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Apr 20 10:18:58 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: /@DB01
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
However it fails to connect to 11G database (DB02, below)
C:Documents and SettingsCLASS_user>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Apr 20 10:19:29 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: /@DB02
ERROR:
ORA-01017: invalid username/password; logon denied
I applied the Jan 09 CPU to my database this past Saturday and now my daily schema export is not running properly:
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in WE8MSWIN1252 character set and UTF8 NCHAR character set server uses UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
[code]...
It seems that dml trigger doesn't fire when lob field is being updated using dbms_lob package. As it stated in Oracle documentation:
Quote:Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not cause Oracle to fire triggers defined on the table containing the columns or the attributes.
I need to know that table was updated (or is about to be updated), how can I do that in case it is lob field that is being updated?
Data Base Configuration assistant for create new database not working in oracle 8.1.7 in windows server 2000 / server 2003 how can i create new database
View 1 Replies View RelatedRPMS needed by OCFS2 for oracle enterprice linux 6.2,Since we unable to mount through OCFS2 console.
View 1 Replies View Relatedi am installing oracle database 8.1.7 on dell server power edge 2650 first time database successfully installed but when i want to crate new database by Database Configuration Assistant it is not working for new database creation.
View 1 Replies View RelatedI'm an absolut Oracle beginner and have problems with loading csv files into an Oracle DB. I get 2 files in the morning, the first one has provisionally information like that:
03.11.2011;BFS;03:00:00;00:29:25;0,00572090;0,22408220;56,71504000;
03.11.2011;BFS;03:30:28;00:30:29;0,00676938;0,29812120;55,06820000;
03.11.2011;BFS;04:04:56;00:28:54;0,00315933;0,15332050;61,68312000;
03.11.2011;BFS;04:33:50;00:59:33;0,00093421;0,04295300;59,57108000;
I load them with sqlload into my table. Table keys are the frist three fields. Now in the second file there are updates in the numbers, f.e. like that:
03.11.2011;BFS;03:00:00;00:27:25;0,00572090;0,22408220;56,71504000;
03.11.2011;BFS;03:30:28;00:31:29;0,00676938;0,29812120;55,06820000;
03.11.2011;BFS;04:04:56;00:29:54;0,00315933;0,15332050;61,68312000;
03.11.2011;BFS;04:33:50;00:60:33;0,00093421;0,04295300;59,57108000;
I can't load the with sqlload, because the program says, the key already exists. My control file looks like that:
load data
infile 'example.csv'
badfile 'example.bad'
append
into table mf_sendung
[code]...
I manage to configure webutil set up on my local pc, jacob jar is working and I can now export data to excel file.
There's a server dedicated to me on linux. I tried to test my form server. There's no error on webutil but the process stops on the line where excel is called. This is the same problem I encountered when jacob.jar is not working on my local drive.
i have a software...i installed it and configured it to the database now i copied the listener file from a working server to this both have same name and configuration and databases with same port number.
but when i try to log in it throws a error
ORA-12154 TNS COULD NOT RESOLVE THE CONNECT IDRNTIFIER SPECIFIED
I checked my listener tns.ora files and all are correct but still i cant connect.
Below is the sample code working fine in 10g and not working now in 11g.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.List;
[code]....
we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class
Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Is RFS an background process.How do I check if its working or not.Can I stop and start RFS.
View 14 Replies View RelatedI had a requirement of loading flatfile into staging table using SQL Loader, One of the columns in the the Flat file is having values FALSE or TRUE and my requirement is that I load 0 for FALSE and 1 for TRUE which can be achieved by simple DECODE function...I did use decode and tried to load several times but did not work.
INFILE 'sql_4ODS.txt'
BADFILE 'SQL_4ODS.badtxt'
APPEND
INTO TABLE members
FIELDS TERMINATED BY "|"
[code]...
I did try putting a trim as well as SUBSTR but did not work....the cloumn just doent get any values in the output (just null or say free space)
I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.
I am using following two queries:
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))
by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.
whats the diff between to_char and || symbol?
The reason "why sql loader's CONTROL FILE is not working, if that location has space in between"
E.g. - C:practice for sqlloadercontrol1.ctl
But it works if we remove the space in between the path as below:
E.g. - C:practice_for_sqlloadercontrol1.ctl
Output in the sqlldr log:-
------------------------------------------------------------------------------
Path used: Direct
Insert option in effect for this table: APPEND
Trigger DEV."R_TM_BK_BORROWER" was disabled before the load.
DEV."R_TM_BK_BORROWER" was re-enabled.
The following index(es) on table "YO"."TM_BK_BORROWER" were processed:
index DEV.I_NK_TM_BK_BORR_1 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_2 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_3 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_31 loaded successfully with 1554238 keys
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 1554241
Total logical records rejected: 48
Total logical records discarded: 2
Total stream buffers loaded by SQL*Loader main thread: 7695
Total stream buffers loaded by SQL*Loader load thread: 0
------------------------------------------------------------------------------
So, I still see in the sqlldr log that the stream buffers are laoded by main thread and load thread is still not being used. SQL*Loader load thread did not offload the SQL*Loader main thread. If the load thread takes care of the current stream buffers, then it allows the main thread to build the next stream buffer while the load thread loads the current stream on the server. We have a 24 CPU server.
I am using the following parameters set to true in the sqlldr:- parallel=true , multithreading=true , skip_index_maintenance=true in the sqlldr