oca oracle database 12c sql fundamentals

Recently, because of the job needs, the boss asked the Oracle database on the server to migrate data to another server, I am not very familiar with Oracle, the migration of the database This is a matter of time spent, but also on the internet to inquire a lot of information. All kinds of moves are exhausted or not succ

Page query SQL statement for the Oracle database tutorialOracleSELECT *From (select RowNum r,t1.* from table name T1 where rownum where T2.R >= m For example, from the table sys_option (primary key is sys_id) from 10 records or retrieve 20 records, the statement is as follows: SELECT *From (select RowNum r,t1.* from sys_option where RowNum where T2.R >= 10 MyS

The earliest discovery of this problem was in the Memetniyaz analysis, if the SQL statement in the database The code is as follows Copy Code SELECT * FROM table name where To_char (' Date field ' in table ', ' yyyy-mm-dd ') = ' 2011-01-11 ', If the data in the table is not much, then it is OK to execute the statement. If there is too much data in the table (100.03 bil

); Example: Alter TABLE Stu Modify (stu_name number (20)); (8), add primary key: After the table is established, specify the column as the primary keyThe premise is that there is no null data no duplicate data.ALTER TABLE name add constraint (constraint, limit) pk_ table name _ specified column name primary key (the column specified by the primary key); Example: ALTER TABLE Stu add constrain Pk_stu+stu_no primary key (STU_NO); You can also add a primary key when you create a table CREATE TABLE

Tags: log cheng Baidu Oracle. com targe install Oracle detail getThe simple way to connect a remote database via PL/SQL is to install the Oracle client, and one way is to not install the client, but you need to create the necessary configuration files yourself, the following

drop three grammars. Database Manipulation Language (DML) is responsible for the instruction set for running data access work on database objects, with INSERT UPDATE DELETE three directives as the core The Database Control Language (DCL) is responsible for authorization and role control of database objects,

dataset. C: Different from open source database synchronization software (Google results: Symmetricds, Opendbdiff (for SQL Server)) The previous three scenarios can be synchronized from Oracle to sqlserver2005, but if there is a large amount of data that may result in data loss in midstream or slower synchronization, the following scenarios are established: S

\dbhome_1)(program = dg4msql)))The--------highlighting section shows that when the corresponding SID is configured on the transparent gateway, copy the highlighted paragraph and modify the corresponding sid_name.LISTENER =(Description_list =(DESCRIPTION =(address = (PROTOCOL = TCP) (HOST = 172.18.123.113) (port = 1521))----The IP address or hostname and port number of the listening server(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))))Adr_base_listener = D:\app\Administrator3. After configurin

Create and use packages for oracle Database SQL statements, oraclesql -- Create a package Create or replace package pkg_emp As Name emp. ename % type; Procedure pro_emp (id emp. deptno % type, num out number ); Function fun_deptno (p_empno emp. empno % type, p_sal out number) return emp. ename % type; End; / -- Create a package subject Create or replac

Oracle Database pl SQL study notes, oraclepl 1. define basic variables:2. referenced variable: set serveroutput on declarepename emp. ename % type; psal emp. sal % type; beginselect ename, sal into pename, psal from emp where empno = '000000'; dbms_output.put_line (pename | 'salary:' | psal); end; /3. Record-type variables: set serveroutput on declareemp_rec emp

Label:SELECT * FROM (SELECT Tt.*,rownum RN from(SELECTa.case_id as Treatid,A.type as TYPE,B.content as CONTENT,b.add_user_id as Adduserid,B.add_user_type as Addusertrpe,A.create_time as Createtime,B.check_time as Checktime,E.addusertypeFrom T_medical_case ALeft JOIN t_treatment B on a.case_id = b.treat_idLeft JOIN (select c.patient_id as ID, C.user_name, ' 2 ' as AddusertypeFrom T_patient_info CUNIONSelect d.doctor_id as ID, D.user_name, ' 1 ' as AddusertypeFrom T_doctor_info D) E on b.add_user_

(primary key), Username VARCHAR2 (20), CONSTRAINT un_username Unique (username) ) 2. Adding a unique constraint when modifying a table CREATE TABLE USERINFO_U2 ( ID VARCHAR2 (primary key), Username VARCHAR2 (20) ) ALTER TABLE USERINFO_U2 ADD CONSTRAINT un_username_new Unique (username) 3. Delete a constraint disabling UNIQUE constraints ALTER TABLE USERINFO_U2 DISABLE CONSTRAINT un_username_new Delete ALTER TABLE USERINFO_U2 DROP CONSTRAINT un_username_new Five, check the constraints 1. Set CHE

One, add dataINSERT statement INSERT INTO TABLE_NAME (Name of field, field name) VALUES (value1,value2)1. Add values to all fields in the tableINSERT into UserInfoVALUES (1, ' xxx ', ' 123 ', ' [email protected] ', sysdate)2. Add values to the fields established in the tableINSERT into UserInfo (ID,USERNAME,USERPWD)VALUES (2, ' yyy ', ' 123 ')CREATE TABLE Userinfo1(ID number (6,0),RegDate Date Default Sysdate)INSERT into USERINFO1 (ID)VALUES (1)Second, copy the table data1. Copy all when creatin

Tags: data SP problem BS database server SQL as installationWhen SQL Server accesses a database table in Oracle remotely:The remote syntax is typically: SELECT * from OpenQuery (Oracle linked server name, ' query statement ')Eg:se

, Baidu to basically is program 2, but some of the data is basically the Oracle regular expressions listed to explain it again, In fact, I want to be a regular simulation like the function, I think if as a not proficient in the regular novice, learning a lot may not be able to use, so I have to share the idea of this article, if there is need to take to use good, if the need for regular, then further study.Advantages of this scheme:Simple to use, clea

Log on to PL/SQL Developer, log on to the local database, and check that the Oracle listening service is not started. Right-click to start the listening program and report an error. How can this problem be solved ?, I. Error description When you log on to PL/SQL Developer to log on to the local

Oracle query database tablespace usage SQL statement SELECTUPPER (F. TABLESPACE_NAME) tablespace name, D. TOT_GROOTTE_MB Oracle query database tablespace usage SQL statement SELECT UPPER (F. TABLESPACE_NAME) tablespace name, D. TO

Oracle Database SQL --- one of selectPrefaceI have already explained in the previous article that the best learning is practice! Therefore, my learning is based on practice. In all SQLThe most basic statement is the query statement. Therefore, I also learned from the select statement. (In fact, this part of the author)I have mastered it, but it is still necessary

Tags: HRP PLL BOM gzip imp different query ant FTPThe process of creating an instance of an Oracle database is similar to SQL Server creating a database, one instance of Oracle can correspond to more than one table space, one table space corresponds to one user, and a differ

I. Basic Knowledge Oracle SQL * LOADER can load externally formatted text data to database tables. It is usually used in combination with the SPOOL Method for exporting text data. 1. Command Format SQLLDR keyword = value [, keyword = value,…] Example: $ sqlldr user/pwd control = emp. ctl data = emp. dat bad = emp. bad log = emp. log 2. Control File