If you are trying to use the oracle net configuration assistant in order to create tnsnames.ora entry on windows 7, you may notice that sometimes the net service name is simply not created. Even after you run through the wizard successfully you may get the famous “ORA-12154 – TNS could not resolve service name” when you actually try to use it. Another symptom would be that when you run the net configuration assistant again, the entry you just added will not be there.

This issue is caused by the change in Windows 7 default permissions. Windows 7 has the “Administrator” account disabled by default right out of the box. During the Windows 7 setup, the user is prompted for a username that has administrator rights and is in the administrator group. Running the net configuration assistant as the “administrator” user does not guarantee that this user has the administrator’s rights. This probably prevents the Network Configuration Assistant from editing the tnsnames.ora therefore the changes are not saved.

A simple solution to this issue is to run the network configuration assistant as an administrator.

1.  Press and hold Ctrl+Shift while opening the program. Alternatively, you can navigate to the program shortcut (or the actual exe file), right click and then click on Run as administrator.
2. If prompted by the UAC you should click on Yes to apply permission to allow the program to run with full permission as an Administrator (You may need to provide the administrator password if you are logged in as a standard user).

Sometimes, when you try to shrink a data file you may get the following message:

TITLE: Microsoft SQL Server Management Studio
——————————
Shrink failed for Database ‘Data base name’.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+Database&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
A severe error occurred on the current command.  The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

First, you should know that shrinking data files is not recommended and will hurt performance. Shrinking data files will cause index fragmentation. You can recreate the indexes but it will require the freed space. Shrinking will also cause fragmentation in the server’s file system which will slow it even more. In addition, since every page move is logged to the transaction log, chances are that the transaction log will claim the same space.

However, if in case you decide to shrink the database anyway (for example, after a large and permanent delete, or in case it is a test system) a common reason for this error is lack of space.
Since many times the attempt to shrink a database will come after discovering that the drive is running out of space, it would only make sense that there is not enough space for the shrinking process itself.

To verify, open the sql server error log (usually under Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG) and look for Operating system error 112(There is not enough space on the disk.).

To solve, you should clear some space to allow the process to complete. If you can not free some disk space, Try to shrink the transaction log and tempdb.

over time, the Listener.log file can grow to huge size and take a lot of disk space.
If you will try to delete it in windows you may get the following error:

—————————
Error Deleting File or Folder
—————————
Cannot delete listener: It is being used by another person or program.

Close any programs that might be using the file and try again.
—————————
OK
—————————

Linux will allow you to delete the file but it will not create a new one.

The solution to this issue is simple. following are some options:

The easiest solution would be to stop the listener process, delete the file and restart the listener service. However, stopping the listener will prevent new connections to the database and disrupt service.

A better option would be to stop logging to the listener.log file:

In the command prompt type >lsnrctl
This will open the Listener control utility. Type LSNRCTL> set log_status off
now you can navigate to the file location and delete the file using the operating system.
This will prevent the file from growing in the futre.
If you like, you can restart logging LSNRCTL> set log_status on
This will allow you to delete listener.log but nothing will be logged when the log_status is set to off.

If you want to the logging to continue. you can switch to a different logging file:
type LSNRCTL> set log_file listener2.log

The listener will now log to the new file. you can delete or move the old file.

sometimes, when running a big transaction, the transaction log can become full or simply grow to a huge size . while you can sometimes free some space using “backup log db-name with truncate_only” and trying to shrink the DB using SQL Management studio.

The process is described in the above link but basically it is: right click on the Database name ->tasks->shrink->files and choosing the log file.
This will shrink the db if there is free space in the transaction log but it can only serve as a temporary solution.

If the transaction is still running and you are afraid that the hard drive will run out of disk space, a better solution would be to create another transaction log on a different drive. This will solve the disk space issue and might even improve performance.
To create another transaction log file  using the SQL Server Management Studio:

1) Expand the DataBases node and choose your Database
2) Right Click your Database and click Properties.
3) Select the Files Page
4) CLick ADD and Navigate to te Database files grid.
5) Enter a name for the new log file. This name must be unique within the database.
6) Select the Log file type
7) select initial size, growth method (auto Growth, fixed Increments or percent). you can also set a maximum size. Set up the path to the new file and file name and Click OK

In Some cases, the file name column might be disabled when adding a log file using the Sql Server Mangment Studio while the transaction is still runing.
In this case you can add the file manually using a query.
Open a query editor and type:

USE master

GO

There are better ways to avoid the Transaction log is full situation and this should be prevented in the the first place by the code but if you got no access to it, this might be a good solution.

ORA-00980: synonym translation is no longer valid
This error usually means that the synonym used is based on a table, view, or synonym that no longer exists. in order to fix it you will need to replace the synonym with the name of the object it references or re-create the synonym so that it refers to a valid table, view, or synonym. However, this is not always simple. following are some common causes and suggested solutions.

1) If you imported a specific schema, it is possible that one of the sysnonyms is pointing to an object on a different schema that is simply not there. run SELECT * FROM all_synonyms and verify that all the table name are there and reachable.
2) If you imported from a different version it is also possible that that the synonym is pointing to an object that does not exists in the new version. This could be due to the fact that this component was not installed on the new version or that the component is not in a valid state.
SELECT comp_name, version,status FROM dba_registry and make sure that all the components are valid.
3) it is also possible that object is there but you don’t have access to it. connect as system and do the SELECT * FROM dba_synonyms WHERE owner=’YOUR USER”. again, make sure that your user have access to all the objects under TABLE_NAME. If not, grant it.
4) if this issue happens during import or export, add trace=y to the imp/exp command (imp user/pass file=filename.dmp trace=y) This will create a trace file that will provide more information on what is failing.

If you are using SQL Profiler and you want to know which workstation is using the db you should use the hostname column in sql profiler.
While this was relatively straight forward in SQL Server 2000, users of SQL Server 2005 may find that the hostname column is not displayed when running the SQL Profiler. By default, SQL Server 2005 is hiding this column (and others) and if you want to see it or filter by it, you should activly choose it.
In order to display the hostname column in SQL Server Profiler you should:

1) Open SQL Server Profiler: SQL Server Management Studio -> Tools –> SQL Server Profiler
2) Start a New Trace:  File –> New Trace
3) Connect to your server.
4) Go to the “Events Selection” tab.
5) Check the “Show all columns” box
6) Scroll to the hostname column
7) Check the event you want to trace in the hostname column.

In order to filter the results, you can click “Column filters” and choose the hostname on the left panel. This will allow you to set a “Like” or “Not Like” filter in the right panel.

If you are trying to install Oracle 10.2.0.3 client on a Windows 2008 server and you get the following error:

OUI.EXE has stopped working
Close the program

For some reason, the Oracle installer is having problem with long directory structures.
To overcome this, you can simply copy the installtion directory to a location with a shorter path.

1)create a new Temp directory on the root of your drive
2)copy the installtion files to this directory. it is possible that windows will say that ” you need permission to perform this action”. This could be overcome by turning off the User Account Control (UAC) but a simpler soulution would be to copy the files using the command promptmove “10203_vista_w2k8_x86_production_client” c:\temp\ (weird, but it works)
3) run the installer from the new location.

When using Data Pump, you may encounter the following error:
ORA-39171: Job is experiencing a resumable wait.
Together with

ORA-01653: Unable to extend table %s.%s by %s in tablespace %s
Or
ORA-01691  unable to extend lob segment string.string by string in tablespace string
Both errors basically say that the table space you are using can not allocate more space. This could be because the datafile is full, the disk is full or autoextend is not set. This is simple to check by running the following query on DBA_DATA_FILE:
select *
from dba_data_files
where tablespace_name=’TABLESPACE_NAME’

The bytes column will tell you how many bytes are used by Oracle in the tablespace. If AUTOEXTENSIBLE is set to NO, then Oracle will not extend the size of the tablespace.

In many cases, the Data Pump import may seem to hang. Data Pump will try to continue after some time but unless something is changed, you will keep getting the above errors.

The solution, however, is simple enough. All you need to do is use the Data Pump Restart Capability:
1)    In the IMPDP window, click CTRL-C to stop the job.
2)    In the command line type:
Import> stop_job=immediate
3)    Use SQLPlus to make the required changes to the table space. You can:

a)    Add DataFile ALTER TABLESPACE <tablespace name> ADD DATAFILE ‘<path to file name and  file name>’ SIZE <integer>M;

b)    Resize the Datafile:
ALTER DATABASE DATAFILE ‘< path to file name and  file name >’ RESIZE <integer>M;
c)     Enable autoextend:
ALTER DATABASE DATAFILE ‘< path to file name and  file name >’ AUTOEXTEND ON MAXSIZE UNLIMITED;

4)    Attach the Job.
>impdp system/manager attach=Job_Name
If you did not provide a specific job name when you first ran the IMPDP, Oracle will assign a default name for it. In order to find the System assigned name for the IMPDP job you can run the following query:
SELECT * FROM DBA_DATAPUMP_JOBS;
The result will probably be something like SYS_IMPORT_FULL_number.
5)     Restart the job
Import> start_job
6)    You can check the status of the job by simply typing STATUS in the utility command prompt.
Import> status

If you try to import a file and you get the following error:

IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully
The cause according to Oracle error description is “Either the file was not generated by Export or it was corrupted”. The first obvious reason might be that the file is corrupted. However, if you downloaded it correctly (using binary transfer in the FTP as opposed to ASCII transfer) and you have no other reason to believe that the file is indeed corrupted. You might want to explore other options.
The most common reason for this error is mismatch of IMP and EXP versions. While it should not be a problem to import a file that was created with an earlier version of EXP using a newer version of IMP, trying to use it the other way will generate the above error. In other words, you can import a file that was created with 10g version of EXP using a 9i version of IMP. If this is the case, try to export the file using a lower version of EXP or try to import the file to a higher version Database. Later you can export it again using a lower version of the EXP utility.
Another reason, over looked many times, is that the file was exported using Data Pump (EXPDP not EXP)  . If you are not the one who exported the file, try to restore it using the Data Pump import utility IMPDP. Since Data Pump is gaining traction and it is creating .dmp files it is possible that the dmp file in your hands was created using it.

Even Oracle veterans may encounter some challenges when it comes to setting an odbc  using the Oracle XE Client.    As every DBA will tell you, you will need to configure the tnsnames.ora  (using  net configuration Assistant or by going  to oracle home\network\admin and edit it manually). After that, theTNS Service name in the ODBC will be populated with the entries from the tnsnames.ora file. You will only be required to choose the appropriate entry and you should be good to go.
This was the case in all the latest Oracle releases However If you will download the Oracle XE client you will quickly discover  that there is no tnsnames.ora under the Oracle client XE directory structure. In addition, the network\admin directory is not there as well.
Oracle documentation explains (In their usual user friendly way) how to connect to the Data Base using a connection string (username/password@[//]host[:port][/service_name] where service name is usually XE and port is 1521) but it is not that obvious how to configure the odbc to use this.
The solution is simple enough (once you find it). All that needs to be done is to type  the connection string  on the ODBC’s “ TNS Service Name” field.  Just add the line 127.0.0.1/XE (no need to use the drop down) and test the connection.
If the Data Base is on a different machine, you can replace 127.0.0.1 with the address of the remote server.