1.sqlplus Sys/[email protected] as SYSDBA with SYS as the system administrator to connect to the remote database ORCL name is located in the D:\app\lenovo\product\11.2.0\dbhome_1\ Network\admin\tnsnames.ora inside;
Or
Sqlplus/nolog logging in Sqlplus but not connecting to the database
Conn sys/x2145637 as Sysdba
Sqlplus statement too long can be used-hyphens (note distinguishing between SQL statements, Sqlplus statements, and PLSQL statements)
--user Scott unlocks alter user Scott account unlock;
--user Scott locking alter user Scott account lock;
--Change the password to Orclalter user Scott identified by ORCL;
2.sql Buffer retains only one statement
Clear buffer Empty buffer
SQL statement for List view buffer
You can modify the forward of the buffer
Sql>list 5 Current Behavior line 5th with the Run command changes the current row
Then sql>4 the 4th line of the current behavior
SQL>C/100/15 Change the line 100 to//sql>aa the current line all replaced with AA; 0 AA is quite equal to the first line of AA added
Add append and id<5 Add and id<5 after the current line
I and id<5 add a row after the current line and id<5
del * N Delete the current row to nth row in buffer
Del m n
Del *
del * Last deletes the current line in buffer to the final row
Run displays the SQL statement for the buffer and executes
/execute buffer for SQL statements
Save [filepath] Saving SQL statements
@?/sqlplus/admin/help/hlpbld.sql Helpus.sql @ Run script? For Oracle_home
3.desc Display table and view structure or call interface for a PL/SQL function
Replace variable select * from a where id=&myid; myID can be entered
4 using PL/SQL statements
Start by typing begin or declare
Begin
For I in 1.. 10
Loop
INSERT into t values (I, ' boobooke ');
End Loop;
End
/
Show Serverout display server-side output status
The client can receive the output from the server after set serverout on is on
Declare
x varchar2 (n): = ' Hello world ';
Begin
Dbms_output.put_line (x);
exception
When others then
Dbms_output.put_line (' Error occurred! ');
End
/
. For storage but not execution/for execution
5.host or! You can follow the command of the operating system later. Switch to the operating system shell after not following the command
Shell and Sqlplus nested execution parent Process child process
6.show pause shows the paused state
Show pagesize shows how many rows a table page displays
Set pause on pause, displaying a table page per press ENTER
Set pause ' ~~~~~~~~~~~~~~~~~~~~~~~ ' table page is separated by a wavy line
7truncate table m Empty m meter
Commit command does not execute this command all SQL operations are only put in memory
Or quit automatically when quit Sqlplus
Turn on auto-commit now
Show autocommit display Open status
Set autocommit on open set autocommit 10 10 statement Auto commit plsql only one bar
8. Editing scripts
Define command defining environment variables
Under Linux define _EDITOR=VI use Edit sale to call VI to create and edit Sale.sql scripts saved in the current default directory
REM */*-3 Notes command
Spool Save query statement to file and output on screen
Spool filename
SELECT statement
Spool off
Role: Bulk Delete table
Spool Droptable.sql
Spool off
Set pagesize 0
Set Feedback off
Spool Droptable.sql
Select ' Drop table ' | | object_name | | '; ' from user_objects where object_type= ' tyble ';//nested query shows the bulk drop table code, in single quotation mark as character constant, | | To separate symbols
Spool off
Show user Current users
Drop user DZYSC cascade; object is present, indicating to be deleted together with the object
Oracle Queries All Users
SELECT * FROM All_users or select * FROM Dba_users or select * from User_users;
View User system permissions S
Elect * from Dba_sys_privs;select * from All_sys_privs;
View User Object permissions
SELECT * from Dba_tab_privs;
SELECT * from All_tab_privs;
View all roles
SELECT * from Dba_roles;
CREATE TABLE Space
Create Tablespace MySpace
DataFile ' E:\app\myspace\myspace.dbf '
Size 10M autoextend on next 5M maxsize 100M;
View Table Spaces
Select Tablespace_name from Dba_tablespaces/user_tablespaces;
View User table names
Select table_name from User_tables;
View Datefile
Sql> select name from V$datafile;
View Tempfile
Select name from V$tempfile;
Querying table names under the Users table space
Select table_name from all_tables where tablespace_name= ' USERS ';
Allocate table space quotas
Sql>alter USER a QUOTA UNLIMITED on A3; --a user can use A3 table space indefinitely
Or
Sql>alter USER A quota 100M on A3; --a users can use data from the 100MA3 table space
Grant SELECT on t to test;
Grant the query permission for table T to the test user
1. Authority and Recovery
System permissions
Grant create session,create table to test with admin option;
Revoke create session,create table from test;
Object permissions
Grant Select on book to test with GRANT option;
Revoke select on book from Test;
Create session is granted permission to login
With admin option Plus Administrator privileges
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Use of Sqlplus
