3 years ago, Oracle command-line notes are still common.

Col Column name for (MAT) format: Columns can be formatted (A100 is a varchar-based format that displays 100 characters, which can be A60,A80), for example: col file_name format a100;col column name: Display the format of the forefront Clear Co Lumn: Clear the format of all columns//hostname host hostname;//help help index;//show the current change value shows all//the previous bug show err;//show the repository version shows rel// View Oracle version conn/as Sysdbaselect * from v$version;//View global service name Select Global_name from global_name;//database name Select name from V$da Tabase; SHOW PARAMETER db//Instance name select instance_name from V$instanceshow PARAMETER INSTANCE//database domain Select VALUE from V$parameter WHE RE name= ' Db_domain '; SHOW PARAMETER domain;//Database service name Select VALUE from V$parameter WHERE name= ' service_name '; SHOW PARAMETER service_name; login listener lsnrctl start listener lsnrctl Start Listener | Lsnrctlstart Listener Close listener Lsnrctl Stop Listener | Show Listener status Lsnrctlstatus listener//View more detailed listener services//test client to server connection tnsping myorcl//login 1 sqlplus2 [email protected] admin//Check for network failure ping 127.0.0.1//sql; end/New Line end Beginend/new row Execute operating system command in Sqlplus command host dir d: Close plusexit or quitor ctrl+z//connection number Data base Connect system/[email protected]//Exit Database Disconnect//Importing SQL files @f:\banksystem.sql//configuring TOMCAT environment variables Catalina_home=h:\tomcat 6.0tomcat_home=h:\tomcat 6.0classpath=% tomcat_home%\lib\servlet-api.jar;tomcat_home%\lib\jsp-api.jar//Creating table Spaces Create tablespace userdbdatatile ' F:\oracl Tool\ '//Switch User Sqlplussystemadminconn scott/admin--Toggle show user;//Disconnect current database disc scott;//Modify user password passw//run SQL script start e:\ A.sql or @ e:\a.sql//edit sqledit e:\a.sql;//intercept screen Select data to specified file spool D:\b.sql;select * from Emp;spool off;//sql statement ' &a ' change The measure replaces the SELECT * from emp where ename= ' &a '; Adjust the line's display set linesize 90;select * from emp;//line Show show linesize;//paging set pagesize 5;select * from emp;//CREATE database user created users no Key identified by ORCL;//ORCL is the password//modify other database user password alter user nokey//delete users drop username Nokey; if there is a table in Nokey, you need to take a parameter cascade to delete/ /Specify the user for the Tablespace alter users demo default Tablespace demotsdata; Grant to the specified user (table operation Connect) grant connect to nokey;//assign user authorization (table Space Operation Resource) grant resource to nokey;//operation of the repository grant DBA to Noke y;//view Table Structure desc test;//give Object Permissions view table grant Select on EMP to nokey;//view empowered table select * FROM SCOTT.EMP;//SOCTT represents a role//give object permission to modify table Grant Update on EMP to nokey;//give EMP access to test (Modify/delete delete \ query \ add SOCTT EMP table) Grant AL L on EMP to test//retract Nokey permissions on the EMP table revoke select on EMP from nokey;//rights Maintenance//nokey query Scott EMP Table permissions passed to other users//view all user Select Usern Ame from dba_users;//Delete user Myorcl cascade;//Delete the table space and the library file (delete the time table space and the library file) drop tablespace tablespace_name Including contents and Datafiles;drop tablespace tablespace_name including contents and datafiles;//View all tables SELECT Table_na ME from all_tables//View pre-user all tables Select table_name from all_tables WHERE owner= ' MYORCL ';//view pre-user tablespace select Username,default _tablespace defspace from Dba_userswhere username= ' MYORCL '; Select Username,default_tablespace,temporary_tablespace from Dba_users WHERE username= ' MYORCL ';//Enquiry All Tables Space Select Tablespace_name from dba_tablespaces;//user information for space distribution Select Tablespace_name,owner,sum (bytes) from Dba_segments GROUP by tablespace_name,owner;//Pre-user All tables space Select Tablespace_name from user_tablespaces;//view table space Size Select A.name,sum (b.byTES)/1024/1024 from V$tablespace a,v$datafile B WHERE a.ts#=b.ts# GROUP by a.name;//Inquiry table space free space Select Tablespace_name,sum ( BYTES)/1024/1024 from Dba_free_spacegroup by tablespace_name;//create the time table space creates temporary tablespace user_temptempfile ' F : \data_temp. DBF ' SIZE 50M autoextend ONNEXT 50M MAXSIZE 10280MEXTENT MANAGEMENT LOCAL; Create a table space create Tablespace user_datadatafile ' F:\USER_DATA.  DBF ' SIZE 50MAUTOEXTEND ONNEXT 50M MAXSIZE 10280MEXTENT MANAGEMENT LOCAL; Create a user and specify the table space create MYORCL identified by Admindefault tablespace user_datatemporary tablespace user_temp;//conn SYST Em/admin//grant Connect,resource to myorcloracle resize tablespace file size 1, adjust tablespace file size ALTER DATABASE datafile '/u01/app/oracle/oradata     /rmanrep/users01.dbf ' Resize 15m;2, adjust tablespace file auto-expand ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/RMANREP/USERS01.DBF '     Autoextend on next 20m maxsize 1g;3, new disk ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/RMANREP/USERS01.DBF '   Autoextend on next 20m maxsize 1g; Alter TABLESPACe userdb add datafile '/u03/oradata/users02.dbf ' size 50m autoextend on next 10m maxsize 200M5, modify table space name alter TABLESP Ace UserDB Rename to userdb2//add column ALTER TABLE EMP01 add ENO number (4);//Modify Column definition ALTER TABLE EMP01 MODIFY job VARCHAR2 (15); /delete column ALTER TABLE EMP01 DROP column dno;//Modify the column name of ALTER TABLE EMP01 RENAME column Eno to empno;//modify tables name RENAME EMP01 to employee;// Modify the default tablespace to a specified tablespace alter DATABASE default tablespace user_data;//add comment comment on table employee is ' store employee information ';　COMMENT on TABLE employee.name is ' description of employee name ';　　1, copy the table structure of SQL CREATE TABLE B as select * from a where 1<>1 2, that is, copy the table structure and copy the data in the table SQL CREATE TABLE B as select * from a 3. The SQL CREATE table B as select Row_id,name,age from a where 1<>1//assumes that row_id,name,age are column 4 of table A, the specified field of the replicated table, and the reference　　SQL CREATE TABLE B as select Row_id,name,age from a statement of the data of the fixed field although it is easy to create B table according to the structure of a table, but the index of a table is not replicated, it needs to be established manually in B. 5. Insert into will save the query results to an existing table insert into T2 (column1, Column2, ....) select Column1, Column2, .... from T1//sql Insert Time Date class Type INSERT inTo Person (Id,name,age,brithday,money) VALUES (1, ' Zhang San ', $, to_date (' 2011-01-01 ', ' yyyy-mm-dd hh24:mi:ss '), 30.0); INSERT into person VALUES (' Zhang San ', Name,age,brithday,money, to_date (' 2011-01-01 ', ' yyyy-mm-dd hh24:mi:ss '), 30.0);//For person ID for self-increment//create sequence Create SEQUENCE Seq_idstart with 1--Start value maxvaule 99999999999999--Maximum 99999999999999 minvaule 1-Minimum 1 N   Ocycle-The maximum value of the sequence is not followed by the cycle cache 100;   --Cache 100 sequence values create SEQUENCE seq_idincrement by 1START with 1 nomaxvalue nocycle NOCACHE; CREATE OR REPLACE TRIGGER Tri_userbean before INSERT on usertest for each ROW BEGIN IF (: New.id was NULL) then SELECT seq_use Rbean. Nextval INTO:NEW.ID from DUAL; END IF; END; /CREATE or REPLACE TRIGGER Tri_person before INSERT on person for each ROW if (New.id is NULL) BEGIN SELECT seq_id_per SON. Nextval INTO:NEW.ID from DUAL END; /create TRIGGER Tri_ins_person before INSERT on person for each ROW if (New.id is NULL) Beginselect Seq_id_person. Nextval INTO:NEW.ID from dualend;//creating a trigger Create OR REPLace TRIGGER Trigger_person before insert on person--insert pre-trigger for each row--increases the BEGIN-SELECT seq_id for every row. Nextval INTO:NEW.ID from DUAL; --sequence to the next value end;//Delete triggerdrop TRIGGER trigger_person;//disable and enable trigger alter TRIGGER <trigger_name> Disable;alter  Trigger <trigger_name> enable;//View Pre-user Current table trigger SELECT * from User_triggers WHERE table_owner= ' MYORCL ' and table_name =upper (' person '), select * from user_triggers where table_owner = ' xxx ' and table_name = UPPER (' table_name '); View all triggers for this user select * from dba_triggers where owner= user name;//View all foreign KEY constraints in this user, table_name shows which table is built on select * from Dba_ Constraints T where owner= user name and t.constraint_type= ' F ';//empty cache 10g above alter system flush Buffer_cache;9ialter SESSION SET EV Ents ' immediate trace name Flush_cache ';//Change Chinese report environment to English alter SESSION SET nls_language= ' AMERICAN '//select * from Nls_ Database_parameters;alter Database Nls_characterset = zhs16gbk;//Registration Form Nls_lang Simplified Chinese_china. ZHS16GBK Chinese value Chinese_china. Zhs16gbkamerican_amerIca. UTF8 English value American_america; American_america. We8iso8859p1simplified American_america. UTF8//Prompt in English, code for Chinese American_america. ZHS16GBK//CT Table CREATE Table CT (ID number PRIMARY KEY not NULL, NAME VARCHAR (TEN) not NULL, age number is not NULL, DEP NU Mber not NULL);//Procedurecreate PROCEDURE Prol (ID number) as V number; BEGIN INSERT into CT VALUES (V, ' Zhangsan ', 1, 1); COMMIT; END; EXECUTE performs//execute Prol (2)//block execute Declarebeginprol (2); END//constant definition Constantid CONSTANT number: =30;//no stored process create OR REPLACE PROCEDURE Pro_person (PARAMENT1 varchar2,parament2 Number,parament3 date,parament4 FLOAT) Asbegininsert into person (Name,age,brithday,money) VALUES (parament1,parament2 , PARAMENT3,PARAMENT4); end;//returns a reference to create OR REPLACE PROCEDURE pro_person_reture (PARAMENT1 varchar2,parament2 out number) Asbeginselect age into PARAMENT2 from the person WHERE name=parament1; end;//when inserting id//the first CREATE OR REPLACE PROCEDURE Pro_last_id_person (PARA5 out number,para1 varchar2,para2 number,para3 D Ate,para4 FLOAT) as BEGIN INSERT into person (Id,name,age,brithday,money) VALUES (Seq_id_person. NEXTVAL,PARA1,PARA2,PARA3,PARA4); SELECT Seq_id_person. Currval to PARA5 from DUAL; END; Second CREATE OR REPLACE PROCEDURE pro_last_id_person1 (PARA5 out number,para1 varchar2,para2 number,para3 date,para4 Floa T) as BEGIN INSERT into person (Name,age,brithday,money) VALUES (PARA1,PARA2,PARA3,PARA4); SELECT Seq_id_person. Currval to PARA5 from DUAL; END; Storage process return list//build package Create OR REPLACE packages person_package astype person_cursor is REF CURSOR; PROCEDURE Pro_person_get (c_ref out person_cursor); end;///save Process CREATE OR REPLACE package BODY person_package as PROCEDURE pro_person_get (C_ref out person_cursor) is BEGIN O PEN c_ref for SELECT * from person; END Pro_person_get; END Person_package;     View trigger set long 50000;     Set heading off;     Set PageSize 2000;              Select ' Create or replace trigger ' | | trigger_name | | '"' ||      Chr (10) | | Decode (substr (Trigger_type, 1, 1), ' A ', ' after', ' B ', ' before ', ' I ', ' INSTEAD of ') | |      Chr (10) | | triggering_event | |      Chr (10) | | ' On ' | | Table_owner | |            '"."' || table_name | | '"' ||      Chr (10) | | Decode (InStr (Trigger_type, ' each row '), 0, NULL, ' for each row ') | | Chr (Ten), trigger_body from User_triggers;     View the status of functions and procedures select Object_name,status from user_objects where object_type= ' function ';     Select Object_name,status from user_objects where object_type= ' PROCEDURE ';     SELECT object_name from user_objects WHERE object_type= ' TRIGGER ';   View the source code of the function and procedure select text from All_source where Owner=user and Name=upper (' name ');      SELECT TEXT from All_source WHERE type= ' package BODY ' and owner= ' SYSTEM ' ORDER by Name,line; Procedure,function, package, trigger the definitions of these database objects can be obtained in a similar way. Transactionsavepoint aselect * from person; UPDATE person SET age=30 WHERE name= ' Zhangsan '; SELECT * from person; ROLLBACK to Aselect * from Person;//transaction ' S namesetTRANSACTION NAME ' INSERT into table test_transaction '//insert to databaseinsert into table test_transaction VALUES (' KKK K ', 20,to_date (' 2011-10-10 ', ' yyyy-mm-dd HH24:MI:SS '), 20.8);//SELECT transactionselect NAME from v$transaction;// COMMIT; SELECT NAME from v$transaction;//transactional consistency (Transaction-level read consistency)//Session 1 window 1//read onlyset TRANSACTION READ only ;//2select * from person;//Session 2 window 2INSERT into TABLE test_transaction VALUES (' Kkkk ', 20,to_date (' 2011-10-10 ', ' yyyy-mm-dd HH24:MI:SS '), 20.8); commit;//Session 1 Window 1SELECT * from Person;//commit; SELECT * FROM person;//integrity CONSTRAINTS integrity constraint//create tablecreate TABLE integrity_test (ID int,name VARCHAR2 (10));// Alter COLUMN of table for PRIMARY key ALTER table Integrity_test ADD PRIMARY key (id,name);//deletealter TABLE Integrity_te ST DROP PRIMARY key cascade;//alter COLUMN of table for PRIMARY key and CONSTRAINT namealter TABLE integrity_test ADD CONS Traint PK1 PRIMARY KEY (id,name)//delete altert TABLE integrity_test DROP pk1;//create UNIQUE Constraintdrop TABLE integrity_test; 1 CREATE Table Integrity_test (ID INT unique,name VARCHAR2)//2alter TABLE integrity_test ADD UNIQUE (id,name);// Deletealter table Integrity_test DROP Unique (ID,NAEM)//3alter table integrity_test ADD CONSTRAINT PK3 Unique (id,name); /deletealter table Integrity_test DROP CONSTRAINT PK3 cascade;//create CHECK constraintsdrop table integrity_test;// CREATE check CONSTRAINT of tablecreate TABLE integrity_test (ID int,name VARCHAR2), NUM number CONSTRAINT C_check CHECK (NUM between and 20)); /add Check CONSTRAINT of table ALTER table Integrity_test ADD CONSTRAINT c_check CHECK (NUM between 1 and);//delete ALT ER TABLE integrity_test DROP CONSTRAINT c_check;//alter COLUMN VALUES are null OR NOT NULL of table ALTER table INTEGRITY _test MODIFY (ID NULL); ALTER table Integrity_test MODIFY (ID not NULL);//foreign key//create STUDENT CREATE TABLE STUDENT (SID int,cid int,name VA RCHAR2 (), CONSTRAINT PK1 PRIMARY KEY (SID));//create collegecreate TABLE COLLEGE (CID int,cname VARCHAR2), caddress VARCHAR2 (+), CONSTRAINT PK2 PRIMARY KEY (CID));//constraints FOREIGN Keyalter table STUDENT addconstraint fk1foreign KEY (CID) REFERENCES COLLEGE (CID);//or CONSTRAINT FOREIGN keyalter TABLE ST Udent addconstraint fk1foreign KEY (CID) REFERENCES COLLEGE (CID) on DELETE cascade;//delete CONSTRAINTS FOREIGN key//if TAB LE is COLLEGE's delete then ALTER TABLE COLLEGE drop CONSTRAINT FK1//or useing CASCADE CONSTRAINTS to DELETE DROP TAB LE COLLEGE CASCADE constraints;//all TABLES and all CONSTRAINTS SELECT table_name,constraint_name from user_constraints;/ /view table Constraints SELECT Constraint_name,constraint_type from user_constraints WHERE table_name=upper (' Department ') and Owner= user;//View value SELECT column_name,position from User_cons_columns WHERE constraint_name= ' sys_c0011056 ';

3 years ago, Oracle command-line notes are still common.