Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> what happened for view if i delete table related it
reem
post Oct 12 2017, 07:40 AM
Post #1


Member
**

Group: Members
Posts: 13
Joined: 7-May 16
Member No.: 51,315



hi guys
i want ask this question :
if i create view from table ,what happened if the table is dropped? and is there a way to flasback different objects from recycle bin like views and indexes
etc..... as similar as table????????
Go to the top of the page
 
+Quote Post
burleson
post Oct 12 2017, 10:38 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,577
Joined: 26-January 04
Member No.: 13



Hi Reem,

This is a great question!

Obviously, if you drop a table, all views become that use the table become invalid. (Query dba_views).

As for indexes and views in the recyclebin, you need to try a sole experiment in your test instance.

My guess is they are kept, but verify this. Also see my notes on recyclebin:


http://www.dba-oracle.com/t_oracle_recycle_bin.htm

Please let us know what you find! Thanks


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Oct 13 2017, 11:04 PM
Post #3


Advanced Member
***

Group: Members
Posts: 85
Joined: 6-June 16
From: India
Member No.: 51,370



Hi Reem,

Adding to Mr. Burleson's answer, as he said if the underlying table is dropped, the view goes invalid.

But if you drop your view, you cannot find it in the recycle bin as the recycle bin is meant for storing objects which has a size. A view doesnt have a size on its own and it just wraps up a SQL, which is ran when you run a view. You just dont lose any data or setting if you drop your view! In other words, there will be no effect on the database if your existing view is dropped.

The below objects are taken to the recyclebin (if they are dropped + if the recycle bin is turned ON)

TABLE
NORMAL INDEX
BITMAP INDEX
NESTED TABLE
LOB
LOB INDEX
DOMAIN INDEX
IOT TOP INDEX
IOT OVERFLOW SEGMENT
IOT MAPPING TABLE
TRIGGER
CONSTRAINT
Table Partition
Table Composite Partition
Index Partition
Index Composite Partition
LOB Partition
LOB Composite Partition


Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post
reem
post Oct 14 2017, 09:15 AM
Post #4


Member
**

Group: Members
Posts: 13
Joined: 7-May 16
Member No.: 51,315



I am so much obliged my sir.....
I read your great tips about recyclebin and I made a sole expirement as you told me to do, I do these steps:
create table
create view from prevuios table
drop view
show recyclebin ,nothing in it so I concluded that no way to get back alone view, you can flashback table and its views related it but you can't flashback only view because there is some objects don't get away to recyclebin when we drop as like as view , I have got confirmation of it when we do query:
show recyclebin
there is nothing


I have enjoyed this discussion , thank you
Go to the top of the page
 
+Quote Post
reem
post Oct 14 2017, 09:19 AM
Post #5


Member
**

Group: Members
Posts: 13
Joined: 7-May 16
Member No.: 51,315



QUOTE (boobal_ganesan @ Oct 14 2017, 12:04 AM) *
Hi Reem,

Adding to Mr. Burleson's answer, as he said if the underlying table is dropped, the view goes invalid.

But if you drop your view, you cannot find it in the recycle bin as the recycle bin is meant for storing objects which has a size. A view doesnt have a size on its own and it just wraps up a SQL, which is ran when you run a view. You just dont lose any data or setting if you drop your view! In other words, there will be no effect on the database if your existing view is dropped.

The below objects are taken to the recyclebin (if they are dropped + if the recycle bin is turned ON)

TABLE
NORMAL INDEX
BITMAP INDEX
NESTED TABLE
LOB
LOB INDEX
DOMAIN INDEX
IOT TOP INDEX
IOT OVERFLOW SEGMENT
IOT MAPPING TABLE
TRIGGER
CONSTRAINT
Table Partition
Table Composite Partition
Index Partition
Index Composite Partition
LOB Partition
LOB Composite Partition


Thank you,
Boobal Ganesan


I am so much obliged my sir..... Boobal Ganesan
I do these steps:
create table
create view from prevuios table
drop view
show recyclebin ,nothing in it so I concluded as you told me that no way to get back aand lone view, you can flashback table and its views related it but you can't flashback only view because there is some objects don't get away to recyclebin when we drop as like as view


I have enjoyed this discussion , thank you

but i could'nt understand all displayed objects as like LOB partition, IOT OVERFLOW SEGMENT
IOT MAPPING TABLE , can you help me where I find it in a simple manner to understand???
Go to the top of the page
 
+Quote Post
reem
post Oct 14 2017, 09:20 AM
Post #6


Member
**

Group: Members
Posts: 13
Joined: 7-May 16
Member No.: 51,315



QUOTE (burleson @ Oct 12 2017, 11:38 AM) *
Hi Reem,

This is a great question!

Obviously, if you drop a table, all views become that use the table become invalid. (Query dba_views).

As for indexes and views in the recyclebin, you need to try a sole experiment in your test instance.

My guess is they are kept, but verify this. Also see my notes on recyclebin:


http://www.dba-oracle.com/t_oracle_recycle_bin.htm

Please let us know what you find! Thanks


I am so much obliged my sir..... burleson
I read your great tips about recyclebin and I made a sole expirement as you told me to do, I do these steps:
create table
create view from prevuios table
drop view
show recyclebin ,nothing in it so I concluded that no way to get back alone view, you can flashback table and its views related it but you can't flashback only view because there is some objects don't get away to recyclebin when we drop as like as view , I have got confirmation of it when we do query:
show recyclebin
there is nothing


I have enjoyed this discussion , thank you
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd October 2017 - 03:48 PM