Several Methods for executing SQL statements using PLSQL

Plsql is very convenient for us to execute SQL. Below is a brief introduction to several commonly used SQL files (of course, each svn branch can also execute ant scripts to automatically execute the SQL files under a file)

First open the plsq command window

1) execute the SQL file (you can put the SQL statement to be executed into a file)

Input @''

Enter the path of the SQL file in single quotes, such as jbpm under D: \ db. oracle. SQL file, see (the SQL file content is select * from system_menu r where r. menu_name = 'sales order ';)

 

 

2) import the dmp file. Delete the corresponding user before importing the dmp file (test_user is used as an example below)

Drop user test_user cascade;
$ Impdp system/test123 @ SYSTEM directory = data_pump_dir schemas = test_user dumpfile = date. DMP REMAP_SCHEMA = test_user: test_userTABLE_EXISTS_ACTION = replace logfile = imp. log;
Alter user test_user identified by 123456;

 

 

3) when we need to restart the beta server or kill the session connection of the Application Server (such as tomcat) from the official database to the data beta version

The v $ session table shows the basic information about the applications connected to the oracle database. Therefore, you can kill the session of the corresponding program through this table.

If you want to kill the connection to user test_user, you can run the following SQL: select * from v $ session r where r. USERNAME = 'test _ user ';

Then kill the corresponding session. For details, refer to the following:

 

For example, if you want to kill the first one, execute the following SQL statement: alter system kill session '21, 77'; // Because sid, serial #. These two columns are unique.

 

 

The following describes the application information for connecting to oracle and the session information for oracle operations.

 1. Find the basic information of the application connected to the oracle database.

 

Select sid, serial #,
Username, -- connection username
Program, -- Application name
Machine, -- machine name
Osuser, -- Operating System User
Logon_time -- Logon Time
From v $ session;

2. How to view session-level wait events?

When we adjust the database performance, one of the most important reference indicators is the system waiting for events. $ System_event, v $ session_event, and v $ session_wait records system-level and session-level wait events, by querying these views, you can find out what database operations are waiting? Whether the disk I/O, the buffer is busy, or the plug-in lock, etc.

You can use the following SQL statement to query what each application is waiting for, and then adjust the database performance based on the information.
Select s. username, s. program, s. status, se. event, se. total_waits, se. total_timeouts, se. time_waited, se. average_wait
From v $ session s, v $ session_event se
Where s. sid = se. sidAnd se. event not like 'SQL * Net %'And s. status = 'active'And s. username is not null

 

3. query the locked table in oracle and release the session
Select. OWNER,. OBJECT_NAME, B. XIDUSN, B. XIDSLOT, B. XIDSQN, B. SESSION_ID, B. ORACLE_USERNAME, B. OS _USER_NAME, B. PROCESS, B. LOCKED_MODE, C. MACHINE, C. STATUS, C. SERVER, C. SID, C. SERIAL #, C. PROGRAM
FROM ALL_OBJECTS A, V $ LOCKED_OBJECT B, SYS. GV _ $ SESSION C
WHERE (A. OBJECT_ID = B. OBJECT_ID) AND (B. PROCESS = C. PROCESS) ORDER BY 1, 2

Release session SQL:
AlterSystemKillSession'Sid,Serial #'
AlterSystemKillSession'2017,21132'
AlterSystemKillSession'2017,6938'

4. View sessions that occupy a large amount of system io
SELECT se. sid, se. serial #, pr. SPID, se. username, se. status, se. terminal, se. program, se. MODULE, se. SQL _address, st. event, st. p1text, si. physical_reads, si. block_changes
FROM v $ session se, v $ session_wait st, v $ sess_io si, v $ process pr
WHERE st. sid = se. sid AND st. sid = si. sid AND se. PADDR = pr. addr and se. sid> 6 AND st. wait_time = 0 AND st. event not like '% SQL %' ORDER BY physical_reads DESC

5. Find sessions with high cpu consumption
Select a. sid, spid, status, substr (a. program, 60/100) prog, a. terminal, osuser, value/value
From v $ session a, v $ process B, v $ sesstat c
Where c. statistic # = 12 and c. sid = a. sid and a. paddr = B. addr order by value desc
6. You can use SQL statements to check whether oracle runs:

Select status from v $ instance;
Among them, status may return three values: open (open Database), mount (the database has been loaded, but it has not been opened), started (the database process has been started, but has not been loaded ), this data dictionary can be queried when the database is not opened, but must be executed by the sys user.
Response time. The number of requests must specify the parameter. You can refer to the dictionary; v $ status, v $ session (to see how many connected users are there ).