code_junkie

In Oracle, why do public synonyms become invalid when a table partition is dropped

Nov 13th, 2011
131
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.80 KB | None | 0 0
  1. SQL> create table part_test (
  2. i int primary key,
  3. d date
  4. )
  5. partition by range (d) (partition part_test_1 values less than (to_date(' 2 3 4 5 1980-01-01', 'yyyy-mm-dd')));
  6.  
  7. create public synonym part_test for part_test;
  8.  
  9. select object_name, object_type, status from all_objects where object_name = 'PART_TEST';
  10.  
  11. alter table part_test add partition part_test_2 values less than (to_date('1981-01-01', 'yyyy-mm-dd'));
  12.  
  13. select object_name, object_type, status from all_objects where object_name = 'PART_TEST';
  14.  
  15. alter table part_test drop partition part_test_1;
  16.  
  17. select object_name, object_type, status from all_objects where object_name = 'PART_TEST';
  18.  
  19. drop public synonym part_test;
  20. drop table part_test;
  21.  
  22. Table created.
  23.  
  24. SQL> SQL>
  25. Synonym created.
  26.  
  27. SQL> SQL>
  28. OBJECT_NAME OBJECT_TYPE STATUS
  29. ------------------------------ ------------------- -------
  30. PART_TEST TABLE VALID
  31. PART_TEST TABLE PARTITION VALID
  32. PART_TEST SYNONYM VALID
  33.  
  34. SQL> SQL>
  35. Table altered.
  36.  
  37. SQL> SQL>
  38. OBJECT_NAME OBJECT_TYPE STATUS
  39. ------------------------------ ------------------- -------
  40. PART_TEST TABLE VALID
  41. PART_TEST TABLE PARTITION VALID
  42. PART_TEST TABLE PARTITION VALID
  43. PART_TEST SYNONYM VALID
  44.  
  45. SQL> SQL>
  46. Table altered.
  47.  
  48. SQL> SQL>
  49. OBJECT_NAME OBJECT_TYPE STATUS
  50. ------------------------------ ------------------- -------
  51. PART_TEST TABLE VALID
  52. PART_TEST TABLE PARTITION VALID
  53. PART_TEST SYNONYM INVALID
  54.  
  55. SQL> SQL>
  56. Synonym dropped.
  57.  
  58. SQL>
  59. Table dropped.
  60.  
  61. SQL>
Add Comment
Please, Sign In to add comment