Oracle etiketine sahip kayıtlar gösteriliyor. Tüm kayıtları göster
Oracle etiketine sahip kayıtlar gösteriliyor. Tüm kayıtları göster

ORA-06508: PL/SQL: could not find program unit being called

Cause: An attempt was made to call a stored program that could not be found. The program may have been dropped or incompatibly modified, or have compiled with errors.
Action: Check that all referenced programs, including their package bodies, exist and are compatible.

ORA-01722: invalid number

The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

ORA-00932: inconsistent datatypes: expected string got string

Cause: One of the following:

An attempt was made to perform an operation on incompatible datatypes. For example, adding a character field to a date field (dates may only be added to numeric fields) or concatenating a character field with a long field.

An attempt was made to perform an operation on a database object (such as a table or view) that is not intended for normal use. For example, system tables cannot be modified by a user. Note that on rare occasions this error occurs because a misspelled object name matched a restricted object's name.
An attempt was made to use an undocumented view.

Action: If the cause is different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields.
an object not intended for normal use, then do not access the restricted object.

ORA-00257: archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.

ORA-12535 : ORACLE instance terminated. Disconnection forced

Cause: The requested operation could not be completed within the time out period.

Action: Look at the documentation on the secondary errors for possible remedy. See SQLNET.LOG to find secondary error if not provided explicitly. Turn on tracing to gather more information.

Shrink a tablespace in Oracle 10G OEM?

Here are the steps that you can follow to resize a tablespace using Oracle 10G OEM.

1. To log in to Oracle Enterprise Manager Database Control:

Open your Web browser and enter the following URL


In a default installation, the port number is 1158. If you are unsure of the correct port number to use, look for the following line in the ORACLE_BASE\ORACLE_HOME\install\portlist.ini file:

Enterprise Manager Console HTTP Port (db_name) = port

For example, if you installed the database on a host computer named mgmt42, and the port number listed in the portlist.ini file is 5500, then enter the following URL

Enterprise Manager displays the Database Control Login Page.

Log in to the database using the SYSMAN database user account. Enterprise Manager displays the Oracle Database home page.

Use the password you specified for the SYSMAN account during the Oracle Database installation.

2. Select/click on Administration link > then on storage link on the next page and select tablespace

3. Click on datafile name then "Edit".

Enter new datafile size in MB or KB and click on apply when finished.

Retreiving data from backup of crashed Oracle

If you you have a hot backup set of all data file, control file, archived log file, do the following:

1. Create the new directories (same or different hiarachy)
2. Copy all datafiles, archived log and init files from backup set to those dirs.
3. Modify the file was created from "backup control file to trace" command. This script will be used to recreate the control file
4. Startup nomount then run the script to recreat the new control file
5. Issue:
SQL>recover database
Oracle will ask you for the archived log, apply until cancel or turn on AUTO to let Oracle find the archived log file by itself
6. Alter database open

ORA-01400: cannot insert NULL into (string)

Cause: An attempt was made to insert a NULL into the column "USER"."TABLE"."COLUMN".

For example, if you enter:

connect scott/tiger create table a (a1 number not null); insert into a values (null);

Oracle returns:

ORA-01400 cannot insert NULL into ("SCOTT"."A"."A1") : which means you cannot insert NULL into "SCOTT"."A"."A1".

Action: Retry the operation with a value other than NULL.

ORA-12154: TNS:could not resolve the connect identifier specified

Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.

Action: - If you are using local naming (TNSNAMES.ORA file):

- Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)

- Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.

- Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.

- Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.

- If you are using directory naming:

- Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

- Verify that the LDAP directory server is up and that it is accessible.

- Verify that the net service name or database name used as the connect identifier is configured in the directory.

- Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier

- If you are using easy connect naming:

- Verify that "EZCONNECT" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

- Make sure the host, port and service name specified are correct.

- Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.

ORA-00604: error occurred at recursive SQL level string

Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).

Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.

ORA-01017: invalid username/password; logon denied

Cause: An invalid username or password was entered in an attempt to log on to Oracle. The username and password must be the same as was specified in a GRANT CONNECT statement. If the username and password are entered together, the format is: username/password.

Action: Enter a valid username and password combination in the correct format.

ORA-12638: Credential retrieval failed

Cause: The authentication service failed to retrieve the credentials of a user.

Action: Enable tracing to determine the exact error.

ORA-12838: cannot read/modify an object after modifying it in parallel

ORA-12838: cannot read/modify an object after modifying it in parallelIs it a must that i should commit immediately After that delete inorder to avoid that above error.


It is a limitation of parallel DML .

Once a table has been modified by a parallel DML statement, that table cannot be accessed in a subsequent SQL statement (serial or parallel, DML or query) in the same transaction before the transaction is committed.

Please check the following link for other limitations.

So you either have to remove the parallel execution, or commit after the delete.

BTW, if you are deleting every row, it's better to truncate the table. Be aware though that truncate is DDL therefore implies a commit in it.

How can I list Locked and inactive sessions in Oracle?

SELECT c.owner, c.object_name, c.object_type,
fu.user_name locking_fnd_user_name,
fl.start_time locking_fnd_user_login_time, vs.module, vs.machine,
vs.osuser, vlocked.oracle_username, vs.SID,,
vp.spid AS os_process, vs.serial#, vs.status, vs.saddr, vs.audsid,
FROM fnd_logins fl,
fnd_user fu,
v$locked_object vlocked,
v$process vp,
v$session vs,
dba_objects c
WHERE vs.SID = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || UPPER ('&tab_name_leaveblank4all') || '%'
AND NVL (vs.status, 'XX') != 'KILLED'; --<-change it

Query execution time in Oracle?

Oracle 10g has R1 and R2 (r stands for Release).
I hope you have installed R3 (10.2.0.x)

To sped up the processing you have to do 2 important things:

1. To investigate and possibly to increase the size of SGA components - db_buffer_cache, shared_pool_cache

2. To run the statistics package (as SYS):

END compute_statistics;

Now either run:

execute compute_statistics;

or schedule once weekly:

set serveroutput on
set linesize 200000
variable x number;

How to find last executed queries in Oracle?

select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
, v$session b, v$process c
where a.address = b.sql_address
and a.username='&username'
and b.paddr = c.addr
and a.hash_value = b.sql_hash_value
order by c.spid,a.hash_value,a.piece

Export and import to Oracle

Prerequisites/Requirements :

exp and imp are utilities present in the $ORACLE_HOME/bin directory and are installed when Oracle is installed. Their prime purpose is to move logical objects out of and into the database respectively - for example dumping all of the tables owned by a user to a single file is achieved using the exp utility. It is important to distinguish between dumping data in this manner and backing up the database which is normally achieved using the rman utility.
Starting with Oracle 10g these two utilities are deprecated and the Oracle recommended alternatives are the data pump versions of these utilities which provide a number of new features including the ability to disconnect and reconnect to an interactive session (so a direct logon to the server isn't required) and improved performance. There are still a number of things that cannot be achieved with the data pump utilities - in particular exporting/importing across a named pipe which is a technique used by database administrators when the export file needs to be compressed on the fly or when one wishes to export directly to an import session.

Before executing these commands, the environment should be set correctly for the user in particular if the examples used on this page are followed the $ORACLE_HOME, $ORACLE_SID and $PATH environment variables need to be set appropriately in a unix environment and the %ORACLE_SID% environment variable needs to be set in a windows environment. If you login as the owner of the oracle software (usually the oracle user on unix) these are likely to have been set in the users profile. The Oracle Database instance needs to be up in order to export/import data. Type 'imp help=y' or 'exp help=y' for a detailed explanation of the available options for these utilities. Some brief examples follow to illustrate the usage of these utilities.

Using exp:

To export the entire database to a single file dba.dmp in the current directory.

- Login to server

exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y

exp SYSTEM/password PARFILE=params.dat

where params.dat contains the following information


To dump a single schema to disk (we use the scott example schema here)

- Login to server which has an Oracle client

exp / FIlE=scott.dmp OWNER=scott

To export specific tables to disk:

- Login to server which has an Oracle client

exp SYSTEM/password FIlE=expdat.dmp TABLES=(scott.emp,hr.countries)
-the above command uses two users : scott and hr

exp / FILE=scott.dmp TABLES=(emp,dept)
the above is only for one user

Using imp:

To import the full database exported in the example above.

imp SYSTEM/password FULL=y FIlE=dba.dmp

To import just the dept and emp tables from the scott schema

imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp)

To import tables and change the owner

imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager)

To import just the scott schema exported in the example above

imp / FIlE=scott.dmp

If you do not supply any parameters then you enter an interactive session as illustrated below.

Import: Release - Production on Thu Mar 29 15:07:43 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: SYSTEM
Password: password
Connected to: Oracle9i Enterprise Edition Release - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release - Production
Import file: expdat.dmp > /mention/path/of/dumpFile/includingFileName.dmp
Enter insert buffer size (minimum is 8192) 30720> (press enter to accept default)
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
List contents of import file only (yes/no): no > press enter
Ignore create error due to object existence (yes/no): no > press enter
Import grants (yes/no): yes > press enter
Import table data (yes/no): yes > press enter
Import entire export file (yes/no): no > press enter or type no
Username: give the userName for which you want the data to be imported
Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: press enter
. importing TST_001_V2's objects into TST_001_V2

Good practices

Always take care about CHARSETS when you do export and import. Using the wrong ones can convert your data in a lossy manner. The best situation is when your source and destination database have the same character sets, so you can avoid completely any character conversion. You control this behaviour by setting NLS_LANG environment variable appropriately. When not set properly you may see 'Exporting questionable statistics' messages.
After doing an export, it is better to check your dump by doing an import with the parameter SHOW=Y. This checks the validity of your dump file.

Other considerations

You may need to patch your Oracle client (where you are running exp/imp) to the same level as the Oracle server to prevent errors
When importing large amounts of data consider dropping indexes prior to the import to speed up the process and re-creating them once the import is completed
The amount of archivelogs that may be created on a large import may fill up your disk
On INSERT triggers will fire, consider whether these need to be disabled
Increasing the RECORDLENGTH (max 65535) parameter can improve the length of time to perform an import/export as well as DIRECT=y for export


SQL Server vs MySQL vs Oracle vs PostgreSQL

As far as features, performance and power goes, Oracle is leader of the pack clearly. There are more features and tools available for Oracle than for any other DB. However, you will be paying, and paying dearly. You will be able to rest assured that your DB system is the best that money can buy.

Now in most cases, money *is* an object, and as such tradeoffs must be made. In my opinion, PostgreSQL is just below Oracle for powerand features. In fact, there are some things now that PGSQL even do better than Oracle especially in terms of transaction concurrency, PGSQL's MVCC (Multi Version Concurrency Control) model is widely accepted as beign the best conscurrency handling system there is. And its catching up. PGSQL version 8 is due out any day now, there are huge performance increases and features being added in the new version such as native Windows support (it will now install and run on any Win32 system including NT, 2000, XP and 2003), replication in the Slony-I project which brings PGSQL's scaleability to Oracle's calibre.

MySQL is a non-fitter in my opinion. It would be a great product, but since PGSQL's performance has taken great steps in the last few years there is little room for this between PGSQL and SQLite for MySQL to fit in. (SQLite is an ultralightweight, ultrafast SQL database that is embeddable and can be used for small simple projects, and is blisteringly fast in such uses.) MySQL has a *huge* following, and I'm risking a lynching bad mouthing it here, but I personally feel that it is a has-been product with benefits that were once leaders of the pack but have since been overtaken by superior products, namely PostgreSQL and SQLite.

MS SQL is a good product to work with if you're a die hard Windows and Microsoft user and are familiar enough with the other software that goes with it, and are comfortable paying large amounts of money for licences. Personally, with far superior open source options, I feel that MS SQL is a silly choice unless you have your hands tied by other considerations such as licencing constraints or compatibility with other MS products.

Finally, my overall view is that I use SQLite for small projects such as web polls and guestbooks where only the standard SQL commands are sufficient and there is no need for transactions, row/column/table locking, stored procedures and the like. I use PostgreSQL for large projects requiring transactions, stored procedures and with replication I cannot see myself involved in anything too big for PG. In fact I can't think of anything outside genetics and other highly specialised fields where PG would not suffice.

Finally, PG is distributed under the BSD licence, which means there is no chance of licencing compliance issues in your application, and you are guaranteed that there will never be any costs associated with the use of the DB itself.

ORA-06502: PL/SQL: numeric or value error string

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

ORA-04932: increment or adjust of sequence number failed

Cause: A call to the SSN failed to increment the sequence number.

Action: Verify that the MC hardware is functioning properly. If it is not, contact Digital"s customer support organization. If it is, contact Oracle support.

Bilgisayarın çıkış yaptığı internet dış IP'yi bulmak ( işlevini görür)

Kullanılan sistemlerde çıkış yaptığınız IP'yi kontrol etmek için aşağıdaki prosedür kullanılabilir. Bu  a...