Oracle

1, first of all, the use of rownumRowNum: A pseudo column added to the result set, that is, the result set followed by a column (emphasis: first to have the result set), simply say RowNum is the match to the conditional result of the serial number.

Shell Script connects Oracle database Recently studied how to use shell script to connect to the database, and then execute the query statement. So for the script that needs to be executed regularly, we can build a crontab to execute it

Dba_free_space: Describes the remaining size of the data space Dba_data_file: Describes the total size of the data table space Select A.tablespace_name, A.bytes bytes_used, B.largest,round ((a.bytes-b.bytes)/a.bytes) *100,2) percent_used from (

Environment: Oracle 11g R2 +sqlplus problem: Oracle with SYS and system default password login prompt Ora-01017:invalidusername/password;logond denied As we all know, the default password for SYS is Manager,system's default password is

Query for one hour Select Concat (To_char (sysdate, ' yyyy-mm-dd ') | | (To_char (sysdate, ' hh24 ')-1), ': 00:00 ') Start_time,concat (To_char (sysdate, ' yyyy-mm-dd hh24 '), ': 00:00 ') End_time from Dual Results: Query the day before Select

round--rounding according to specified precision Sql> Select Round (3.1415926,4) from dual; ROUND (3.1415926,4) ------------------ 3.1416 trunc--to intercept a number according to the specified precision Sql> Select Trunc (3.1415926,4) from

Here we are verifying an Oracle unconventional high efficient insert notation --Creates 2 test tables HYPER/HYPERW create table Hyper (a int); CREATE TABLE HYPERW (a int); --Insert 1 million records-note: This is a time consuming serious declare

1 Select all information about the employee in Department 302 List the number of employees whose position is (MANAGER), name3 Find bonuses above the salary of employees4 Find the sum of each employee's bonus and salary5 Find the manager in

Create a table space Create tablespace l_l datafile ' D:luo. DBF ' size 5m autoextend on next 4m MaxSize Unlimited; Create user default table space Create user Luo identified by 123456 default Tablespace l_l; Modify user Default space Alter

User name/Password login Identity descriptionSys/change_on_install SYSDBA or Sysoper cannot be logged in as NORMAL as the default system administratorSystem/manager SYSDBA or NORMAL cannot be logged on as a sysoper, as the default system

1, Sqlplus Sys/password as SYSDBA admin login 2, create temporary table space Change the red part into your own path Create temporary tablespace mytestTempfile ' D:\app\Administrator\oradata\orcl\mytest.dbf 'Size 200mAutoextend onNext 200m

Max min avg count sum1, the entire result set is a group1 to the Department 30 of the highest wages, minimum wage, average wage, total number of people who have work, the number of jobs and the sum of Wages copy code code as follows:Select Max

--Create automatic partition table:--Date type partition CREATE TABLE t_dpi_firstvisit_http (Date_tag DATE, Statdate VARCHAR (8), Acc_nbr VARCHAR2 (), CATEGORY V ARCHAR2, Sub_domain VARCHAR2 (1000), Total_flow number (a), total_duration number (a))

The weight of the word field View Duplicate data SELECT * from Testrepeat t where t.id in (select ID from testrepeat GROUP by ID has count (ID) > 1); Delete duplicate records, keep rowid the smallest rows Delete from Testrepeat t where t.id

Problem description When importing data to the Orale database ORA-01658: Unable to create a INITIAL area error for the segment in table Space XXX. This is due to the amount of data in the data file in the table space that exceeds the initialization

1. Query user (data) Table space Select UPPER (f.tablespace_name) "Table space name",D.TOT_GROOTTE_MB "Table space size (M)",D.tot_grootte_mb-f.total_bytes "used Space (M)",To_char (ROUND (d.tot_grootte_mb-f.total_bytes)/d.tot_grootte_mb * 100,2),' 9

A table if the amount of data is very large, if the table is not indexed, then any records in this table can only be scanned sequentially, which results in a large amount of IO input output, thus reducing execution efficiency. To speed up the query,

Oracle Security includes 2 major categories Oracle's database system security. Mainly includes the word system level to control user access to the system resources in the database, such as user name, password, user's system operation and the disk

First Kind Ora-28000:the account is lockedThe first step: the use of Pl/sql, login name System, the database name unchanged, select the type of time to modify the normal to SYSDBA;The second step: Select MyObject, view users;The third step: select

What is ORACLE   Oracle database system is one of the most popular database of client/server (client/server) or b/s architecture, which is provided by Oracle Corporation (Oracle), a group of software products based on distributed database. ORACLE