Resolves an object collection in an Oracle database Schema_java

Source: Internet
Author: User

To build an Oracle, there will be many schemas below, and no data under each schema is affected.

Feel and MySQL library concept is very similar, now use the database management system is in fact so divided, MySQL with the ip+port+ library identification, Oracle with Ip+port+schema logo, usually still hear an example of the concept, My understanding is that the example is a series of related processes that represent a database service. At present, in order to save resources, the machine is often divided into several instances, with different port numbers identified, each instance has more than one schema.

Xu elder brother and I say very image, Oracle one instance has corresponding multiple libraries. MySQL A library should have multiple instances. MySQL has a very flexible build base.

In a row of data in Websql, name Mgmt_view,displayname is MGMT_VIEW@DEV_CRM,JDBC for JDBC:ORACLE:THIN:@10.232.31.XXX:1521:NEWCRM, Jdbcusername is dev_ddl,dsname for Dev_crm,dbname Newcrm (consistent with Jdbaurl) Dbuser is mgmt_view,tnsname for DEV_CRM (should be the configuration on Ora file).

Name is stat,displayname for STAT@DEV_DBC, JDBC is jdbc:oracle:thin:@10.232.31.xxx:1521:dev-dbc,jdbcusername for DEV_DDL, DSName is dev-dbc,dbname for DEV-DBC (consistent with Jdbaurl), Dbuser is stat,tnsname for DEV_DBC (should be configuration on Ora file),

My local ora file has the following configuration:

Localtest =
 (DESCRIPTION = (Address
  = (PROTOCOL = TCP) (HOST = xxx.com) (PORT = 1521))
  (Connect_data =
   (SER VER = dedicated)
   (service_name = localtest)))
 

There's a servicename, and there's the SID, and there's the first localtest name, which identifies the link descriptor.

The #db_name是数据库的名称, which is set at DB installation, is not modifiable and determines the location of the database installation files. #instance_name是实例名, is the name in the database run. In Oo, db_name is equivalent to a class and instance_name to an object, and it represents memory and its processes in the running of the database, and it affects the names of those processes, such as a database db_name=cus, and in fact an example instance_name= Aking, then when the database is up, its process name may be: pmon_aking_1. #service_name我觉得应该是指数据库网络连接时的名称, it will be considered in the listener configuration. This value can also be changed arbitrarily, and can also have multiple values. #SID_NAME指数据库的运行的实例名, should be consistent with instance_name. #GLOBAL_DBNAME是listener配置的对外网络连接名称, we will consider this parameter when configuring Tnsname.ora. This parameter can be set arbitrarily.
In short, for example, your name is xiaoming, but you have a lot of nicknames. Your parents call you xiaoming, but friends call you a nickname. Here your parents are examples of Oracle, Xiaoming is sid,service name is your nickname. SIDs are used to differentiate between individual databases and service name for external links. They may be different, pay attention to which name you get, use it properly, or you may have an error with a remote connection to another database.
Another concept is the relationship between schema and user. I quote directly, very image:

The relationship between schema and user in an Oracle database is one by one, meaning that a schema corresponds to only one user, and a user corresponds to a schema. When there is a table,view,index under a user ... When schema object, this user becomes a schema, the one that appears in Enterprise Manager, if there is no table,view,index under a user ... When schema object, this user does not appear in Enterprise Manager
User is just name, schema was home, with many stuff, tables, index, ...
Local configuration, servicename and Sid are both a

Schemas and other schemas used to log in with Dicmgr

The SID field in the db_host of the SID in the Websql represents the library name, which corresponds to the jdbcurl on the same value.

Look at the synchronization of the dictionary program, DSName and Tnsname is Tnsname,dbname is sid,name and dbuser corresponding to the schemal,tnsname and SID can be directly from the Dbhostgroup table to take out. Schemal can use the method to take out:

Select lower (username) Username from dba_users where username not in (' Stdbyperf ', ' READONLY ', ' Appqossys ', ' anysql ',

' Dbflash ', ' SYS ', ' SYSTEM ', ' MONITOR ', ' tbsearch ', ' MANAGER ', ' Sysman ', ' Exfsys ', ' Wmsys ', ' DIP ', ' Tsmsys ', ' oracle_ocm ', ' Outln ', ' dbsnmp ', ' perfstat ', ' SEARCH ', ' TOOLS ', ' tbdump ', ' Dmsys ', ' XDB ', ' ANONYMOUS ' and ' username ' like
?

 

To sum up, Oracle has a lot of these name, where ServiceName and SIDs are instance-level schemas similar to those in MySQL, which are generally equivalent to username. In addition Jdbcurl the port behind the corresponding is the service name OH. and a table structure to familiarize yourself with Websql's database.


Oracle creates users and creates related views and synonyms for other schemal
1 Create a user

CREATE USER "xxx" identified by the XXX account UNLOCK profile "DEFAULT";
Grant Connect, resource to XXX;

2 Give this user permission to query for tables on other Schemal

GRANT SELECT on Wf_dataexport to XXX;
GRANT SELECT on Dbmis2_sql_exe_his to XXX;

3 Create a view on a new user to query other schemal tables

CREATE VIEW "XXX". " V_xxx_wf_dataexport "AS select * from Idb.wf_dataexport;
CREATE VIEW "XXX". " V_xxx_dbmis2_sql_exe_his "AS select * from Idb.dbmis2_sql_exe_his;

4 Establish a synonym

Create or replace synonym Xxx.base_aone_app for base_aone_app;

All of these actions are performed in the original Scheaml and use the DBA account.

To sum up, Oracle's management commands are largely forgotten and picked up when they are free.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.