Oracle常用的数据库信息查询和CURD操作

oracle常用操作

• shell连接数据库
  sqlplus user/passwd@IP/server_name

数据库信息查询

-- 数据库版本查看
select * from v$version; 
-- oracle 密码修改
alter user life identified by 'NewPassword' replace 'OldPassword';
-- 查看表空间信息
select * from user_users
-- 查看用户的所有表
select table_name from user_tables;

-- 查看用户拥有的序列
select * from user_sequences;（普通用户即可）
select count(*) from dba_sequences where sequence_owner='SPACE_NAME';（名称需大写）
select SEQUENCE_NAME from dba_sequences  where sequence_owner='SPACE_NAME';

数据库CRUD相关

列

-- 修改列名：
ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN_NAME_OLD TO COLUMN_NAME_NEW;

-- 修改列类型：
ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME varchar2(16);

-- 新增一列
ALTER TABLE TABLE_NAME  ADD COLUMN_NAME varchar2(50);

-- 删除一列
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME

-- 复制一列数据
update 表 set 另一列 =这一列

行

-- 插入数据
INSERT INTO "TABLE_NAME"("COLUMN_NAME1", "COLUMN_NAME2" ) VALUES ('1', '2' );

-- 删除一行数据
delete from TABLE_NAME where KEY='SOMETHING' and FIELD='ANYTHING';

-- 一行数据设值
update 表名 set 列名=null where 条件;

注释

-- 注释：
COMMENT ON COLUMN TABLE_NAME.COLUMN_NAME IS '列描述内容';
COMMENT ON TABLE TABLE_NAME IS '表描述内容';

主键、外健、索引

--添加外键约束 
ALTER TABLE TABLE_NAME1_INFO(从表表名) ADD CONSTRAINT column_foreign_keyname(外键约束名称) foreign COLUMN1(列名) references TABLE_NAME2_INFO（主表表名）(DISBURSE_NO（列名）)
-- 这种方案为TABLE_NAME1_INFO添加了外键，指向TABLE_NAME2_INFO表的主键，该约束起到了保护数据完整性的作用：如果删除的用户信息(COLUMN1)
-- 已经在TABLE_NAME1_INFO表中使用，则该条数据无法删除；无法向TABLE_NAME1_INFO表中添加用户id不存在的地址信息。

-- 删除外键
alter table "TABLE_NAME1_INFO " drop constraint "column_foreign_keyname"

-- 唯一索引
CREATE INDEX "IDX1_TABLE_NAME1" ON  "TABLE_NAME1"("SHARE_DATE");

-- 新建联合索引
CREATE INDEX "IDX1_TABLE_NAME1" ON  "TABLE_NAME1"("SHARE_DATE"，"COLUMN2");

-- 查询索引
select * from user_indexes where table_name in ('表名');

-- 删除索引：
drop index 索引名;

-- 设置主键
	-- 建表语句中
	CONSTRAINT PK_OPERATION_MANUAL PRIMARY KEY (COLUMN1，COLUMN2)

	-- 后续新增
	ALTER TABLE "TABLE_NMAE " ADD CONSTRAINT "SYS_C61" PRIMARY KEY ("COLUMN_NAME1", "COLUMN_NAME2");

发生锁表的解决方法

-- 以数据库管理员账号登录
-- (1)锁表查询的代码有以下的形式：
select count(*) from v$locked_object;
select * from v$locked_object;

-- (2)查看哪个表被锁
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

-- (3)查看是哪个session引起的
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

-- (4)杀掉对应进程
执行命令：alter system kill session'3789,2807';
其中1025为sid,41为serial#.

批量删除数据库表

select ‘Drop table’ || table_name||’;’ from all_tables where owner=‘用户名’

select table_name from user_tables;

exp/imp

export NLS_LANG='AMERICAN_AMERICA.AL32UTF8'
// 导出
exp username/password@ip:1521/server_name BUFFER=64000 file=/tmp/oradump/backup_db.dmp log=/tmp/oradump/lvas_backup_db.log  owner=username

// 导入 
imp  username/password@ip:1521/server_name BUFFER=64000 file=/tmp/oradump/backup_db.dmp log=/tmp/oradump/backup_rel_db.log fromuser=username ignore=y