Study Notes--oracle

Basic statement

Log in to create a user and set permissions

#sqlplus/nolog sql> conn/as sysdba;　　Sql>create user username identified by password sql> Grant DBA to username; Sql> Conn Username/password sql> select * from user_sys_privs;//view Current user All rights sql> select * from user_tab_privs;// To view the user's permissions on a table User level: sys;//system administrator, with the highest privileges system;//local administrator, sub-high privilege scott;//Ordinary user, password default is tiger, default unlocked login: Sqlplus/as sysdba;//Login Sys  Account Sqlplus sys as sysdba;//ibid sqlplus scott/tiger;//landing Ordinary user Scott Admin User: Create user zhangsan;//under Administrator account, creating users Zhangsan alert  User Scott identified by tiger;//modify password grant permission: Grant create session to zhangsan;//grants Zhangsan user permission to create session, i.e. login permission grant Unlimited tablespace to zhangsan;//Grant Zhangsan user permission to use tablespaces grant CREATE table to zhangsan;//grants permission to make Tables grante drop table to zhangsan;//granting permission to delete a table grant Insert table to zhangsan;//right to insert Table permissions Grant Update table to zhangsan;//Modify table permissions grant all to public ;//This is more important, grant all permissions (all) for all users (public) grant DBAs, resource, connect to Zhangsan; permissions: Create session CREATE TABLE Unli　　mited tablespaceConnect resource DBA Create role: Create role myrole;//creating roles grant create session to myrole;//Grant Myrole Grant Myro permission to create session Le to zhangsan;//grants Zhangsan user Myrole role of drop role myrole;

Delete CREATE Database

drop user jingyan cascade;drop tablespace jingyan including contents;create tablespace jingyan datafile ‘D:\oradata\jingyan.DBF‘ size 200m reuse autoextend on next 50m maxsize 1000m;create user jingyan identified by 1 default tablespace jingyan;grant dba, resource, connect to jingyan;

Query the table space and file path in the current database, the default tablespace for the database user, the table space where the table resides

select tablespace_name, file_name from dba_data_files order by file_name;select user_id, username, default_tablespace from dba_users order by user_id;select table_name, tablespace_name from user_tables;select table_name from all_tables where owner = ‘USER‘;  //查看某用户的所有表名，需要有sysdba权限

View table Space Size usage

Modify the database default table space

alter database default tablespace test;

Create a data table

create table table_name(column1 type1, column2 type2, ...) tablespace tablespace_name;或者create table table_name as select ... from ...;

Create an index

CREATE INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名;

Example of a build:

create temporary tablespace DRGS_TEMP0 TEMPFILE ‘/data/oradata/oracle/oradata/orcl/DRGS_TEMP0.bdf‘size 100m reuse autoextend on next 20m maxsize unlimited;CREATE TABLESPACE "DRGS2017"     LOGGING     DATAFILE     ‘/data/oradata/oracle/oradata/orcl/drgs201701.dbf‘ SIZE 32000M,     ‘/data/oradata/oracle/oradata/orcl/drgs201702.dbf‘ SIZE 32000M,     ‘/data/oradata/oracle/oradata/orcl/drgs201703.dbf‘ SIZE 32000M,     ‘/data/oradata/oracle/oradata/orcl/drgs201704.dbf‘ SIZE 32000M,     ‘/data/oradata/oracle/oradata/orcl/drgs201705.dbf‘ SIZE 32000M,     ‘/data/oradata/oracle/oradata/orcl/drgs201706.dbf‘ SIZE 32000M  REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;create user drgsuser2017 identified by drgsuser2017 default tablespace DRGS2017 temporary  tablespace DRGS_TEMP0;grant connect,resource to drgsuser2017;grant exp_full_database,imp_full_database to drgsuser2017;grant dba to drgsuser2017;

Alter operation

alter table student add(class_id number);alter table student modify(class_id varchar2(20));alter table student rename column student_id to id;alter table student drop column class_id;alter table student move tablespace users;

Exporting data

First, spool commonly used settings set ArraySize 5000;    This parameter can increase the speed of spool unloading, can be set to 5000set autotrace on;　　　Set allow parsing of executed SQL set colsep ', ';　　　　Field output delimiter set echo off;　　Displays each SQL command in a script starting with start, onset feedback off by default;　　 echo the number of records processed by this SQL command, the default is on, setting displays "XX row selected" set heading off;  The output field title, the name of the field, defaults to onset linesize 2500;      The maximum number of characters allowed per line, set larger, so that the data is truncated, but not too large, too much reduced the speed of the export (note must be used in conjunction with Trimspool to prevent the exported text has too many trailing spaces) set newpage 1; To set the separation between pages and pages {1|n|      none}; 0 o'clock a small black box at the beginning of each page; n blank lines between pages and pages when the value is N, and when none, there is no interval between pages and pages; set NEWP none;          Set the number of pages to display the query, if you need continuous data, do not appear in the middle of the empty line to the NEWP set to none, so that the output of the data row is continuous, there is no empty row, such as set num 18;　  Set the length of the number, if not large enough, then use scientific notation to display set Numwidth 12;      Output Number Type field length, default is 10SET NULL text;　 When displayed, replace the null value with the text value set pagesize 2000; Output the number of rows per page, the page size, the default is 24, in order to avoid paging, can be set to 0set serveroutput on;        Setting allow display output similar to dbms_output;--when writing stored procedures, most of the necessary information will be output; SET SPACE 0;set term off;　　 No output on the screen execution result set termout off;      Shows the execution result of the command in the script, the default is onset timing on;　　　Displays the execution time spent on each SQL statement, setting the display "Elapsed time: XXXX" set trimout on; Remove trailing spaces per line of standard output, offset Trimspool o by defaultN        Remove redirect (spool) output trailing space per line, default to offset verify off//whether to show the substitution variable is substituted before and after the statement set wrap on; When the output line length is greater than the set line length (set with the Set linesize n command), when the value is on, the extra characters are displayed on another line, otherwise the extra characters will be cut off, not displayed; Spool usage Description: Spool not only can export data to TXT, can also export to CSV, etc., not only can lead the data, can also export characters, assembled into SQL files, etc. 1. The method of setting delimiter is explained: After setting the delimiter, it is not very use to sqlplus the field by using the delimited delimiter. Set Colsep ' | '--set | For column delimiter set Trimspool on set linesize set pagesize-set NewPage 1 set heading off SE 　　T term off set num-set Feedback off spool e:\temp.txtselect * from TableName; Spool off

To view the SQL processes that Oracle is running

set linesize 400;set pagesize 400;set long 4000;col SQL_FULLTEXT format a100;col machine format a25;col username format a15;SELECT a.username,a.machine, b.sql_id, b.SQL_FULLTEXTFROM v$session a, v$sqlarea bWHERE a.sql_address = b.address    AND a.SQL_HASH_VALUE = b.HASH_VALUE;

Special Data Sheet Dual
The dual table provides a single row of data formats so that various expressions and function operations can output a single-line single-column form when they are data sources.

select sysdate from dual;

Data type
Type | Explain
----------------|------------------------
CHAR | Fixed-length string, 1-2000 bytes in length, not specified as 1 bytes
VARCHAR2 | Variable length string, length 1-4000 bytes, size must be specified
LONG | Variable-length strings, up to 2GB, long text information, one table can only be one column, cannot be indexed
number | Numeric type
DATE | Storing date and time data in a table
TIMESTAMP | Store date, time, and time zone information
RAW | Binary data or byte string, length 1-2000 bytes, should be specified size, can be indexed
LONG RAW | Variable-length binary data with a maximum of 2GB, limited to a long type
CLOB | Large object data types, storing large numbers of character data
BLOB | Store a large number of binary objects
BFILE | Ability to store binaries in operating system files outside the database

SQL optimization
Operations that may cause a full table scan (index invalidation) should be avoided as far as possible:

1. Use NOT OR "<>" on the index column;
2. Use functions or calculations on indexed columns;
3. Not in operation;
4. Wildcards are located in the first character of a query string;
5. Is null or is not NULL;
6. A multi-column index, but its first column is not referenced by the WHERE clause;

SQL analysis
Explain usage

EXPLAIN PLAN FOR SELECT * FROM T_USER;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Some similarities and differences between Oracle and MySQL
1,oracle no offset,limit, in MySQL we use them to control the number of rows displayed, most of it is paging. If Oracle is paging, replace it with RowNum.
2,oracle has a dual table when the select does not have a table, plus. No error will be added. Select 1 This will not be the error in MySQL, Oracle next. Select 1 from dual this way, Oracle does not get an error.
3, the null value of the judgment, name! = "" This will not be an error in MySQL, but Oracle will error. Under Oracle to change to name is NOT NULL, (Innull (*))
4,oracle to single quotation marks, double quotation marks are required to die, generally not double quotes
5,oracle has to_number,to_date such a conversion function, the Oracle table field is number type, if you $_post get the parameter is 123456, when the storage, you also want to to_number to cast a bit, Otherwise it will be treated as a string. and MySQL doesn't.
6,group_concat This function, Oracle is WM_CONCAT.
7,group by, with group by under Oracle, the field behind group by must appear behind the Select, otherwise it will be an error, and MySQL will not.
8,oracle's table field type is also not MySQL much, and there are many different, for example: MySQL int,float synthesized the number type of Oracle.
9,oracle query when the from table name cannot be appended with as or will be error, select T.username from Test as T and under MySQL is possible.
10,oracle the function of intercepting a string is: substr (field name, start position, string length) starting position can start from 0, and the result is the same as starting from 1. MySQL intercepts the string function as: substring (field name, start position, string length) starting position must start from 1, 0 cannot get to the data.
11,oracle is not automatically submitted by default and must be submitted manually. MySQL default is auto commit

Problems:

garbled or displayed as??

1. Switch to the Oracle database installation user $ SU Oracle $ cd ~                            Go to the home directory of the Oracle user $ vim. bash_profile Edit the. bash_profile file under the Oracle user. bash_profile file contents: (Everyone's this file specific settings may not be the same, don't worry , pay attention to the red line. Path= $PATH: $HOME/bin:/us                                                R/bin:/usr/sbin:/sbin Export PATH                                                 Export ORACLE_SID=ORCL Export oracle_base=/u01/app/oracle                                                 Export Oracle_home= $ORACLE _base/product/10.2.0/db_1 Export path= $PATH: $ORACLE _home/bin Export Oracle_term=xter                       M                          Export ld_library_path= $LD _library_path: $ORACLE _home/lib                                              Export classpath= $ORACLE _home/jre:oracle_home/jlib: $ORACLE _home/rdbms/jlib Export Nls_lang=american_america. UTF8//Set this parameter is to set the user client's character sets, when creating the database does not set this parameter is no problem, it is best to plan your library needs to use when the type of font, and then set this value, it is best to keep the database consistent with, of course, this Parameters can be changed at any time. (My own database is using the UTF8 character set) $ source. Bash_profile//Execute the Source command to make the modified. Bash_profi Le file comes into effect now, set up, log in to the Oracle database again, query display is normal, but one thing to note is that if you insert data when the character set is not executed under UTF8, when your client and server have become UTF8 character set, the later inserted Chinese may The display is garbled. Therefore, only three points (client, operating system, database) of the character set is consistent, so that the Chinese normal display in the database.

Date format Issues

日期格式冲突问题          输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: ‘01-Jan-01‘          alter system set NLS_DATE_LANGUAGE = American scope=spfile         alter session set NLS_DATE_LANGUAGE = American scope=spfile         或者在to_date中写          select to_char(to_date(‘2002-08-26‘,‘yyyy-mm-dd‘),‘day‘,‘NLS_DATE_LANGUAGE = American‘) from dual;          注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，          可查看          select * from nls_session_parameters          select * from V$NLS_PARAMETERS      显示时间需要将日期型列转换成字符型，使用to_char(日期, ‘yyyy-mm-dd hh24:mi:ss‘)或者修改oracle缺省日期格式。

Restart Listener

lsnrctl status          //查看状态lsnrctl start            //开启监听器lsnrctl stop            //停止监听器lsnrctl reload         //重启监听器

Landing Oracle via IP

sqlplus username/[email protected]/ORCL

Not null and default order problems
Default must precede null or NOT NULL

CREATE TABLE datp0 (  GLOBALID number(11,0) DEFAULT ‘0‘ NOT NULL,  CYCLE number(11,0) DEFAULT ‘0‘ NOT NULL,  P1 varchar2(5) DEFAULT NULL,  ...);

Background execution SQL

nohup sqlplus oracle/[email protected] @sql.sql &

View Locked process

//查看被锁的表SELECT p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username,b.os_user_nameFROM v$process p, v$session a, v$locked_object b, all_objects cWHERE p.addr = a.paddrAND a.process = b.processAND c.object_id = b.object_id;// 查看是哪个进程锁的SELECT sid, serial#, username, status, osuser FROM v$session where serial# = ‘3789‘//查看指定进程的sql内容select sql_text from v$sqlarea s,v$session ses where s.address=ses.sql_address and ses.serial# =‘12345‘;// 杀掉这个进程alter system kill session ‘sid,serial#‘;//查看执行时间太长的sql语句select sid,serial#,sql_text,executions from v$sql join v$session on v$sql.sql_id=v$session.sql_id where cpu_time>20000;

Ora-00031:session marked for kill handles a lock that cannot be killed in Oracle

alter system kill session ‘sid,serial#‘;   //若执行此语句提示ORA-00031: session marked for kill，则进行如下处理select spid, osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=33;#kill -9 12345  //在OS界面终端执行

Learning Note--oracle