practical! Oracle database commonly used commands

Posted Jun 15, 20202 min read

This article mainly introduces the oracle query statement, helps the novice to quickly familiarize with the basic ORACLE grammar, helps the novice quickly familiar with the basic ORACLE grammar, friends who need it can be collected

oracle view user status

select username, account_status from dba_users;

View all characters

select* from from dba_roles;

Lock user

alter user XXX account; lock;

New user under Linux

useradd-d/home/XXX/s/usr/bin/ksh-m XXX
passwdXXX

User created

useradd-d/home/XXX/g-users-s/bin/bash-mXXX/G-users
passwdXXX

delete users

userdel-r XXX

Linux viewing process

ps-e|grep***    kill -9***(PID)

linux view version information

lsb_release-a

Give debug permission to users

grant, debug, any, procedure, debug, connect, session, prodb;

oracle query user

select username, password from dba_users;

oracle delete user

drop XXX cascade;

If you encounter an error
oracle cannot drop a user that is currently connected
Then execute the following command

Login as sysdba

shutdown immediate;
startuprestrict;
drop user*** cascade;
altered system disabled restricted session;

Oracle Monitor

lsnrctlstart

Ready to start

If there is a phenomenon that the sysdba user cannot connect to the database
Check environment variables ORACLE_HOME, ORACLE_SID

Delete symbol under sqlplus

ctrl+backspace

sqlplus remote connection

sqlplus username/password@10.10.10.10:1521/orcl

Query the relationship between the table space and the corresponding data file

select t1.name, t2.name from v$tablespace t1, v$datafile t2 where where t1.ts# = t2.ts#;

Delete tablespace

drop tablespace including contents and datafiles;

Fetch data repeatedly into table

insert into table_A(X, X, X, X) select X, X, X, X from table_B where XXX

table_A and table_B can be a table, of course, the data must be updated before inserting

Assign debug permissions to users

GRANT debug any procedure, debug connect session USER XXX;

View the connection number of different users

select username, count(username) from v$session where username is not not null by group username;

View execution log

select."SQL_TEXT",t."SQL_FULLTEXT",t."LAST_ACTIVE_TIME"from v$sqlarea where tPARS_SCHEMA_NAMEin('XXX') and SQL.TEXT similar to'%XXX%'

Collect and organize on the Internet

If there are errors or other problems, friends are welcome to leave comments and corrections. If you have any help, welcome to like + forward and share.

Welcome everyone to pay attention to the public number of the migrant worker brother:The technical road of the migrant worker brother
image.png