oracle常用命令

--启动数据库
lsnrctl stop;
lsnrctl start;
sqlplus sys/manager as sysdba
shutdown immediate
startup
--查询表空间
select * from sys.dba_tablespaces;

--查看表空间占用情况
select a.tablespace_name,a.bytes bytes_used,b.largest,round(((a.bytes - b.bytes)/a.bytes)*100,2) percent_used
from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by ((a.bytes - b.bytes) / a.bytes) desc
--查看表空间占用情况2
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc

--查看表空间的数据文件是否是自动扩展
select file_name,tablespace_name,autoextensible from dba_data_files

--查看列名
select * from dba_tab_columns where owner = 'OSS'
--创建临时表空间
create temporary tablespace oss_temp
tempfile '/home/oracle/oradata/oss/oss_temp.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

//创建数据表空间

create tablespace OSS
logging
datafile '/home/oracle/oradata/oss/oss.dbf'
size 100m
autoextend on
next 32m maxsize 2048m
extent management local;

--datafile参数必须有

--增加表空间
alter tablespace OSS
add
datafile '/home/oracle/oradata/oss/oss1.dbf'
size 100m
autoextend on
next 32m maxsize 4048m
extent management local;

--删除表空间
drop tablespace oss_temp including contents and datafiles cascade onstraints;

如果删除表空间之前删除了表空间文件，解决办法:

如果在清除表空间之前，先删除了表空间对应的数据文件，会造成数据库无法正常启动和关闭。
可使用如下方法恢复（此方法已经在oracle9i中验证通过）：
下面的过程中，filename是已经被删除的数据文件，如果有多个，则需要多次执行；tablespace_name是相应的表空间的名称。
$ sqlplus /nolog
SQL> conn / as sysdba;
如果数据库已经启动，则需要先执行下面这行：
SQL> shutdown abort
SQL> startup mount
SQL> alter database datafile 'filename' offline drop;
SQL> alter database open;
SQL> drop tablespace tablespace_name including contents;


--查看当前用户每个表占用空间的大小：
Select Segment_Name,Sum(bytes)/1024/1024 disksize From User_Extents Group By Segment_Name order by disksize desc
--按用户统计表占用情况
SELECT t.owner, trunc(SUM(db_size),3) db_size_M FROM(
select owner,table_name,
NUM_ROWS,
BLOCKS*(select to_number(p.VALUE) from v$parameter p where p.NAME = 'db_block_size')/1024/1024 db_size,
EMPTY_BLOCKS,
LAST_ANALYZED
from dba_tables t
order by BLOCKS desc
)t
group by t.owner
order by db_size_M desc;

-- 统计表占用
select owner,
table_name,
NUM_ROWS,
BLOCKS * (select to_number(p.VALUE) from v$parameter p where p.NAME = 'db_block_size') / 1024 / 1024 db_size,
EMPTY_BLOCKS,
LAST_ANALYZED
from dba_tables t
where owner = 'OSS'
order by t.owner, T.BLOCKS desc

--查看用户
select * from dba_users;

--将system用户改为manager
alter user system identified by values 'D4DF7931AB130E37';
alter user system identified by manager;

--查看用户或角色系统权限(直接赋值给用户或角色的系统权限
select * from dba_sys_privs

select * from role_sys_privs;

--查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS

--删除用户
select username,'alter system kill session '''||sid||','||serial#|| ''';' from v$session v
where v.USERNAME = 'OSS'
drop user OSS cascade;

--查询当前数据库名
select name from v$database;
show parameter db
--查询当前数据库实例名
select instance_name from v$instance;

select value from v$parameter where name = 'service_name';

--创建用户并指定表空间
create user oss1 identified by oss
default tablespace oss temporary tablespace oss_temp;

--授予oss1用户DBA角色的所有权限
GRANT DBA TO oss1;
grant connect,resource to oss1;


导入导出命令：

Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件， imp命令可以把dmp文件从本地导入到远处的数据库服务器中。 利用这个功能可以构建两个相同的数据库，一个用来测试，一个用来正式使用。

执行环境：可以在SQLPLUS.EXE或者DOS（命令行）中执行，
DOS中可以执行时由于 在oracle 8i 中 安装目录ora81BIN被设置为全局路径，
该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。
oracle用java编写，SQLPLUS.EXE、EXP.EXE、IMP.EXE这两个文件有可能是被包装后的类文件。
SQLPLUS.EXE调用EXP.EXE、IMP.EXE所包裹的类，完成导入导出功能。

下面介绍的是导入导出的实例。
数据导出：
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:daochu.dmp中
exp system/manager@TEST file=d:daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:daochu.dmp owner=(system,sys)
3 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:datanewsmgnt.dmp tables=(inner_notify,notify_staff_relat)

4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:daochu.dmp tables=(table1) query=" where filed1 like '00%'"

上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面 加上 compress=y 来实现。

数据的导入
1 将D:daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:daochu.dmp
imp aichannel/aichannel@HUST full=y file=d:datanewsmgnt.dmp ignore=y
上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
在后面加上 ignore=y 就可以了。
2 将d:daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:daochu.dmp tables=(table1)

基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入。

注意：
操作者要有足够的权限，权限不够它会提示。
数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。

附录一：
给用户增加导入数据权限的操作
第一,启动sql*puls
第二，以system/manager登陆
第三，create user 用户名 IDENTIFIED BY 密码 （如果已经创建过用户，这步可以省略）
第四，GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO 用户名字
第五, 运行-cmd-进入dmp文件所在的目录,
imp userid=system/manager full=y file=*.dmp
或者 imp userid=system/manager full=y file=filename.dmp

执行示例:
F:WorkOracle_Databackup>imp userid=test/test full=y file=inner_notify.dmp

屏幕显示
Import: Release 8.1.7.0.0 - Production on 星期四 2月 16 16:50:05 2006
(c) Copyright 2000 Oracle Corporation. All rights reserved.

连接到: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

经由常规路径导出由EXPORT:V08.01.07创建的文件
已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入
导出服务器使用UTF8 NCHAR 字符集 (可能的ncharset转换)
. 正在将AICHANNEL的对象导入到 AICHANNEL
. . 正在导入表 "INNER_NOTIFY" 4行被导入
准备启用约束条件...
成功终止导入，但出现警告。


附录二：
Oracle 不允许直接改变表的拥有者, 利用Export/Import可以达到这一目的.
先建立import9.par,
然后，使用时命令如下：imp parfile=/filepath/import9.par
例 import9.par 内容如下：
FROMUSER=TGPMS
TOUSER=TGPMS2 （注：把表的拥有者由FROMUSER改为TOUSER，FROMUSER和TOUSER的用户可以不同）
ROWS=Y
INDEXES=Y
GRANTS=Y
CONSTRAINTS=Y
BUFFER=409600
file==/backup/ctgpc_20030623.dmp
log==/backup/import_20030623.log

--创建目录
create directory dump_oracle as '/home/oracle/dump_oracle';
/home/oracle
--授权
grant read, write on directory dump_oracle to oss;
grant all on directory dump_oracle to public

--导出表数据
expdp oss/oss DIRECTORY=dump_oracle dumpfile=content.dmp tables=content;

--查询目录
select * from dba_directories;

--服务端job
select * from dba_datapump_jobs

--delete job
DROP TABLE OSS.SYS_EXPORT_TABLE_02;
PURGE TABLE OSS.SYS_EXPORT_TABLE_02;

expdp oss/oss DIRECTORY=dump_oracle dumpfile=user_object.dmp tables=USER_OBJECT_PROGRAM

expdp oss/oss DIRECTORY=dump_oracle dumpfile=user_object.dmp tables=USER_OBJECT_PROGRAM PARALLEL=8

impdp oss1/oss DIRECTORY=dump_oracle dumpfile=user_object.dmp
tables=USER_OBJECT_PROGRAM parallel=4 REMAP_SCHEMA=OSS:OSS1
expdp oss/oss schemas=oss dumpfile=oss_0214.dmp DIRECTORY=dump_oracle parallel=16;
[url]http://tieba.baidu.com/f?kz=604525817[/url]

--查询外键关联的表
select * from user_cons_columns cl where cl.constraint_name like 'FK72A%';

添加主键索引
--alter table content add constraint pk_contId primary key(contid)
--alter table content DISABLE constraint pk_contId
--alter table content enable constraint pk_contId
--alter table content drop constraint pk_contId
--create index idx_object on content(object_id)


1.查看回收站

select * from user_recyclebin;

2.清空回收站

purge recyclebin;

3.清空回收站中的某个表

--如下方式删除会提示：SQL命令未正确结束。

purge table BIN$/UpBuh+LQ9yZGN95BFsk5Q==$0

--正确写法如下：

purge table "BIN$/UpBuh+LQ9yZGN95BFsk5Q==$0";

--如下方式删除会提示：SQL命令未正确结束。

drop table BIN$/UpBuh+LQ9yZGN95BFsk5Q==$0

--而如下这样写，则会提示：无法对回收站中的对象执行DDL/DML。

drop table "BIN$0iJ7/rWFQrSGdZexvGv3qQ==$0"

4.恢复回收站

FLASHBACK TABLE "BIN$0iJ7/rWFQrSGdZexvGv3qQ==$0" TO BEFORE DROP

这是10g 的新特性
在10g中，如果启用flash drop功能，在drop表时，数据库不会直接删除，而是将其放在回收站中，当空间出现短缺时，才会逐渐回收这部分空间。
bin$表示表放在了回收站，你想要的话还可以找回来
删除的话一个方法是直接删delete tanle bin$.....;
另一种方法就是使用 purge table table_name;

drop table时， 不产生他们，修改你的drop语句
写成 ：

DROP TABLE TABLE_NAME PURGE ;

这个时候再用SELECT语句查询此表时，将会提示表或视图不存在。但可以用如下语句查询到这个表还在Oracle回收站中：

SELECT * FROM user_recyclebin WHERE original_name=‘drop_test’;

那么现在就可以用如下语句进行恢复：

FLASHBACK TABLE drop_test TO BEFORE DROP

但是，要注意的是，如果用toad工具进行鼠标操作，即右键drop table时，如果选择了purge选项，那么就是永久性删除，在oracle的回收站也不会存在了，所以在进行表的删除的时候一般不要选择该选项，除非你很肯定该表不再使用。