I could not connect to OEM 11g, from the client machine. Although it was running on the server. Neither could I connect to it using sql developer.
Reason : Firewall on centos 6
Steps to disable firewall on centos 6
# service iptables save
# service iptables stop
# chkconfig iptables off
Steps to enable firewall on centos 6
# service iptables start
# chkconfig iptables on
Query to get object name, and type. Here recompile_order decides the order in which the objects will be compiled.
SELECT object_name, object_type,
DECODE(object_type, ‘VIEW’,1,’PROCEDURE’,2,’FUNCTION’,3,’PACKAGE’,4) AS recompile_order
FROM USER_OBJECTS
WHERE object_type IN (‘VIEW’,’PROCEDURE’,’FUNCTION’,’PACKAGE’)
ORDER BY recompile_order;
BEGIN
FOR cur_rec IN (SELECT object_name, object_type,
DECODE(object_type, ‘VIEW’,1,’PROCEDURE’,2,’FUNCTION’,3,’PACKAGE’,4) AS recompile_order
FROM USER_OBJECTS
WHERE object_type IN (‘VIEW’,’PROCEDURE’,’FUNCTION’,’PACKAGE’)
ORDER BY recompile_order )
LOOP
EXECUTE IMMEDIATE ‘ALTER ‘ || cur_rec.object_type ||’ ‘|| cur_rec.object_name || ‘ COMPILE’;
END LOOP;
END;
SELECT SYS_CONTEXT(‘userenv’,’db_name’) AS INSTANCE_NAME, SYS_CONTEXT(‘userenv’,’session_user’) AS SCHEMA_NAME FROM DUAL;
select instance_name,host_name from v$instance;
I executed the following block :
Here the procedure is on another database, and it is accessed using database link.
SET SERVEROUTPUT ON
DECLARE
OUT_STATUS VARCHAR2(50);
V_ID VARCHAR2(30);
BEGIN
PACKAGE_NAME.PROCEDURE_NAME @ RPC_DBLINK ( V_ID, OUT_STATUS) ;
DBMS_OUTPUT.PUT_LINE( V_DECL_NO || ‘ : ‘ || OUT_RMS_STATUS);
END;
Following error occurred while executing database procedure over database link.
ORA-02064: distributed operation not supported
ORA-06512: at “”, line 239
ORA-06512: at line 9
02064. 00000 – “distributed operation not supported”
*Cause: One of the following unsupported operations was attempted
1. array execute of a remote update with a subquery that references
a dblink, or
2. an update of a long column with bind variable and an update of
a second column with a subquery that both references a dblink
and a bind variable, or
3. a commit is issued in a coordinated session from an RPC procedure
call with OUT parameters or function call.
*Action: simplify remote update statement
Solution :
In my case : Third cause was the reason for the problem. The remote procedure was performing update and commit, and also it has out parameter.
I made the remote procedure as Autonomous Transaction using PRAGMA AUTONOMOUS_TRANSACTION.
For ex :
PROCEDURE PROCEDURE_NAME ( IN_ID IN VARCHAR2, OUT_STATUS OUT VARCHAR2 )
AS
–Creates autonomous transactions.
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
–STATEMENTS;
END;
select count(*) from user_tables; –where table_name like ‘DMRS%’;
select count(*) from user_views; –where view_name like ‘DM%’;
/* Anonymous block to delete multiple tables*/
SET SERVEROUTPUT ON
DECLARE
CURSOR CUR_TABLIST IS
–SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE ‘MSWORD%’;
select VIEW_NAME from USER_VIEWS where VIEW_NAME like ‘MGV%’;
BEGIN
FOR REC IN CUR_TABLIST LOOP
EXECUTE IMMEDIATE ‘DROP VIEW ‘||REC.VIEW_NAME;
–DBMS_OUTPUT.PUT_LINE(‘DELETED TABLE ‘||REC.TABLE_NAME);
DBMS_OUTPUT.PUT_LINE(‘DELETED TABLE ‘||REC.VIEW_NAME);
END LOOP;
END;
Error Log : /u01/app/oracle/product/11.2.0/db_1/ECVSSERVER_ECVS/sysman/log/emoms.log
Following error was found in the log file :
getFormattedDateWithTZ.441 – Invalid region name format: GMT+03:00
Solution :
emctl stop dbconsole
emctl resetTZ agent
Now start the em
emctl start dbconsole
Consider table Temp with a unique constraint. Now you drop the constraint, and try to insert a record with existing value of the field on which the constraint was defined.
This may lead to Unique constraint violation, if their exists Unique “Index” with the same name as the constraint name.
So, drop the unique index and then try to insert.
Section 1 : How to enable archive logging in oracle 11g
Method 1 : Using Alter system command
ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest=’/u01/oradata/MYSID/archive/’ SCOPE=spfile;
ALTER SYSTEM SET log_archive_format=’arch_%r_%t_%s.arc’ SCOPE=spfile;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ARCHIVE LOG START;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
archive log list
alter system switch logfile; –> This will create archive logfile
Method 2 : By modifying the parameter file.
create pfile=’/tmp/mypfile.ora’ from spfile;
vi mypfile.ora
Add following lines to the mypfile.ora
log_archive_start=TRUE
log_archive_format = ‘arch_%r_%t_%s.arc’
log_archive_dest = <localtion> #the location must be accessible to oracle.
shutdown immediate
startup nomount pfile =’/tmp/mypfile’ora’
cd $ORALCE_HOME and check there is any spfile<SID>.ora file exist or not
if yes then take the backup
create spfile = ‘oracle_home/database/spfile<sid>.ora’ from pfile =’/tmp/mypfile’ora’
shutdown immediate
startup
archive log list
Section 2 : Steps to implement Logminer
Step 1 : Enable Archive Logging as explained in Section 1.
Step 2 : Insert some values in the mytest table.
SQL> insert into mytest values(&id,’&name’);
Enter value for id: 1
Enter value for name: asd
old 1: insert into mytest values(&id,’&name’)
new 1: insert into mytest values(1,’asd’)
1 row created.
SQL> /
Enter value for id: 2
Enter value for name: qwer
old 1: insert into mytest values(&id,’&name’)
new 1: insert into mytest values(2,’qwer’)
1 row created.
SQL> /
Enter value for id: 3
Enter value for name: zxcv
old 1: insert into mytest values(&id,’&name’)
new 1: insert into mytest values(3,’zxcv’)
1 row created.
Step 3 :
SQL> commit;
Step 4 : Create the archive log file immediately.
SQL> alter system switch logfile;
===============================================================================
Step 5 : Create a user with role, which will be given all the privileges to use log miner.
SQL> create role logmnr_admin;
SQL> grant create session to logmnr_admin;
SQL> grant select on v_$logmnr_contents to logmnr_admin;
SQL> grant select on v_$logmnr_parameters to logmnr_admin;
SQL> grant select on v_$logmnr_logs to logmnr_admin;
SQL> grant select on v_$archived_log to logmnr_admin;
SQL> grant execute_catalog_role, select any dictionary, select any transaction, select any table, create tablespace, drop tablespace to logmnr_admin;
SQL> create user miner identified by miner;
SQL> grant logmnr_admin to miner;
SQL> alter user miner quota unlimited on users;
Step 6 : Enable Supplemental logging.
SQL> alter database add supplemental log data;
http://docs.oracle.com/cd/B19306_01/server.102/b14215/logminer.htm#i1021068
Step 7 : Ensure that supplemental logging has been enabled.
SQL> select supplemental_log_data_min from v$database;
The result of above query should be YES.
Step 8 : Specify a LogMiner dictionary. ( http://docs.oracle.com/cd/B19306_01/server.102/b14215/logminer.htm#i1015913)
In this step, their are three options :
a) Using the Online Catalog
b) Extracting LogMiner dictionary to Redo Log files.
c) Extracting LogMiner dictionary to a Flat file.
Steps for Scenario 8(a) : Online catalog can be directly specified in the subsequent steps when we start the log miner.
Step for Scenario 8 (c) :
i) Create a directory where dictionary file will be created.
mkdir /home/oracle/logmnr
ii) Set the utl_file_dir parameter. This is the location where dictionary file will be created.
SQL> alter system set utl_file_dir=’/home/oracle/logmnr’ scope=spfile;
iii) Restart the database.
iv) Execute DBMS_LOGMNR_D.BUILD(‘dictionary.ora’, ‘/home/oracle/logmnr’, options => dbms_logmnr_d.store_in_flat_file);
Note : /home/oracle/logmnr should be same as utl_file_dir
Step 9 : Find the archive log (created in step 4 of Section 2) that you want to use to mine.
SQL> SELECT name, TO_CHAR(first_time, ‘DD-MON-YYYY HH24:MI:SS’) first_time
FROM v$archived_log
WHERE name IS NOT NULL AND first_time BETWEEN TO_DATE(’20-MAR-2013 08:00:00′, ‘DD-MON-YYYY HH24:MI:SS’)
AND TO_DATE(’20-MAR-2013 10:59:00′, ‘DD-MON-YYYY HH24:MI:SS’)
ORDER BY sequence#;
Step 10 : Add log files to the logminer for mining.
In this step their are two ways of adding log files.
a) Manually adding each file.
b) Automatically.
Steps for Scenario 10(a) :
i) Execute dbms_logmnr.add_logfile(LogFileName=>’/u01/oradata/MYSID/archive/arch_803914076_1_22_.arc’, Options=>dbms_logmnr.NEW); –Adds first file.
Execute dbms_logmnr.add_logfile(LogFileName=>’/u01/oradata/MYSID/archive/arch_803914076_1_23.arc’, Options=>dbms_logmnr.ADDFILE); –Adds second file.
And so on….
Steps for Scenario 10(b) :
i) Files can be automatically added using the CONTINUOUS_MINE option of the start_logmnr procedure, which is explained in next step (Step 11).
Step 11 : Start LogMiner.
Follwing are the different ways of starting logminer based on dictionary used :
a) If Flat file is used for LogMiner dictionary ( Section 2 : Step 8 : Scenario (c))
i) Execute DBMS_LOGMNR.START_LOGMNR(dictfilename => ‘/home/oracle/logmnr/dictionary.ora’); — uses the added log file for mining.
b) If Online catalog is used ( Section 2 : Step 8 : Scenario (a))
i) Execute DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); — uses the added log file for mining.
c) Using CONTINUOUS_MINE
i) Alter session set NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24:MI:SS’;
Execute DBMS_LOGMNR.START_LOGMNR(STARTTIME => ’19-MAR-2013 14:02:14′, ENDTIME => SYSDATE, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE ); –uses the log files between specified times and performs continuous mine.
Step 12 : Request the Redo data of interest.Query the V$LOGMNR_CONTENTS view. (You must have the SELECT ANY TRANSACTION privilege to query this view.)
SQL> SELECT username, operation,
DBMS_LOGMNR.MINE_VALUE(REDO_VALUE,’SCHEMA.TABLENAME.COLNAME’) REDO VALUE,
DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE,’SCHEMA.TABLENAME.COLNAME’) UNDO VALUE,
sql_redo,
sql_undo,
TO_CHAR(timestamp, ‘DD-MON-YYYY HH24:MI:SS’) timestamp,
scn
FROM
v$logmnr_contents
WHERE
username = ‘SCOTT’
AND operation = ‘INSERT’
AND seg_owner = ‘SCOTT’;
Step 13 : Stop the miner
SQL> DBMS_LOGMNR.END_LOGMNR();
http://docs.oracle.com/cd/B19306_01/server.102/b14215/logminer.htm#i1015913
Section 1 : Integrating SQL developer with SVN
Step 1 : Create a svn repository
url : http://ipaddress/svn-repos/project-repos
Step 2 : In sql developer,
choose, View > Team > Version Navigator
Step 3 : In Version Navigator,
Right click on Subversion, and select New Repository Connection,
Provide the following details,
Repository URL -> http://ipaddress/svn-repos/project-repos
Connection Name -> Any user defined name.
User Name -> User name of user who is allowed to login into the repository.
Password -> Password of the above user.
Step 4 : Click Test Read Access, to ensure that connection to repository was successful.
Section 2 : Adding Files to repository.
Initially the repository (project-repos) is empty.
To add files to the repository follow the steps below :
Step 1 : Choose View > Files.
Files tab will appear in Sql developer.
Step 2 : In the Files tab, expand My Computer and select the folder which contains the files to be added.
Step 3 : Choose Versioning > Import Files
Follow the six steps of the import files wizard, by selecting destination(svn repository), source(your hard disk files), filters, options.In step 5 of the wizard (options step), check the “Perform Checkout” check box.
Finally click Finish. All the files of the selected folder (step 2) will be imported to svn repository.
Step 4 : In the versioning Navigator, select the connection name for the repository and click refersh button.
All the files that you have added will appear with their version number in brackets.
Section 3 : Accessing the repository.
Till now the repository has been created and files have been imported to repository.
Now other users will need to access the repository, make, update, changes to repo.
Step 1 : Create the connection to repository as shown in section 1.
Step 2 : In Version Navigator,expanding the connection name will show all the files imported in Section 2.
Step 3 : Under the connection name select the folder containing the files.
Step 4 : Rt-click the folder and select Check-out.
Step 5 : Select the destination folder where you want the files to created on your hard drive.
Step 6 : Click ok.
Step 7 : Go to Files tab and click refresh.
Step 8 : Expand the tree and look for the folder the you selected in Step 5. This folder will now contain all the files that you have exported from the repository to your hard drive.
Section 4 : Making changes to files and committing changes to repository.
Any user can make changes to files (files that he has checked out(exported) from repository in section 3 or he has added to repository in section 2).
Step 1 : From the Files tab, go to the folder that you have imported/exported to/from the repository.
Step 2 : Select the file to modify,by double-clicking.
Step 3 : File will open in edit mode. Make the required changes and save it. After you save the file, an asterik(*) will appear near the name of file in Files tab.
Step 4 : Rt-click the file/folder that contains modified files, and choose Versioning > Commit.
Step 5 : In the dialog box, give your comments for the changes and click Ok.
When the commit is successfull, version number of the file will increase by one. All the changes made are now saved in the repository and other users can take these changes as explained in next Section.
Section 5 : Updating changes from the repository.
If a file has been changed and committed by any other user, you can take update local file from the repository version of the file.
Step 1 : In the Files tab, select the file/folder to update.
Step 2 : Rt-click and choose Versioning > Update.
Step 3 : In the dialog box, choose required options and click ok.
Now the file on your hard drive is in sync with the repository file.
Continue statement in FOR loop, iterating over a cursor skipping the complete loop (Not just that iteration).
Scenario :
Consider the given for loop :
SET SERVEROUTPUT ON
DECLARE
BEGIN
FOR REC IN (SELECT EMPLOYEE_ID, SALARY FROM EMPLOYEES ORDER BY SALARY)
LOOP
IF REC.SALARY = 500
THEN
DBMS_OUTPUT.PUT_LINE('Record Skipped with id = ' || REC.EMPLOYEE_ID);
CONTINUE;-- INNERLOOP;
END IF;
DBMS_OUTPUT.PUT_LINE(REC.EMPLOYEE_ID ||' : '|| REC.SALARY);
END LOOP;
END;
Expected Output :
Only the record with salary 500 should be skipped.
Actual Output :
When the equality cond. is satisfied, it skips 100 records (Unexpected). Might be that all the cached records are skipped.
Solution :
SHOW PARAMETER PLSQL_OPTIMIZE_LEVEL;
NAME TYPE VALUE
plsql_optimize_level integer 2
Set the value of this parameter to 1.
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;
It worked fine, but not alwalys.
So, I used another option USING GOTO STATEMENT:
SET SERVEROUTPUT ON
DECLARE
BEGIN
FOR REC IN (SELECT EMPLOYEE_ID, SALARY FROM EMPLOYEES ORDER BY SALARY)
LOOP
IF REC.SALARY = 500
THEN
DBMS_OUTPUT.PUT_LINE('Record Skipped with id = ' || REC.EMPLOYEE_ID);
--CONTINUE;
GOTO ENDOFLOOP;
END IF;
DBMS_OUTPUT.PUT_LINE(REC.EMPLOYEE_ID ||' : '|| REC.SALARY);
<>
NULL;
END LOOP;
END;
Oracle Java Combo 