ORACLE数据库常用命令总结

素颜马尾好姑娘i 2022-10-16 03:00 231阅读 0赞

SELECT * FROM all_tables WHERE owner='icore_prdtdb';
    --查询当前用户下所有的表名
    SELECT  table_name  FROM user_tables;
    --查询当前用户下所有的表
    SELECT  *  FROM user_tables;
    --查询当前用户下所有的索引
    SELECT  *  FROM USER_indexes;
    --查询当前用户下所有的序列
    SELECT  *  FROM USER_SEQUENCES; 
    --查询当前用户下所有的视图
    SELECT  *  FROM USER_VIEWS; 
    
    --查询所有的表
    SELECT  *  FROM all_tables;
    --查询所有的索引
    SELECT  *  FROM all_indexes;
    --查询所有的序列
    SELECT  *  FROM all_SEQUENCES; 
    --查询所有的视图
    SELECT  *  FROM all_VIEWS;
    
    --查询所有的表名，类型表名含义 和 视图
    SELECT * FROM USER_tab_comments;
    
    
    SELECT SEQUENCE_OWNER,SEQUENCE_NAME FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='icore_prdtdb';
    --统计当前用户下序列的个数
    SELECT count(*) FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='icore_prdtdb';
    
    --删除序列
    DROP SEQUENCE  SequenceName;
    --删除视图
    DROP VIEW  ViewName;
    --删除索引
    DROP INDEX  IndexName;
    
     --查看死锁的语句
    select sql_text from v$sql where hash_value in(SELECT sql_hash_value from v$session where sid in (select session_id from V$LOCKED_OBJECT));
    
    
    select s.username,I.OBJECT_ID,I.SESSION_ID,s.SERIAL#,I.ORACLE_USERNAME,I.OS_USER_NAME,I.PROCESS FROM V$LOCKED_OBJECT I,V$SESSION S WHERE I.SESSION_ID=S.SID;
    
    ALTER SYSTEM KILL SESSION '20,11393';
    
    
     --查看死锁的语句
    select sql_text from v$sql where hash_value in(SELECT sql_hash_value from v$session where sid in (select session_id from V$LOCKED_OBJECT));
    sqlplus "/as sysdba"(sys/change_on_install) SELECT s.username,I.OBJECT_ID,I.SESSION_ID,s.SERIAL#,I.ORACLE_USERNAEM,I.OS_USER_NAME,I.PROCESS FROM V$LOCK_OBJECT I,v$SESSION S WHERE I.SESSION_ID=S.SID;
    select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;
    
    
    select username,lockwait,status,machine,program from V$session where sid in(select session_id from V$locked_object);
     --查看死锁的语句
    select s.username,I.OBJECT_ID,I.SESSION_ID,s.SERIAL#,I.ORACLE_USERNAME,I.OS_USER_NAME,I.PROCESS FROM V$LOCKED_OBJECT I,V$SESSION S WHERE I.SESSION_ID=S.SID;
    ALTER SYSTEM KILL SESSION '1721,7093';

# 查看死锁 #

1）用dba用户执行以下语句

select username,lockwait,status,machine,program from v$session where sid in
     (select session_id from v$locked_object)

如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：

Username：死锁语句所用的数据库用户；
    Lockwait：死锁的状态，如果有内容表示被死锁。
    Status： 状态，active表示被死锁
    Machine： 死锁语句所在的机器。
    Program： 产生死锁的语句主要来自哪个应用程序

2）用dba用户执行以下语句，可以查看到被死锁的语句。

select sql_text from v$sql where hash_value in
    (select sql_hash_value from v$session where sid in
    (select session_id from v$locked_object))

2、死锁的解决方法  
1）查找死锁的进程：

SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
    l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
    FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

2）kill掉这个死锁的进程：

alter system kill session ‘sid,serial#’; （其中sid=l.session_id）

3）如果还不能解决：

select pro.spid from v$session ses,
    v$process pro
    where
    ses.sid=XX
    and  ses.paddr=pro.addr;

参考：http://www.zuidaima.com/share/1607194957646848.htm  
http://blog.itpub.net/30345407/viewspace-2122531/

# Oracle ORDER BY子句基本用法 #

在Oracle中，表中是以非指定顺序存储行数据记录的，它不管行插入数据库的顺序如何。要按列以升序或降序查询行记录，必须明确指示Oracle数据库要如何去排序。

例如，您可能希望按名称的字母顺序列出所有客户，或者按照从最低到最高信用限额的顺序显示所有客户。

要对数据进行排序，我们可以将ORDER BY子句添加到SELECT语句中，参考以下语法：

SELECT
        column_1,
        column_2,
        column_3,
        ...
    FROM
        table_name
    ORDER BY
        column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST],
        column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST],

要按列排序结果集，可以在ORDER BY子句之后列出该列。

按照列名是一个排序顺序，可以是：

ASC表示按升序排序  
DESC表示按降序排序  
默认情况下，无论是否指定ASC，ORDER BY子句都按升序对行进行排序。如果要按降序对行进行排序，请明确使用DESC。

NULLS FIRST在非NULL值之前放置NULL值，NULLS LAST在非NULL值之后放置NULL值。

ORDER BY子句可以按多列对数据进行排序，每列可能有不同的排序顺序。

请注意，ORDER BY子句总是SELECT语句中的最后一个子句。

## 按列位置排序行示例 ##

SELECT name, credit_limit,address
    FROM customers
    ORDER BY 2 DESC, 1;

## UPPER()函数 ##

ORDER BY子句可在一列上应用一个函数，例如字符串函数，数学函数等，并按函数的结果对数据进行排序。

例如，以下语句使用ORDER BY子句中的UPPER()函数来区分客户名称的大小写：

SELECT customer_id, name
    FROM customers
    ORDER BY UPPER( name );

# Oracle AND与OR运算符结合示例 #

可以将AND运算符与其他逻辑运算符(如OR和NOT)组合，以形成一个条件。

例如，以下查询查找客户ID为44的订单，并且状态已取消(Canceled) 或 待定(Pending)。参考以下实现语句：

SELECT
        order_id,
        customer_id,
        status,
        salesman_id,
        TO_CHAR(order_date, 'YYYY-MM-DD') AS order_date
    FROM
        orders
    WHERE
        (status = 'Canceled' OR status = 'Pending')
        AND customer_id = 44
    ORDER BY
        order_date;

# 查看Oracle 版本信息 #

select * from v$version;
    select banner from sys.v_$version;

# 在表中新加字段 #

ALTER TABLE schema.TableName ADD columnName VARCHAR2(32);

# 修改字段长度 #

ALTER table schema.TableName MODIFY (字段名 字段类型(长度));      
    eg: alter table dpsm_dm_drugslog modify(person VARCHAR2(30));

# 加入注释 #

COMMENT ON COLUMN schema.TableName ADD columnName IS '注释';

# 创建schema #

create user schemaName identified by schemaName default tablespace users temporary tablespace temp;
    grant connect to schemaName;
    grant resource to schemaName;
    grant dba to schemaName;
    alter user schemaName quota unlimited on users;

# 创建mysql的schema #

--创建数据库
    create schema iiiis default character set utf8 collate utf8_general_ci;
    --立即启用配置
    flush  privileges;

# 查询某一列只包含数字的数据，并且值最大 #

select MAX(columnName) from schemaName.tableName where regexp_like(columnName,'^[0-9]+[0-9]$');

# 修改列值的长度 #

alter table schemaName.tableName modify (columnName,VARCHAR2(256));

# 删除某列 #

alter table schemaName.tableName drop column columnName;

# 备份表 #

create table tableNewName as select * from schemaName.tableName;

# 查看表结构 #

DESCRIBE schemaName.tableName;

# 查看建表语句 #

select dbms_metadata.get_ddl('TABLE','a') from dual;   //其中a为表名；

# 将备份表的数据还原到原始表 #

insert into schemaName.tableName select * from BackupsTableName where columnName='';//BackupsTableName为备份表的表名，columnName列名

# 数据库被锁的解决方法 #

--查看已错误输入次数
    select name, lcount from sys.user$ where name='TIGER';
    --查看用户的具体的被锁时间
    select username,account_status,lock_date from dba_users;
    --查看FAILED_LOGIN_ATTEMPTS的值，一般数据库默认是10次尝试失败后锁住用户
    select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS';
    --修改为无限次（为安全起见，不建议使用）
    alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
    --数据库解锁
    alter user myuser account unlock;

# 将入唯一约束条件 #

ALTER TABLE schemaName.tableName ADD CONSTRAINT UNIIQUENAME UNIIQUE(columnName1,columnName2);

# 删除唯一约束条件 #

ALTER TABLE schemaName.tableName DROP CONSTRAINT UNIIQUENAME;

# 查询表中字段值相同的数据 #

SELECT * FROM schemaName.tableName a 
    WHERE
    (a.columnName1,a.columnName2) 
    IN
    (SELECT b.columnName1,b.columnName2 
    FROM schemaName.tableName b
    GROUP BY b.columnName1,b.columnName2 HAVING COUNT(*)>1);