views:

3267

answers:

2

Hi everyone, can someone tell me why the following behavior occurs (Oracle 10.2):

SQL> create table part_test (
        i int primary key,
        d date
)
partition by range (d) (partition part_test_1 values less than (to_date('  2    3    4    5  1980-01-01', 'yyyy-mm-dd')));

create public synonym part_test for part_test;

select object_name, object_type, status from all_objects where object_name = 'PART_TEST';

alter table part_test add partition part_test_2 values less than (to_date('1981-01-01', 'yyyy-mm-dd'));

select object_name, object_type, status from all_objects where object_name = 'PART_TEST';

alter table part_test drop partition part_test_1;

select object_name, object_type, status from all_objects where object_name = 'PART_TEST';

drop public synonym part_test;
drop table part_test;

Table created.

SQL> SQL> 
Synonym created.

SQL> SQL> 
OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
PART_TEST                      TABLE               VALID
PART_TEST                      TABLE PARTITION     VALID
PART_TEST                      SYNONYM             VALID

SQL> SQL> 
Table altered.

SQL> SQL> 
OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
PART_TEST                      TABLE               VALID
PART_TEST                      TABLE PARTITION     VALID
PART_TEST                      TABLE PARTITION     VALID
PART_TEST                      SYNONYM             VALID

SQL> SQL> 
Table altered.

SQL> SQL> 
OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
PART_TEST                      TABLE               VALID
PART_TEST                      TABLE PARTITION     VALID
PART_TEST                      SYNONYM             INVALID

SQL> SQL> 
Synonym dropped.

SQL> 
Table dropped.

SQL>

The synonym becomes invalid after partitions are dropped, and I can't work out why.

Thanks for any thoughts.

+2  A: 

It doesn't in 11.1.0.6 It could have been a bug that was fixed. It may have been something that was necessary (eg for revalidating a view based on a synonym...) but where an improved technique has been used.

Gary
I would have liked to accept this answer to, but I can only accept one, so I'll go for the one related to my Oracle version.Ta Gary.
Jamie Love
+3  A: 

I have tested it with Oracle 10.2 and the synonym becomes indeed invalid but when an other user uses the synonym by doing

select count(*) from part_test;

Oracle automatically compiles the synonym and the synonym becomes valid.

It is the same with packages, procedures and functions. When you execute them and they are invalid, Oracle will try to compile them automatically.

tuinstoel