日常工作操作SQL语句记录

查看数据库文件
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13636837/viewspace-1015268/