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 (

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

1. View All Users: SELECT * from Dba_users; SELECT * from All_users; SELECT * from User_users; 2. View user or role system permissions (directly assigned to user or role system permissions): SELECT * from Dba_sys_privs;SELECT * from

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

The start database command is divided into three phases: Startup Nomount ALTER DATABASE Mount ALTER DATABASE Open 1. View All Users:SELECT * from Dba_user;SELECT * from All_users;SELECT * from User_users;2. View User system permissions:SELECT *

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

use of the To_char () function in Oracle Ext: http://blog.csdn.net/jiangnan2014/article/details/16908585 (1) As Date conversion: to_char (date, ' format '); Select To_date (' 2005-01-01 ', ' YYYY-MM-DD ') from dual; Select To_char (sysdate, ' Yyyy-

--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

Select a.file_name,a.autoextensible,a.maxbytes,a.increment_by from Dba_data_files A; --autoextensible automatically expands--maxbytes maximum--increment_by number of automatic expansion blocks Sql> Show Parameter Db_blockNAME TYPE VALUE-----------

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

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

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