DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: limited access

  1. #1
    Join Date
    Feb 2002
    Posts
    54
    For example,

    i create a view based on the employee table. how would i limit that the view can only be accessible betwwn 8:00 am to 5:00 pm.

    tnx a lot.

    èDó
    =======================
    Manifest plainness,
    embrace simplicity,
    reduce selfishness,
    have few desires. LAO TZU
    =======================

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    That's a bit radical but why don't you just schedule a job with DBMS_JOB that drops the view at 5PM and another job that creates the view at 8AM.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Feb 2002
    Posts
    54
    Thanks a lot julian.

    Could you please tell me where i can read more about dbms_job?

    Thanks again.

    Peace!

    èDó
    =======================
    Manifest plainness,
    embrace simplicity,
    reduce selfishness,
    have few desires. LAO TZU
    =======================

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    In SQL*Plus run:

    Code:
    variable jobno number; 
    begin 
    dbms_job.submit(:jobno,'edo.create_the_view;',trunc(sysdate) + 8/24,
    'trunc(sysdate+1) + 8/24'); 
    COMMIT; 
    end; 
    /
    Code:
    variable jobno number; 
    begin 
    dbms_job.submit(:jobno,'edo.drop_the_view;',trunc(sysdate) + 17/24,
    'trunc(sysdate+1) + 17/24'); 
    COMMIT; 
    end; 
    /
    and make a package called EDO with two procedures create_the_view and drop the view where you create and drop the view respectively.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Feb 2002
    Posts
    54
    You've been of great help sir..

    tnx a lot!!


    èDó
    =======================
    Manifest plainness,
    embrace simplicity,
    reduce selfishness,
    have few desires. LAO TZU
    =======================

  6. #6
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Hi Julian,
    Thanks for the explaination.You have explained it very clearly and i have tried it also.Could you tell me for what kind of jobs you generally use DBMS_JOB and for what kind of jobs you use operating system commands like "at" in Windows NT.
    Thanks,
    Nishant

  7. #7
    ... WHERE ...bla-bla .... AND {get the time} between {} and {} - you got the point, this is only the idea, not the syntax. You could use to_char() bla-bla...
    And is ready, without any complicate things...

    [Edited by ovidius on 04-04-2002 at 02:26 AM]
    ovidius over!

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by ovidius
    ... WHERE ...bla-bla .... AND {get the time} between {} and {} - you got the point, this is only the idea, not the syntax. You could use to_char() bla-bla...
    And is ready, without any complicate things...

    [Edited by ovidius on 04-04-2002 at 02:26 AM]
    Of course, this is a much better solution. Cool. Just you have to run:

    Code:
    create view V as
    select * from EMP
    where to_char(sysdate,'hh24') between '08' and '17';
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by nishant
    Hi Julian,
    Thanks for the explaination.You have explained it very clearly and i have tried it also.Could you tell me for what kind of jobs you generally use DBMS_JOB and for what kind of jobs you use operating system commands like "at" in Windows NT.
    Thanks,
    Nishant
    I use DBMS_JOB for scheduled scripts, etc. For example, I have a script that calculates the main hit ratioes of all instances, etc. and emails me the information at 5 o'clock every morning. So, when I come to work I only check my email to see if everything is OK in the databses.

    I use "at" for example to take dumps every night.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Thanks again.
    So can I say that you use "at" generally for backup purpose and for other DB activities like tuning you would prefer DBMS_JOB.
    Take Care,
    Nishant.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width