oracle常用命令

1、查看oracle版本

select * from v$version;

2、查看表空间信息

--查看数据文件
select * from dba_data_files;

--查看临时文件
select * from dba_temp_files;

3、查看表是否被锁定

select * from v$lock ,dba_objects,v$session  where  object_id=v$lock.id1  and  v$lock.sid=v$session.sid 

4、创建表空间
--创建临时表空间

create temporary tablespace lisboa_temp
tempfile '/u01/app/oracle/oradata/dev/lisboa_temp01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

--创建数据表空间

create tablespace lisboa_data
logging
datafile '/u01/app/oracle/oradata/dev/lisboa_data01.dbf'
size 256m
autoextend on
next 128m maxsize 2048m
extent management local; 

--修改表空间

alter tablespace lisboa_data add datafile '/u01/app/oracle/oradata/dev/lisboa_data01.dbf' size 128M;

alter tablespace lisboa_temp add datafile '/u01/app/oracle/oradata/dev/lisboa_temp01.dbf' size 32M;

--删除表空间

DROP TABLESPACE lisboa_temp INCLUDING CONTENTS AND DATAFILES;

5、创建用户并指定表空间

create user username identified by password
default tablespace lisboa_data
temporary tablespace lisboa_temp; 

6、给用户授权

grant connect,resource to username; 

7、分配试图权限
--包含了查询权限

GRANT CREATE ANY VIEW TO lisboa; 
GRANT DROP ANY VIEW TO lisboa;

8、在命令行执行sql文件

@D:\books\oracle\test.sql