в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:55:06Z

1 версия

Новая страница

==create synonym for a view==



<source lang="sql">

SQL>
SQL> create table emp(
2 emp_id integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,shortZipCode varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,company_name varchar2(50));
Table created.
SQL>
SQL>
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (1,"Jones","Joe","J","1 Ave","New York","NY","11202","1111","212", "221-4333","Big Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (2,"Smith","Sue","J","1 Street","New York","NY","11444","1111","212", "436-6773","Little Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (3,"X","Peggy","J","1 Drive","New York","NY","45502","2222","212", "234-4444","Medium Company");
1 row created.
SQL>
SQL> create or replace view phone_list as
2 select emp_id, firstname || " " || midinit || ". " || lastname as name,"(" || area_code || ")" || phone as telephone#
3 from emp;
View created.
SQL>
SQL>
SQL>
SQL> desc phone_list
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
EMP_ID NOT NULL NUMBER(38)
NAME VARCHAR2(39)
TELEPHONE# VARCHAR2(13)
SQL> select * from phone_list;
EMP_ID NAME TELEPHONE#
---------- --------------------------------------- -------------
1 Joe J. Jones (212)221-4333
2 Sue J. Smith (212)436-6773
3 Peggy J. X (212)234-4444
3 rows selected.
SQL> create synonym phones for phone_list;
Synonym created.
SQL> desc phones
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
EMP_ID NOT NULL NUMBER(38)
NAME VARCHAR2(39)
TELEPHONE# VARCHAR2(13)
SQL> select * from phones;
EMP_ID NAME TELEPHONE#
---------- --------------------------------------- -------------
1 Joe J. Jones (212)221-4333
2 Sue J. Smith (212)436-6773
3 Peggy J. X (212)234-4444
3 rows selected.
SQL>
SQL> select view_name from user_views;
VIEW_NAME
------------------------------
EMP_HQ
V
AVG_SAL
EMPDEPT_V
DEPT_SAL
ALL_ORACLE_ERRORS
INVENTORY_VIE
TOP_EMP
EMP_BONUS
SHARED
PHONE_LIST
11 rows selected.
SQL>
SQL> select synonym_name, table_name from user_synonyms;
SYNONYM_NAME TABLE_NAME
------------------------------ ------------------------------
PHONES PHONE_LIST
1 row selected.
SQL>
SQL>
SQL> drop synonym phones ;
Synonym dropped.
SQL>
SQL>
SQL>
SQL> drop table emp;
Table dropped.

</source>

==Create synonym for table==



<source lang="sql">

SQL>
SQL> CREATE TABLE source_log
2 (backup_date DATE,
3 backup_time VARCHAR2(6),
4 last_ddl_time DATE,
5 owner VARCHAR2(30),
6 name VARCHAR2(30),
7 type VARCHAR2(12),
8 line NUMBER,
9 text VARCHAR2(2000))
10 /
Table created.
SQL> CREATE INDEX source_log_idx1 ON source_log
2 (last_ddl_time, owner, name)
3 /
Index created.
SQL> CREATE or replace PUBLIC SYNONYM source_log FOR source_log
2 /
Synonym created.
SQL> GRANT SELECT, INSERT ON source_log to PUBLIC
2 /
Grant succeeded.
SQL>
SQL> drop table source_log;
Table dropped.
SQL>
SQL>

</source>

==Create synonyms for dropped tables==



<source lang="sql">

SQL>
SQL>
SQL> SELECT "CREATE PUBLIC SYNONYM " || table_name
2 || " for " || user || "." || table_name || ";"
3 FROM DBA_TABLES
4 WHERE dropped = "NO";
no rows selected
SQL>

</source>

==Creating a Private Synonym==



<source lang="sql">

create synonym addresses for hr.locations;
desc addresses

</source>

==Creating a Public Synonym==



<source lang="sql">

create public synonym employees for hr.employees;

</source>

==drop public synonym;==



<source lang="sql">

drop public synonym synonymName;

</source>

==drop synonym addresses;==



<source lang="sql">

Creating a Private Synonym
create synonym addresses for hr.locations;
desc addresses

drop synonym addresses;

</source>

==Viewing synonyms and what they reference.==



<source lang="sql">

SQL> select owner, synonym_name, table_owner, table_name
2 from all_synonyms
3 where synonym_name="JOBS" and rownum < 6;
no rows selected
SQL>
SQL>
SQL>

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/User Previliege/Synonym - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия