oracle用户表空间的创建

一、数据库表空间的查询
-- 1.查询数据库中所有表空间
select tablespace_name from sys.dba_tablespaces;


-- 2.查询所有表空间的大小
SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) "表空间大小(M)" FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME;


-- 3.查询当前表空间中表的记录数（按照记录数倒序）
select t.table_name,t.num_rows from user_tables t order by num_rows desc;

二、数据库表空间的导出

方法一：用expdp命令到处（用exp命令导出的只能用exp命令导入）
-- 1.登陆oracle后台服务器
sqlplus / as sysdba
-- 2.建立导入导出目录
create directory mydump as '/data/sharedata/mydump'；
select * from dba_directories;
-- 3.执行导出命令
expdp \'/ as sysdba\' SCHEMAS=MMSP_PORTAL directory=mydump dumpfile=mmsp_portal111.dmp logfile=mydb.log

方法二：用exp命令导出
exp mmsp_cms/111111@orcl file=mmsp_portal2222222222.dmp log=mydb.log

三、数据库表空间的导入
1、删除用户及其所有表：

drop user MMSP_CMS cascade;

drop user MMSP_BMS cascade;

drop user mmsp_aaa cascade;

drop user mmsp_portal cascade;

drop user mmsp_iscg cascade;

drop user mmsp_mc cascade;

drop user mmsp_cache cascade;


drop tablespace MMSP_CMS including contents and datafiles;

drop tablespace MMSP_BMS including contents and datafiles;

drop tablespace MMSP_AAA including contents and datafiles;

drop tablespace MMSP_PORTAL including contents and datafiles;

drop tablespace MMSP_ISCG including contents and datafiles;

drop tablespace MMSP_MC including contents and datafiles;

drop tablespace MMSP_CACHE including contents and datafiles;

drop TEMPORARY tablespace TEMP_MMSP including contents and datafiles;


2、创建表空间

create tablespace MMSP_CMS  logging datafile '/oracle/MMSP_CMS.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create tablespace MMSP_BMS  logging datafile '/oracle/MMSP_BMS.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create tablespace MMSP_AAA  logging datafile '/oracle/MMSP_AAA.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create tablespace MMSP_PORTAL  logging datafile '/oracle/MMSP_PORTAL.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create tablespace MMSP_ISCG  logging datafile '/oracle/MMSP_ISCG.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create tablespace MMSP_MC  logging datafile '/oracle/MMSP_MC.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create tablespace MMSP_CACHE  logging datafile '/oracle/MMSP_CACHE.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create TEMPORARY tablespace TEMP_MMSP  logging datafile '/oracle/TEMP_MMSP.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;


CREATE TEMPORARY TABLESPACE TEMP_MMSP TEMPFILE '/oracle/TEMP_MMSP.dbf' SIZE 200m AUTOEXTEND ON NEXT 32m MAXSIZE 2048m EXTENT MANAGEMENT LOCAL;


3、创建用户

create user mmsp_cms identified by "111111" default tablespace MMSP_CMS temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_cms;

grant unlimited tablespace to mmsp_cms;


create user mmsp_bms identified by "111111" default tablespace MMSP_BMS temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_bms;

grant unlimited tablespace to mmsp_bms;


create user mmsp_aaa identified by "111111" default tablespace MMSP_AAA temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_aaa;

grant unlimited tablespace to mmsp_aaa;


create user mmsp_portal identified by "111111" default tablespace MMSP_PORTAL temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_portal;

grant unlimited tablespace to mmsp_portal;


create user mmsp_iscg identified by "111111" default tablespace MMSP_ISCG temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_iscg;

grant unlimited tablespace to mmsp_iscg;


create user mmsp_mc identified by "111111" default tablespace MMSP_MC temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_mc;

grant unlimited tablespace to mmsp_mc;


create user mmsp_cache identified by "111111" default tablespace MMSP_CACHE temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_cache;

grant unlimited tablespace to mmsp_cache;


4、导入dmp文件

imp mmsp_cms/111111 file=mmsp_cms.dmp ignore=y full=y

imp mmsp_bms/111111 file=mmsp_bms.dmp ignore=y full=y

imp mmsp_aaa/111111 file=mmsp_aaa.dmp ignore=y full=y

imp mmsp_portal/111111 file=mmsp_portal.dmp ignore=y full=y

imp mmsp_iscg/111111 file=mmsp_iscg.dmp ignore=y full=y

imp mmsp_mc/111111 file=mmsp_mc.dmp ignore=y full=y

imp mmsp_cache/111111 file=mmsp_cache.dmp ignore=y full=y



5、清除表垃圾数据

--数据库：CMS

truncate table CMS_ASSET_LOCATION;

truncate table CMS_CAPTURE_TASK;

truncate table CMS_CATEGORY;

truncate table CMS_CATEGORY_CONTENT_REF;

truncate table CMS_CHANNEL_NODE;

truncate table CMS_CHANNEL_SERVICE_MAPPING;

truncate table CMS_CONP_CONTENT_RELA;

truncate table CMS_CONTENT;

truncate table CMS_CONTENT_INJECT_RESPONSE;

truncate table CMS_CONTENT_SPECIFICATION;

truncate table CMS_DELETE_BOCONTENT;

truncate table CMS_INTERFACE_LOG;

truncate table CMS_MOVIE_FILE;

truncate table CMS_MOVIE_LOCATION;

truncate table CMS_MOVIE_PACKAGE;

truncate table CMS_MOVIE_POSTER;

truncate table CMS_MOVIE_TITLE;

truncate table CMS_OPERATE_LOG;

truncate table CMS_ORG;

truncate table CMS_OTT_CAPTURE_LOG;

truncate table CMS_RTM_ASSET_LOCATION;

truncate table CMS_RTM_CAPTURE_ASSET;

truncate table CMS_RTM_CHANNEL;

truncate table CMS_RTM_CHANNEL_OTT;

truncate table CMS_RTM_CHANNEL_OTT_RATE;

truncate table CMS_RTM_PROGRAM;

update cms_seq_table t set t.seq_value = 1;

truncate table CMS_SIGN;

truncate table CMS_STORAGE_NODE;

truncate table CMS_TASK;

truncate table CMS_TEMP_BGPROCQUEUE;

truncate table CMS_TEMP_REQUEST;

truncate table CMS_TEMP_TRANSFER_CONTENT;

truncate table CMS_TMP;

truncate table cms_upload_log;

truncate table CMS_VODCONTENT_TO_OTT_TASK;


--数据库：ISCG

truncate table ISCG_ASSET_LOCATION;

truncate table ISCG_OPERATE_LOG;

truncate table ISCG_SEND_ERROR_QUEUE;

truncate table ISCG_STORAGE;

truncate table USG_CHANNEL;

truncate table USG_CHANNEL_OTT;

truncate table USG_CHANNEL_OTT_RATE;

truncate table USG_CONTENT;

truncate table USG_FILE_MOVIE;

truncate table USG_PROGRAM;

truncate table USG_PT;

truncate table USM_RTSP_LOG;

truncate table USM_SESSION;

truncate table USM_SESSION_HISTORY;


--数据库：BMS

truncate table T_BILL_RECORD;

truncate table T_BUSINESS;

truncate table T_BUSINESS_SERVICE_RELA;

truncate table T_CHANNEL;

truncate table T_CONP_CONTENT_RELA;

truncate table T_CONTENT;

truncate table t_customer;

truncate table T_INTERFACE_LOG;

truncate table T_LOGIN_USER;

truncate table T_MOD;

truncate table T_OPERATION_LOG;

truncate table T_PRODUCT;

truncate table T_PRODUCT_CONTENT_RELA;

truncate table T_PRODUCT_OFFER;

truncate table T_PRODUCT_OFFER_PRODUCT_RELA;

update t_seq_bms t set t.seq_value = 1;

truncate table T_SYNC_MESSAGE;

truncate table t_user;


--数据库：AAA

truncate table T_BUSINESS;

truncate table T_CHANNEL;

truncate table T_CONTENT;

truncate table T_CONTENTPACKAGE_CONTENT;

truncate table T_CONTENT_PACKAGE;

truncate table T_DEVICE;

truncate table T_LOGIN_INFO;

truncate table T_LOGIN_USER;

truncate table T_MESSAGESYNC_HIS;

truncate table T_PRODUCT;

truncate table T_PRODUCT_CONTENT;

truncate table T_PRODUCT_OFFER;

truncate table T_PRODUCT_OFFER_PROD_REF;

truncate table T_USER_DEVICE_REF;


--数据库：PORTALMS

truncate table imp_column;

truncate table T_CATEGORY_CACHES_TMP;

truncate table T_ASSET_TRANSITION;

truncate table T_BOOKMARK;

truncate table T_BUSINESS_INFO;

truncate table T_CATEGORY;

truncate table T_CATEGORYINFO;

truncate table T_CATEGORY_BACKUP;

truncate table T_CHANNEL;

truncate table T_CHANNEL_EPG;

truncate table T_CHANNEL_EPG_AFTER;

truncate table T_CONTENT;

truncate table T_CONTENTPACKAGE;

truncate table T_FOREST;

truncate table T_GRADE;

truncate table t_key_words;

truncate table T_MOVIE_STOPPOINT;

truncate table T_NPVR;

truncate table t_play_count;

truncate table T_PRODUCT;

truncate table T_PRODUCT_OFFERING;

truncate table T_PRODUCT_OFFER_PROD_REF;

truncate table T_RECOMMEND;

truncate table t_recommend_category_rel;

truncate table T_RECOMMEND_PAGE;

truncate table T_RELA_BUSINESS_SERVICE;

truncate table T_RELA_CONPACK_CONTENT;

truncate table T_RELA_PRODUCT_CONTENT;

truncate table T_SEARCH_RECORD;

update t_seq_category t set t.seq_value = 1;

update t_seq_portal t set t.seq_value = 1;

truncate table T_SYSTEM_MESSAGE;

truncate table T_USER_GRADE;

truncate table T_USER_PLAY;

truncate table t_operate_log;