Jump to content

vidhuvs

Members
  • Content count

    51
  • Joined

  • Last visited

Community Reputation

0 Neutral

About vidhuvs

  • Rank
    Advanced Member
  • Birthday 08/21/1983

Profile Information

  • Gender
    Male
  • Location
    India
  1. vidhuvs

    Index Full Scan

    Hi Team, As per Oracle docs When the Optimizer Considers Index Full Scans The optimizer considers an index full scan in a variety of situations. The situations include the following: A predicate references a column in the index. This column need not be the leading column. No predicate is specified, but all of the following conditions are met: All columns in the table and in the query are in the index. At least one indexed column is not null. A query includes an ORDER BY on indexed non-nullable columns. Now to check this scenario I performed the following CREATE TABLE Test_Table ( Eid NUMBER NOT NULL, First_Name VARCHAR2(100) NOT NULL, Last_Name VARCHAR2(100) NOT NULL, Description VARCHAR2(100) NULL ); BEGIN FOR counter IN 1 .. 10000 LOOP INSERT INTO Test_Table (EId, First_Name, Last_Name, Description) VALUES (CustomerSequence.NEXTVAL, DBMS_RANDOM.STRING('A', 10), DBMS_RANDOM.STRING('A', 20), DBMS_RANDOM.STRING('A', 100)); END LOOP; END; / CREATE INDEX test_tbl_idx1 ON Test_Table (eId, first_name); However when I generated the explain plan for the below query it showed Index range scan instead of Index Full Scan. Can you please explain why? SELECT * FROM Test_Table where eid = 40005; Because as per Oracle docs "A predicate references a column in the index. This column need not be the leading column." should show Index Full scan. Thanks, Vidhu V S
  2. vidhuvs

    Running shceduled job on a particular instance

    Hi Burleson, I'll try this and let you know. However I have passed the instance id as a variable, since the job creation is dynamic. Will that be a problem ?. Please advise.
  3. vidhuvs

    Running shceduled job on a particular instance

    Hi Burleson, Let me make it clear with an example code that i have written. sys.DBMS_SCHEDULER.create_job ('TEST', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN SP_TEST; END;', enabled => TRUE); sys.DBMS_SCHEDULER.set_attribute (name => 'TEST', attribute => 'MAX_RUNS', VALUE => 1); sys.DBMS_SCHEDULER.set_attribute (name => 'TEST', attribute => 'AUTO_DROP', VALUE => TRUE); sys.DBMS_SCHEDULER.set_attribute (name => 'TEST', attribute => 'INSTANCE_ID', VALUE => 1); We have a 6 node cluster for our oracle database. I am trying to create a DBMS scheduler job (one time job) dynamically to run that on instance id 1. However when the job starts it gets assigned to instance id 6. No idea why this is happening. Actually i wanted it to start on the same instance that am assigning it. Can you please suggest options for me how t handle this.
  4. Hi, In one of our requirements I created a dynamic scheduled job, based on some logic, on a particular instance - say 1 using the INSATNCE_ID attribute. But when the job starts, most of the time it gets created on some other instance - say 6 instead of 1. However I wanted the job to get created on the same instance as i needed. Can you please suggest options for me how to force a dynamic scheduled job to get assigned on the same node that i need.
  5. vidhuvs

    About PGA Creation

    Hi, In Oracle docs it's mentioned that PGA gets created for every server process. In that case will PGA gets created for LGWR process which is a server process. It yes can someone explain what information gets stored in a PGA for an LGWR process.
  6. vidhuvs

    Union two rows without changing their unique values

    Hi Sindhu, Can you please specify the result you actually need. You can just write down in rows and columns. Also please mention what you are trying to do/achieve, so that I can try if I can help you out Are you trying to say that you need the result in this way. C1. C2. C3. C4. C5 R1. A1. X1. B1. D1. R2. A1. X2. B 1. D 1 R3. E1. X1. F1. D1
  7. Hi Nasim, No Worries. I'll explain you. When I meant SALT it is actually a random value generated by oracle (although am not sure how it actually gets generated) which is concatenated with the user password. Here in your query you have just used the word SALT which is Incorrect. Also Oracle 11G uses spare4 column instead of password column for storing hash password. So in this case if you have created a database user Train1 with password Train1, then you can use the below query to view the password hash and the salt that Oracle has generated for that password. SELECT SUBSTR (spare4, 3, 40) hash_password, SUBSTR (spare4, 43, 20) salt, spare4 FROM sys.user$ WHERE name = 'TRAIN1'; Here the column hash_password is the password by which you can check. Also It would be better if you can create a function to check for the user password. I have created the function here for you. I have already tested this: CREATE OR REPLACE FUNCTION validateUser (username IN VARCHAR2, passwd IN VARCHAR2) RETURN NUMBER IS lv_pwd_raw RAW (128); lv_enc_raw RAW (2048); lv_user_hash RAW (128); lv_user_salt RAW (128); lv_hash_found VARCHAR2 (300); BEGIN SELECT SUBSTR (spare4, 3, 40) INTO lv_user_hash FROM sys.user$ WHERE name = UPPER (username); SELECT SUBSTR (spare4, 43, 20) INTO lv_user_salt FROM sys.user$ WHERE name = UPPER (username); lv_pwd_raw := UTL_RAW.cast_to_raw (passwd) || HEXTORAW (lv_user_salt); lv_enc_raw := sys.DBMS_CRYPTO.hash (lv_pwd_raw, DBMS_CRYPTO.hash_sh1); lv_hash_found := UTL_RAW.cast_to_varchar2 (lv_enc_raw); IF lv_enc_raw = lv_user_hash THEN RETURN 1; ELSE RETURN 0; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0; WHEN OTHERS THEN raise_application_error ( -20001, 'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM); END; This function returns 1 if user name and password is correct, else returns 0 if either user name or password is incorrect. You can execute the function using select query as given below: select validateUser('Train1', 'Train1') from dual; -- This returns 1 Let me know if you have any questions on this
  8. Hi Nasim, Oracle uses the below methods to store password 1. A SALT value gets generated by Oracle (this will be generated in random by Oracle. However I haven't figured out how this gets generated.) 2. Password (case-sensitive) and SALT value gets concatenated. 3. Oracle uses SHA1 hashing algorithm to generate the hash for the concatenated value 4. Thus 11g password hash becomes: 'S:' + [sHA1 hash for (Password + SALT)] plus [sALT] ie 'S:' || DBMS_CRYPTO.hash ('USERPASSWORD+SALT', dbms_crypto.hash_sh1) || SALT; You can refer the below link to find out how you can validate for the user entered password with the one stored in database http://www.petefinnigan.com/weblog/archives/00001097.htm You can use the same technique to hash the password for your users. Also I believe almost the same can be handled through .NET security features also. :-)
  9. vidhuvs

    Sending emails from Oracle

    Hi Boobal, Can you tell me how shall i do the same for logging in to my company outlook instead of using gmail for sending emails. I tried however am not able to.
  10. vidhuvs

    Sending emails from Oracle

    Hi Boobal, I have corrected it now. Looks like I have used the Incorrect version of Stunnel. Now am able to send email to Gmail from PL SQL. Thanks So much! You are a Rock Star Dude !
  11. vidhuvs

    Sending emails from Oracle

    Hi Boobal, I have tested this and got error on ACL's. So i have configured the ACL using the below code begin dbms_network_acl_admin.create_acl ( acl => 'utl_http.xml', description => 'HTTP Access', principal => 'SYSUSER', is_grant => TRUE, privilege => 'connect', start_date => null, end_date => null ); dbms_network_acl_admin.add_privilege ( acl => 'utl_http.xml', principal => 'SYSUSER', is_grant => TRUE, privilege => 'resolve', start_date => null, end_date => null ); dbms_network_acl_admin.assign_acl ( acl => 'utl_http.xml', host => 'localhost', lower_port => 1925, upper_port => 1925 ); commit; end; And then I changed the stunnel.conf as required. I have sent the file to your mail id. can you please check and let me know if anything wrong. Atlast after running the script I got the below error ORA-20000: Failed to send mail due to the following error: ORA-29278: SMTP transient error: 421 Service not available ORA-29278: SMTP transient error: 421 Service not available Looks like smtp server is not running. Can you please guide me.
  12. vidhuvs

    Sending emails from Oracle

    Hi Boobal, I have created the wallet and then used the same methods that you have suggested as below: DECLARE c UTL_SMTP.CONNECTION; PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS BEGIN UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF); END; BEGIN c := utl_smtp.open_connection( host => 'smtp.gmail.com', port => 25, wallet_path => 'file:/app/Vidhu/product/11.2.0/dbhome_1/BIN/owm/wallets/Vidhu', wallet_password => 'Vidhu123', secure_connection_before_smtp => FALSE); UTL_SMTP.HELO(c, 'gmail.com'); UTL_SMTP.STARTTLS ( c ); UTL_SMTP.MAIL(c, 'sender@foo.com'); UTL_SMTP.RCPT(c, 'vidhuvs1983@gmail.com'); UTL_SMTP.OPEN_DATA©; send_header('From', '"Sender" <sender@foo.com>'); send_header('To', '"Recipient" <vidhuvs1983@gmail.com>'); send_header('Subject', 'Hello'); UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!'); UTL_SMTP.CLOSE_DATA©; UTL_SMTP.QUIT©; EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN BEGIN UTL_SMTP.QUIT©; EXCEPTION WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN NULL; -- When the SMTP server is down or unavailable, we don't have -- a connection to the server. The QUIT call raises an -- exception that we can ignore. END; raise_application_error(-20000, 'Failed to send mail due to the following error: ' || sqlerrm); END; However I am getting the following error: Error report - ORA-06550: line 10, column 6: PLS-00306: wrong number or types of arguments in call to 'OPEN_CONNECTION' ORA-06550: line 10, column 1: PL/SQL: Statement ignored ORA-06550: line 17, column 10: PLS-00302: component 'STARTTLS' must be declared ORA-06550: line 17, column 1: PL/SQL: Statement ignored I am not able to find a solution for this. can you please help
  13. vidhuvs

    Sending emails from Oracle

    Hi Boobal, When I used ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/APP/VIDHU/ADMIN/ORCL/WALLET' IDENTIFIED BY "Vidhu"; It gave me the error ORA-00900: invalid SQL statement I tried taking out the quotes from the password, still didn't work. I logged in as SYSDBA for this. Do we need to login as SYSKM user to make it work. If yes how can I login as such. Else please let me know what might be the problem.
  14. vidhuvs

    Sending emails from Oracle

    Hi Boobal, Can you please tell me what password I have to give here. Do I need to create one before that? If yes, can you suggest me how to?
  15. vidhuvs

    Sending emails from Oracle

    Hi, I wrote a script to send email from Oracle to my gmail Id. Please see below: DECLARE c UTL_SMTP.CONNECTION; PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS BEGIN UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF); END; BEGIN c := UTL_SMTP.OPEN_CONNECTION('smtp.gmail.com'); UTL_SMTP.HELO(c, 'gmail.com'); UTL_SMTP.MAIL(c, 'sender@foo.com'); UTL_SMTP.RCPT(c, 'vidhuvs1983@gmail.com'); UTL_SMTP.OPEN_DATA©; send_header('From', '"Sender" <sender@foo.com>'); send_header('To', '"Recipient" <vidhuvs1983@gmail.com>'); send_header('Subject', 'Hello'); UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!'); UTL_SMTP.CLOSE_DATA©; UTL_SMTP.QUIT©; EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN BEGIN UTL_SMTP.QUIT©; EXCEPTION WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN NULL; -- When the SMTP server is down or unavailable, we don't have -- a connection to the server. The QUIT call raises an -- exception that we can ignore. END; raise_application_error(-20000, 'Failed to send mail due to the following error: ' || sqlerrm); END; However when I ran the script i got the below error: ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first. u78sm10295096pfa.53 - gsmtp when I add utl_smtp.command(c,'STARTTLS'); after helo command then I am getting "value error". Can you please guide me on how to resolve this.
×