Common oracle database operation statements

Common oracle database operation statement [SQL] ------------------ SQL statement operation and help ------------------------ -- view the help information of a command help show; -- View all system variable value information show all; www.2cto.com -- view the current user show user; -- view the SGA size show sga; -- view the error message show errors; -- view the database version information show rel; -- view the system initialization parameter information show parameters; /* use the save command to save the SQL statement of the buffer to a separate file. The optional parameter is "crere" by default. If the created file rep exists, it overwrites the file, if the file does not exist, create an app. If the file exists, append it to the end. If the file does not exist, create the */save 'path \ file name 'crer/rep/app; /* use the get command to read the SQL statement in the specified file to the sqlplus buffer. optional parameter: list default, the buffer list statement nolist does not list the buffer statement */www.2cto.com get 'path \ file name' list/nolist; /* use the start command to read and run the file content */start 'path and filename '; sta' path and filename'; @ 'path and filename '; -- three methods have the same effect, you can also omit single quotes/* use the edit command to copy the content of the sqlplus buffer to afiedt. in the buf file and start the default Operating System editing tool to open */edit/ed 'filename '-- the file name can be omitted. The default value is afiedt. buf/* use the spool command to copy the output results from sqlplus to the specified file or send the query results to the printer until the spool off command is used. optional parameter: CRES default, create File rep if the file exists, overwrite it. If the file does not exist, create an app. If the file exists, append it at the end. If the file does not exist, create the file off and stop copying the output result from sqlplus to the specified file, disable the file out to start the function. Copy the sqlplus output result to the specified file */www.2cto.com spool/spo 'path \ file name 'cres/rep/app /; start/@ 'path \ filename '; spool/spo off; ------------------------------ variable declaration ---------------------------------- -- use & to declare temporary variables, use & declare to use the same temporary variable (take the emp table as an example) select & temp from emp where & temp> 2000; -- to represent two variables, you need to input select & temp from emp where & temp> 2000 twice -- to indicate a variable. Once you input -- to use a temporary variable, whether to display "original value new value" information set verify off; -- Do Not Display set verify on; -- display -- two declaration methods of Defined variables -- use define to declare the variable define temp = 100; -- declare acc [ept] variable name with accept variable type [format/for specified format] [default/def default] [prompt 'prompt content'] ompt/hide example: accept test number format 9999 prompt enter a value for test: 'hide; -- hide indicates hiding the input value -- delete the undefine variable name of the defined variable; ---------------------------- tablespace operation ------------------------------------------ -- Query table space usage select upper (F. TABLESPACE_NAME) "tablespace name", D. TOT_GROOTTE_MB "tablespace 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 * 990, 2), '2017. 99 ') |' % '"usage ratio", F. TOTAL_BYTES "Free Space (M)", F. MAX_BYTES "maximum block (M)" www.2cto.com FROM (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES)/(1024*1024), 2) TOTAL_BYTES, ROUND (MAX (BYTES) // (1024*1024), 2) MAX_BYTES from sys. DBA_FREE_SPACE group by TABLESPACE_NAME) F, (select dd. TABLESPACE_NAME, ROUND (SUM (DD. BYTES)/(1024*1024), 2) TOT_GROOTTE_MB from sys. DBA_DATA_FILES dd group by dd. TABLESPACE_NAME) d where d. TABLESPACE_NAME = F. TABLESPACE_NAME order by 1 -- query the current tablespace name of the database select tablespace_name, file_name file path, sum (bytes)/1024/1024 file size from dba_data_files group by tablespace_name, file_name; -- view the current instance www.2cto.com select name from v $ database; -- view the current database instance name and host name select instance_name, host_name from v $ instance; column host_name format a20; -- alignment display, optional; -- View All tablespaces select tablespace_name from dba_tablespaces; -- view the user's table select * from user_tables; -- view all users in the system select username, account_status from dba_users; -- View specific user permissions, when the sys Administrator logs on, select * from dba_tab_privs where grantee = 'sh'; select * from TABLE_PRIVILEGES where GRANTEE = 'll1'; -- view the permissions of the current user. When the current user logs on: select * from user_sys_privs; -- create a tablespace create tablespace name datafile 'path \ tablespace name. dbf'/* tablespace storage path */size 10 m/* initial capacity */autoextend on next 2 m maxsize 100 m/* tablespace automatic expansion, 2 m each time, the maximum value is 100 mb. You can set the maximum value to unlimited */logging www.2cto.com extent management local/* use the local management mode */segment space management auto; /* the middle part of the tablespace is managed automatically */-- delete the tablespace drop tablespace name including contents and datafiles; -- create temporary tablespace name tempfile '/oracle/oradata/db/temporary tablespace name. dbf 'size 50 M -- 1. offline alter tablespace name offline; -- if a data file is accidentally deleted, it must have the RECOVER option alter tablespace name offline for recover; -- 2. online alter tablespace name online; -- 3. offline alter database datafile 3 offline; -- 4. online alter database datafile 3 online; -- 5. make the tablespace read-only alter tablespace name read only; www.2cto.com -- 6. enable the tablespace to read and write the alter tablespace name read write;/* extend the tablespace to modify the file size: */alter database datafile 'd: \ fatafile. dbf 'resize 300 M; -- add a data file: alter dataspace userdb add datafile 'd: \ datafile2.dbf' size ...; -- set the tablespace data file to automatic extension: alter database datafile 'd: \ datafile2.dbf 'autoextend on next 5 M maxsize unlimited; -- rename the original tablespace name of alter tablespace to the new tablespace name; ---------------------------- user and permission ------------------------------ -- create user username identified by password default tablespace name; /* assign permissions to users or connect roles: Temporary user resource: a formal database user. You can create tables, triggers, processes, and other DBAs: database administrator role. Maximum permissions */grant connect, resource, dba to user name; www.2cto.com -- revoke user permissions or role revoke connect from qy; -- lock user account alter user Username account lock; -- unlock user account lock alter user Username account unlock; /* You can use conn/@ Instance name as sysdba to log on and change the password: alter user Username identified; */--------------------------- data table operation ---------------------------------- www.2cto.com -- allows the user to view the grant select on table name to user name in a table; -- allows the user to update the table name; desc table name; -- View table structure select table_name from user_tables; -- query the name of the data table under the current user. create index name on table name (column name); -- create index drop index name; -- delete index select index_name from user_indexes; -- Query all index names create view name as SQL query statement; -- create view create sequence name; -- create sequence name. nextval -- use sequence -- create a data table create table master (id number (6, 0) primary key, loginid nvarchar2 (50) not null, password nvarchar2 (20) not null, status char (1) default 1 not null); -- delete the table drop table name; ------------------------- constraint ---------------------------------- www.2cto.com -- add the syntax with the primary key and constraint: -- alter table name -- add constraint name constraint description -- add a primary key constraint (use StudentNo as the primary key) alter table Student add constraint PK_stuNo primary key (StudentNo) -- add a unique constraint (unique ID card number) alter table Student add constraint UQ_stuID unique (name of the ID card number column) -- add a default constraint (if the address is not specified, the default value is "Address Unknown ") alter table Student add constraint DF_stuAddress default ('address unknown ') for Address -- add check constraints (the date of birth must be later than January 1, January 1, 1980) alter table Student add constraint CK_stuBornDate check (BornDate> = '2017-01-01 ') -- add a foreign key constraint (the primary table Student establishes a relationship with the Result from the slave table, and the association column is StudentNo) alter table Result add constraint FK_stuNo foreign key (stuNo) references Student (stuNo) www.2cto.com -- delete constraint: alter table Name drop constraint name --------------------- sequence/* Create sequence, sequence is used to generate unique sequence values for the table's primary key. optional parameters: increment by specifies the value of the sequence to increase each time Start with specifies the first value of the sequence Maxvalue specifies the maximum value of the generated sequence Minvalue specifies the minimum value of the generated sequence Cycle specifies when the sequence value reaches the maximum or minimum value, whether the sequence is cyclic. cache specifies the number of cached values of the sequence generator at a time. Order specifies whether the values in the sequence are sorted by access Order. */create sequence name increment by 4 start with 50 maxvalue 60 minvalue 50 cycle cache 3; www.2cto.com -- View sequence, dual is the virtual table select sequence name. nextval from dual; select sequence name. currval from dual; ----------------------- column command use -------------------------------------- -- use the column command to format the specified column (take the emp table as an example) column empon heading 'employee No. 'format 9999; column ename heading 'employee name' format a10; column mgr heading 'superior number 'format 9999; column hiredate heading' employment date 'justify center; column sal heading 'employee payroll 'format $999,999, 99; select * from emp;