-
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
=======================
-
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
-
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
=======================
-
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
-
You've been of great help sir..
tnx a lot!!
èDó
=======================
Manifest plainness,
embrace simplicity,
reduce selfishness,
have few desires. LAO TZU
=======================
-
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
-
... 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!
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|