Common commands for manipulating Oracle databases at the shell terminal

This is a common Linux command used in a project to operate an Oracle database because it was not possible to connect remotely with Plsql at the time, and most operations needed to be done at the command-line window, summarizing

The first way

(1) Switch to Sqlplus first

Sqlplus/   SQL11.2.  0.4. 0  - £ º20181982, Oracle.  All rights reserved. SQL

(2) Connect Administrator account sys

Sql>Conn sys/ As  sysdba; is connected. SQL> Select *From  user_users; # View user information for the current connection USERNAME user_id Account_status   ------------------------------ ---------- --------------------------------lock_date expiry_date default_tablespace-------------- -------------- ------------------------------temporary_tablespace CREATED Initial_rsrc_consumer_group------------------------------ -------------- ------------------------------External_name--------------------------------------------------------------------------------SYS0OPEN --December- -systemtemp --August- -Sys_group

(3) Connect to ordinary users

Sql>Conn gd_xxx/ xxxxxx; is connected. SQL>Select*From User_users; USERNAME user_id Account_status------------------------------ ---------- --------------------------------lock_date expiry_date default_tablespace-------------- -------------- ------------------------------temporary_tablespace CREATED Initial_rsrc_consumer_group------------------------------ -------------- ------------------------------External_name--------------------------------------------------------------------------------Gd_base theOPEN --December- -WORKFLOW01TEMP11 in-June- -Default_consumer_group

(4) Exit Sqlplus

Sql> quit; 11.2. 0.4. 0 -

Sql> exit;
[Email protected] ~]$

The second way

Connect directly with Oracle user information after Sqlplus

[Email protected] ~]$sqlplus gd_base/[email protected]192.168. xxx.xxx/ oanetSQL*plus:release11.2.0.4.0Production on Thu July -  -: the: to 2018Copyright (c)1982, -, Oracle. All rights reserved. Connect to: Oracle Database 11g Enterprise Edition Release11.2.0.4.0-64bit Productionwith The partitioning, Oracle Label Security, OLAP, Data mining,oracle Database Vault and Real applic ation testing Optionssql>Select*From User_users; USERNAME user_id Account_status------------------------------ ---------- --------------------------------lock_date expiry_date default_tablespace-------------- -------------- ------------------------------temporary_tablespace CREATED Initial_rsrc_consumer_group------------------------------ -------------- ------------------------------External_name--------------------------------------------------------------------------------Gd_base theOPEN --December- -WORKFLOW01TEMP11 in-June- -Default_consumer_group

Other commands

(1) Monitoring related

[[Email protected] ~]$ lsnrctl status#查看监听服务的运行状态lsnrctl   forLinux:version11.2.0.4.0-Production on --July-2018  +:Geneva: -Copyright (c)1991, -, Oracle. All rights reserved. Connecting to (DESCRIPTION= (address= (protocol=tcp) (host=localhost) (port=1521)) LISTENER STATUS------------------------alias Listener version Tnslsnr forLinux:version11.2.0.4.0-Production Start Date in-June-2018  the: to: +Normal operating time -Days5Hours $Score of -sec trace level off security on:local OS authenticationsnmp off listener parameter file /oracle/product/11.2.0/dbhome_1/network/admin/Listener.ora Listener log files/oracle/diag/tnslsnr/zwdb/listener/alert/log.xml Monitoring Endpoint Overview ... (DESCRIPTION= (address= (protocol=tcp) (host=localhost) (port=1521)) ) (DESCRIPTION= (address= (PROTOCOL=IPC) (key=EXTPROC1521)))  Service Summary: Service"oanet"Contains1an instance. Example"oanet", state ready, containing this service's1a handler ... Service"Oanetxdb"Contains1an instance. Example"oanet", state ready, containing this service's1a handler ... Command execution succeeded

[[Email protected] ~lsnrctl start  # Start monitoring service

[[Email protected] ~lsnrctl stop   # Shutdown monitoring Service 

(2) Start and close the DB instance

after opening the listening service, log in with the SYS account [email protected]~]$Sqlplus/ nolog SQL*plus:release11.2.0.4.0Production on Thu July -  -: -: A 2018Copyright (c)1982, -, Oracle. All rights reserved. SQL>conn/As sysdba; # Log in with administrator rights is connected. SQL>Startup # Launches the instance and opens the database SQL>shutdown# Close Instance

Common commands for manipulating Oracle databases at the shell terminal