oracleSQL语句
在linux怎么操控oracle建表
登陆linux,以oracle用户登录（如果是root用户登录的，登陆后用su - oracle命令切换成oracle用户）
以sysdba方式来打开sqlplus，命令如下：sqlplus / as sysdba
oacle通过sql语句创建表空间并授予用户权限
1.管理员用户登录oracle数据库
sqlplus / as sysdba
conn 用户/密码; ----普通用户登录数据库
启动数据库
startup
停止数据库
shutdown immediate
shutdown abort ---强制删除
2.查询相关sql语句
（1）查看用户的数据库名
SELECT NAME FROM V$DATABASE;
[root@oracle ~]# env | grep ORA
ORACLE_SID=orcl //显示当前数据库实例名
ORACLE_BASE=/usr/local/oracle/app //oracle的根目录
ORACLE_HOME=/usr/local/oracle/app/oracle/product/11.2.0/dbhome //oracle产品目录
（2）oracle数据库中查询实例名
方法一：select name from v$database;
方法二：SELECT host_name, instance_name, version FROM v$instance;
（3）查看当前用户所在表空间
select username,default_tablespace from user_users;
（4）oracle查询表空间所有表
select table_name from all_tables where TABLESPACE_NAME='表空间' 表空间名字一定要大写。
（5）oracle查询表所有的表空间
select * from user_tables where table_name=‘表名';表名一定要大写；
（6）查询用户拥有哪里权限：
SQL> select * from dba_role_privs;
SQL> select * from dba_sys_privs;
SQL> select * from role_sys_privs;
（7）监视用户：
1、查询用户会话信息：
SQL> select username, sid, serial#, machine from v$session;
2、删除用户会话信息：
SQL> Alter system kill session 'sid, serial#';
3、查询用户SQL语句：
SQL> select user_name, sql_text from v$open_cursor;
（8）查询用户的所有系统权限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'ARWEN';
（9）查看当前用户拥有的角色权限信息
select * from role_sys_privs;
（10）查看所有用户
select * from all_users;
(11)查看当前用户的详细信息：
select * from user_users;
(12)查看当前用户的角色信息：
select * from user_role_privs;
(13)查询数据库上操作的所有命令，需要有dba权限
select * from v$sqlarea t order by t.FIRST_LOAD_TIME desc
(14)查询主键、外键
select * from dba_constraints;
(15)查询索引
select * from dba_indexes;
(16)查询一张表里面索引
select * from user_indexes where table_name=upper('bills');
(17)查询被索引字段
select * from user_ind_columns where index_name=('in_bills');
(18)给某一字段创建索引
create index in_bills on bills(account_id);
查看表空间
select username,default_tablespace from dba_users;
查看控制文件
SELECT NAME FROM v$controlfile;
查看日志文件
SELECT MEMBER FROM v$logfile;
查看数据库的创建日期和方式
SELECT created, log_mode, log_mode FROM v$database;
查看当前连接数据库的主机数：
col machine for a20
set linesize 150
select distinct machine,username from v$session order by username,machine;
查询用户会话
select username,serial#,sid from v$session;
alter system kill session 'serial#,sid'; -- 删除相关用户会话
查询 oracle 的连接数
select count(*) from v$session
查询oracle 的并发连接数
select count(*)from v$session where status='ACTIVE';
查看oracle 的版本
select banner from sys.v_$version;
oracle增加/更改sql语句
oracle授权给其他系统用户登录oracle的权限
1.普通用户赋予所有权限
grant all to user；
2.赋予部分权限
grant create session,select any table,dba to user;
授权命令：SQL> grant connect, resource, dba to 用户名1 [,用户名2]...;
将表的操作权限授予全体用户：
SQL> grant all on product to public; // public表示是所有的用户，这里的all权限不包括drop。
[实体权限数据字典]:
SQL> select owner, table_name from all_tables; // 用户可以查询的表
SQL> select table_name from user_tables; // 用户创建的表
SQL> select grantor, table_schema, table_name, privilege from all_tab_privs; // 获权可以存取的表（被授权的）
SQL> select grantee, owner, table_name, privilege from user_tab_privs; // 授出权限的表(授出的权限)
重设密码
alter user 用户 identified by 密码；
创建表
create table z_test(id number,name varchar(20));
插入表数据
insert into z_test select 1,'a'from dual;
语法: INSERT INTO table [(column1,column2,...)] VALUE (value1,value2,...)
例子： insert into dep (dep_id,dep_name) values(1,'技术部');
一表多行插入
INSERT [ALL] [condition_insert_clause]
[insert_into_clause values_clause] (subquery)
例子：INSERT ALL
INTO sal_history(emp_id,hire_date,salary) values (empid,hiredate,sal)
INTO mgr_history(emp_id,manager_id,salary) values (empid,hiredate,sal)
SELECT employee_id empid,hire_date hiredate,salary sal,manager_id mgr
FROM employees
WHERE employee_id>200;
有条件的Insert
语法:
INSERT [ALL | FIRST]
WHEN condition THEN insert_into_clause values_clause
[WHEN condition THEN] [insert_into_clause values_clause]
......
[ELSE] [insert_into_clause values_clause]
Subquery；
例子：Insert All
when id>5 then into z_test1(id, name) values(id,name)
when id<>2 then into z_test2(id) values(id)
else into z_test3 values(name)
select id,name from z_test;
修改用户：
SQL> Alter User 用户名
1、修改口令字：
SQL>Alter user acc01 identified by “12345”;
2、修改用户缺省表空间：
SQL> Alter user acc01 default tablespace users;
3、修改用户临时表空间
SQL> Alter user acc01 temporary tablespace temp_data;
4、强制用户修改口令字：
SQL> Alter user acc01 password expire;
5、将用户加锁
SQL> Alter user acc01 account lock; // 加锁
SQL> Alter user acc01 account unlock; // 解锁
oracle删/改/查sqlyuju
删除用户：SQL> drop user 用户名 cascade; //加上cascade则将用户连同其创建的东西全部删除
删除表空间
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES
删除用户
SQL>drop user 用户名; //用户没有建任何实体
SQL> drop user 用户名 CASCADE; // 将用户及其所建实体全部删除
*1. 当前正连接的用户不得删除。
创建表空间
create tablespace 表空间名
datafile '/usr/local/oracle/app/表空间名.dbf'
size 100M reuse autoextend on next 40M maxsize unlimited
default storage(inital 128K next 128K minextents 2 maxextents unlimited);
创建新用户
create user new_username
identified by "new_password"
default tablespace new_tablespacename
profile default
account unlock;
给新建用户授权dba权限
grant dba to new_username;
grant unlimited tablespace to new_username;
选择表查询
select * from z_test;
重命名表空间
在表空间为ONLINE的情况下
ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name ；
在表中添加字段：
alter table 表名 字段名 字符长度
注：在表中的修改字段：
alter table 表名 modify(字段名 字符类型 长度）
23.oracle 中如何设置可上翻键，使用历史命令。
Oracle 默认是不可以的，需要下载一个rlwrap 插件即可。参考下载地址：http://v.yingsun.net/cobra/download/
下载下来之后，只需在oracle 的家目录下的环境变量进行设置一个别名即可：
如下： alias sqlplus='rlwrap sqlplus'
设置完之后即可用。
创建用户的Profile文件
SQL> create profile student limit // student为资源文件名
FAILED_LOGIN_ATTEMPTS 3 //指定锁定用户的登录失败次数
PASSWORD_LOCK_TIME 5 //指定用户被锁定天数
PASSWORD_LIFE_TIME 30 //指定口令可用天数
数据库导入导出
-- 数据库导入1：正常情况
SQL> impdp bp_oracle/bp_oracle directory=dump_dir dumpfile=bp_oracle20120209.dmp
-- 数据库导入2：映射情况
SQL> impdp bp_oracle/bp_oracle directory=dump_dir dumpfile=ncp20120209.dmp remap_schema=ncp:bp_oracle remap_tablespace=ncp:bp_oracle
-- 数据导出，可以带版本
SQL> expdp bp_oracle/bp_oracle DIRECTORY=dump_dir dumpfile=bp_oracle.dmp version=10.2.0.1.0
xpdp 导入导出
-- 导出数据库不带版本
SQL>expdp bp_oracle/bp_oracle schemas=bp_oracle DUMPFILE=bp_oracle20120221.dmp DIRECTORY=DUMP_DIR JOB_NAME=full
-- 导出数据库 带版本
SQL> expdp bp_oracle/bp_oracle schemas=bp_oracle DIRECTORY=dump_dir dumpfile=bp_oracle20120221.dmp version=10.2.0.1.0
EXP、IMP导入导出 (常用的方式)
-- 导出数据 指定表名数据
SQL>exp nmswxt_mhwz/nmswxt_mhwz file=/home/oracle/dmp/nmswxt_mhwz_news_content.dmp tables=表1,表2,表3 //tables后面不要带括号，并且tables不要和owner一起用，会尝试冲突，owner与tables不能同时指定。owner是指定要导出指定用户的数据，tables参数指定要导出的表
-- 导入数据，带映射关系
SQL>imp nmswxt_mhzz/nmswxt_mhzz file=/home/oracle/dmp/nmswxt_mhwz_news_content.dmp fromuser=nmswxt_mhwz touser=nmswxt_mhzz
一个数据库由多个表空间构成，表空间又是由段构成，而段又是由区构成，而区又是由块构成，这样构成的可以提高数据的效率；
实际上表空间又是由多个数据文件构成，当表空间不够使用时，可以增加多个数据文件来增大表空间
增大表空间语句：alter tablespace 表空间名字 add filedata 'd:\db2.dbf' size 200m ;
表空间脱机：alter tablespace 表空间 offline;
