ITPub博客

首页 > 数据库 > Oracle > 日常工作操作SQL语句记录

日常工作操作SQL语句记录

原创 Oracle 作者:lxq1985 时间:2008-12-25 15:45:02 0 删除 编辑

查看数据库文件
select name from v$datafile;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
查看某一个用户下的相关信息
select table_name from dba_tables where owner='user';
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 创建 DZJC_UP3 表空间
CREATE SMALLFILE TABLESPACE "DZJC_UP3" DATAFILE 'D:oracleproduct10.2.0oradataJCDZJC_UP3' SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
-- 创建 DZJC_UP3_INDEX 表空间
CREATE SMALLFILE TABLESPACE "DZJC_UP3_INDEX" DATAFILE 'D:oracleproduct10.2.0oradataJCDZJC_UP3_index' SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
-- 创建 DZJC_UP3_TEMP 表空间
CREATE SMALLFILE TEMPORARY TABLESPACE "DZJC_UP3_TEMP" TEMPFILE 'D:oracleproduct10.2.0oradataJCDZJC_UP3_temp' SIZE 250M AUTOEXTEND ON NEXT 25M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 创建 DZJC_UP3 用户
CREATE USER "DZJC_UP3" PROFILE "DEFAULT" IDENTIFIED BY "12345678" DEFAULT TABLESPACE "DZJC_UP3" TEMPORARY TABLESPACE "DZJC_UP3_temp" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "DZJC_UP3" WITH ADMIN OPTION;
GRANT "DBA" TO "DZJC_UP3" WITH ADMIN OPTION;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--权限
GRANT EXECUTE ON "DZJC_UP3"."PRO_T_BUSIINDEX_GEN" TO "DZJC";
GRANT EXECUTE ON "DZJC_UP3"."PRO_T_BUSI_APPRAISE_GEN" TO "DZJC";
GRANT EXECUTE ON "DZJC_UP3"."PRO_T_BUSI_LOGIC_GEN" TO "DZJC";
GRANT EXECUTE ON "DZJC_UP3"."PRO_T_BUSI_TIMELIMIT_GEN" TO "DZJC";
GRANT EXECUTE ON "DZJC_UP3"."PRO_T_PERMISSIONITEM_GEN" TO "DZJC";
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[@more@]查看数据库文件
select name from v$datafile;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
查看某一个用户下的相关信息
select table_name from dba_tables where owner='user';
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 创建 DZJC_UP3 表空间
CREATE SMALLFILE TABLESPACE "DZJC_UP3" DATAFILE 'D:oracleproduct10.2.0oradataJCDZJC_UP3' SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
-- 创建 DZJC_UP3_INDEX 表空间
CREATE SMALLFILE TABLESPACE "DZJC_UP3_INDEX" DATAFILE 'D:oracleproduct10.2.0oradataJCDZJC_UP3_index' SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
-- 创建 DZJC_UP3_TEMP 表空间
CREATE SMALLFILE TEMPORARY TABLESPACE "DZJC_UP3_TEMP" TEMPFILE 'D:oracleproduct10.2.0oradataJCDZJC_UP3_temp' SIZE 250M AUTOEXTEND ON NEXT 25M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 创建 DZJC_UP3 用户
CREATE USER "DZJC_UP3" PROFILE "DEFAULT" IDENTIFIED BY "12345678" DEFAULT TABLESPACE "DZJC_UP3" TEMPORARY TABLESPACE "DZJC_UP3_temp" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "DZJC_UP3" WITH ADMIN OPTION;
GRANT "DBA" TO "DZJC_UP3" WITH ADMIN OPTION;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--权限
GRANT EXECUTE ON "DZJC_UP3"."PRO_T_BUSIINDEX_GEN" TO "DZJC";
GRANT EXECUTE ON "DZJC_UP3"."PRO_T_BUSI_APPRAISE_GEN" TO "DZJC";
GRANT EXECUTE ON "DZJC_UP3"."PRO_T_BUSI_LOGIC_GEN" TO "DZJC";
GRANT EXECUTE ON "DZJC_UP3"."PRO_T_BUSI_TIMELIMIT_GEN" TO "DZJC";
GRANT EXECUTE ON "DZJC_UP3"."PRO_T_PERMISSIONITEM_GEN" TO "DZJC";
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13636837/viewspace-1015268/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-12-26

  • 博文量
    29
  • 访问量
    47194