for i in (select * from user_tables)       

 loop
     execute immediate ‘grant select on ‘||i.table_name||’ to a_role_created_for_this’;
   end loop;
 end;

In case you need a pure SQL solution, the best way to go would be to spool the results of the following query and run the script created in grant-all.txt.

SQL> set lin 150

SQL> spool c:\grant-all.txt

SQL>select ‘grant select  to ‘||table_name||’ to “the new user”;’ from all_tables where owner=’the old user;

SQL> spool off

A service specific error occurred: 2.

More help is available by typing NET HELPMSG 3547.

as expected, typing NET HELPMSG will not provide usefull information.

To solve this:

1) Delete and recreate the Enterprise Manager agent.

In the command prompt type> emca -deconfig dbcontrol db

even if this fails, type> emca -config dbcontrol db

You will be asked to provide the database SID, the listener port, password for SYS, DBSNMP and SYSMAN accounts, and some optional settings.

This process will run for several minutes.

2) restart the dbconsole

> emctl stop dbconsole

> emctl start dbconsole

This should solve the issue, however, if you are still unable to log in or you get the following error:

ORA-28001: the password has expired (DBD ERROR: OCISessionBegin), make sure that the SYSMAN account is unlocked

SQL> conn / as sysdba
Connected.
SQL> alter user sysman identified by [new password]
2  /

User altered.
SQL> alter user sysman account unlock;

User altered.
SQL> exit

> emctl stop dbconsole

> emctl start dbconsole

if you still have problems logging in to database control, refer to the following post

What to do when Enterprise Manager is not able to connect to the database instance.

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).

—————————
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.

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.

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.

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

Checking Network Configuration requirements …
Check complete. The overall result of this check is: Failed <<<<
Problem: The install has detected that the primary IP address of the system is DHCP-assigned.
Recommendation: Oracle supports installations on systems with DHCP-assigned IP addresses; However, before you can do this, you must configure the Microsoft LoopBack Adapter to be the primary network adapter on the system.  See the Installation Guide for more details on installing the software on systems configured with DHCP.

As the error message say, this due to the fact that you are trying to install Oracle on a machine that is using DHCP to obtain its IP address. You can still install oracle on a machine without a constant IP address. All you need to do is configure the LoopBack adapter:

Install the Loopback adapter
1)     Go to the control panel and choose add hardware
2)    The Add Hardware wizard will be displayed, click Next
3)    Choose “Yes, I have already connected the hardware” and click Next
4)    An hardware list will be displayed. Navigate to the bottom of the list and choose “Add new hardware       device”
5)    Choose Install the hardware that I manually select from a list (Advance)”
6)    Choose “Network adapter”
7)    Choose “Microsoft” on the left side of the window and “Microsoft Loopback Adapter” on the right             window”
8)    Click Next and verify that Microsoft Loopback Adapter is about to install and click next again and       finish.

Configure the loop back adapter
1)    Go to control panel – network connections, you will see a new network connection.
2)    Choose “local area connection 2” (the name may vary but basically it is the one we just added) and       click properties.
3)    Click on tcp/ip and then properties.
4)    Enter an IP address and a subnet mask (you can use 1.1.1.2 and subnet 255.255.255.0)

Go to network connection and click on Advanced settings under the Advanced menu and verify that the loopback adapter is in the first one. (Update: it look like this step is optional because it seems to work in most cases anyway)

Now you can continue your oracle installation.

ORA-01758: table must be empty to add mandatory (NOT NULL) column

Since the values in the new column are null (after all, you just added the column) the new constraint can not be satisfied.

There are several option to overcome ORA-01758:

1) provide a default value for the column.

ALTER TABLE table-name ADD column-name VARCHAR2(15) DEFAULT ‘X’ NOT NULL

If you don’t want the default value you can remove it after the column will be populated with the new data.

2) Add the column without the NOT NULL constraint,

ALTER TABLE table-name ADD (column-name VARCHAR2(15));

– add the data

UPDATE table-name set column-name=’x';

– apply the NOT NULL  constraint.

ALTER TABLE table-name MODIFY (column-name NOT NULL)

3) Empty the table, apply the NOT NULL and add the data back to the table