Oracle

Oracle's analytic functions are powerful, and work is often used over the years. This time, the usual use of the analysis function to sort out. for future viewing.We take cases to learn, so it is more easy to understand.1. Build a tableCREATE table

SELECT * from Scott.dept;SELECT * from Scott.emp;--dual is a virtual table provided by OracleSelect Length (' Hello ') from dual;--Common functions--lower uppercase to lowercase upper lowercase to uppercaseSELECT * from Scott.emp where ename= '

1. Current number of database connectionsSelect Count(*) fromV$processwhereProgram='ORACLE. EXE (SHAD)'; 2. Maximum number of connections allowed for the databaseSelectValue fromV$parameterwhereName='Processes'3. To modify the maximum number of

Note: This article source: Small yan Kevin "Oracle Database Memory adjustment increased memory "Simulated operating system memory increased from 2G to 8G, adjust the database memory parameters, the example parameter is not as the actual production

First login to the administrator account, or a user with DBA authority, followed by:--Query All users SELECT * from Dba_users; --Create new users Create user gpmgt identified by GPMGT; --View all user tablespace Select

One, over () analysis functionsGroup Check before: SELECT * from Test t where (select COUNT (*) from Test a where t.type=a.type and T.scope>a.scope) --rank ()/dense_rank () over (partition by ...)SELECT * FROM (select T.*,rank () over (partition by

Advanced Query1. Related queriesFunction: Can be queried across multiple tables--Find out the name of the employee and the name of his departmentGrammar:Select columns, columns, columnsFrom table 1 Join table 2On table 1 FOREIGN key = Table 2

1.ROWNUM Use--top-n AnalysisUsing the result set returned by the SELECT statement, you can use pseudo-column rownum if you want to query the top N records by specific criteria.RowNum is a pseudo-column that adds to the result set, which is the

Advanced Query1. Related queriesFunction: Can be queried across multiple tables--Find out the name of the employee and the name of his departmentThe ancient notationSelect First_name,name from s_emp,s_dept where s_emp.dept_id = s_dept.id;The wording

The execution environment of a statement is a SQL window of Plsql, which is intended to determine the number of records not equal to a field from all tables in the entire database.The code is as follows:DECLARE S_sql clob:= "; --Declares a variable

Oracle Sometimes the user password contains some special characters directly logged in will be error, you need to use the following methods to log in SqlplusSqlplus ' username/"password"PS: The whole is enclosed in single quotation marks, strings

--Create a temporary table, specify a good format, is necessary, different from SQL Server so arbitrary; Create Global temporary table Record4 (  YLJGDM VARCHAR2) not null,  Jzlsh VARCHAR2 () not null,  kh VARCHAR2 (+),  klx VARCHAR2 (+),  hzxm

Oracle Delete user commands and partial commandsDrop user user_name cascade;Drop user user_name cascade;CREATE TABLE SpaceCREATE tablespace DATA01DataFile '/oracle/oradata/db/data01.dbf ' SIZE 500MUNIFORM SIZE 128k; #指定区尺寸为128k, if not specified,

--The vast majority of dba/all/user/v_$/gv_$/session/index begins with a view--dba_tables means all the relational tables that the DBA has or can access. --All_tables All relational tables that a user has or can access. -User_tables means all the

When a user wants to access data in another database table across a local database, the local database must have a dblink of the remote database, and the Dblink local database can access the data in the Remote database table as if it were accessing

Switch to Oracle User: Su-oracle, and execute under Oracle command line:Launch into Oracle:Connection database: Sqlplus/as sysdbaWindows Client Remote connection Oralce:Sqlplus Itmuser/[email PROTECTED]//192.168.14.10:1521/ORCLTest with

Summary of Oracle8i UNDO tablespace corruption (ORA-01092 and ORA-00600 [4193]) in the case of database Incomplete recovery experience, oracle8iora-01092 The recovery directory library of the backup production environment cannot be started due to a

Command for viewing the Oracle Database Name and Instance name, oracle Database Name instance View Database Name SQL> select name from v $ database; NAME --------- ORCL SQL> desc v $ database; Is the name empty? Type --------------------------------

Oracle Transaction Processing Mechanism explanation, oracle Transaction Processing Mechanism I. Transaction Concept Concept: transactions in databases are logical units of work. A transaction is composed of one or more SQL statements that complete

Basic usage of creating, managing, and deleting ORACLE tablespace (code implementation), oracle table 1. Create a Permanent Table -- Create a permanent tablespace TEST01 with a size of 50 MB. do not automatically expand the data file create