oracle数据库常用操作

远程登陆数据库

sqlplus username/password@//IP:1521/orcl

查看当前用户下的表占用的磁盘空间

select sum(BYTES) / 1024 / 1024 as SIZE_M from user_segments

查看当前用户下的各表占用的磁盘空间

select OWNER, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) SIZE_M
	from dba_segments t
	where t.owner = 'DHBJWL'
	and t.segment_type='TABLE'
	group by OWNER, t.segment_name, t.segment_type
	order by SIZE_M desc;

删除当前用户下的所有表、视图、序列、函数、存储过程、包

--delete tables

select 'drop table ' || table_name ||';'||chr(13)||chr(10) from user_tables;

--delete views

select 'drop view ' || view_name||';'||chr(13)||chr(10) from user_views;

--delete seqs

select 'drop sequence ' || sequence_name||';'||chr(13)||chr(10) from user_sequences;

--delete functions

select 'drop function ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='FUNCTION';

--delete procedure

select 'drop procedure ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='PROCEDURE';

--delete package

select 'drop package ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='PACKAGE';

数据库数据导入导出

查询服务器端的字符集

select userenv('language') from dual;

修改导出时客户端的字符集，与服务器保持一致

export NLS_LANG='XXX'

导出：

exp username/password@IP/orcl file=./20200616.dmp owner=ZS

导入：

imp username/password@IP/orcl file=./20200616.dmp full=y ignore=y