set timing on
select * from tbname where ...create user username identified by password default tablespace spuser;
grant connect,resource to username;create view v_name as select * from tbname where ...;
select * from tbname where ...create user username identified by password default tablespace spuser;
grant connect,resource to username;create view v_name as select * from tbname where ...;
SQL>select count(*)
from empno;
count(*)
--------
14
real: 190
实际需要量190/1000=0.19秒
(根据版本来看秒数.)2.用户脚本
accept uname prompt '输入用户名:'
spool c:\gen_user
select username,'create user' || username || ' '||
decode(password,'EXTERNAL','IDENTIFIED EXTERNALLY',
'IDENTIFIED BY || password || ')lne,
'DEFAULT TABLESPACE ' || default_tablespace lne,
'TEMPORARY TABLESPACE ' || temporary_tablespace || ';' lne
from dba_users
where username like upper ('%&&uname%')
or upper('%&&uname%') is null
order by username;select username,'ALTER USER' || username || 'QUOTA' ||
decode(max_bytes,-1,'UNLIMITED',TO_CHAR(ROUND(MAX_BYTES/1024)) ||'K')
|| 'ON TABLESPACE ' || tablespace_name || ';' lne
from dba_ts_quotas
where username like upper ('%&&uname%')
or upper('%&&uname%') is null
order by username;
spool off
3.视图的脚本
set linesize 150
set pagesize 1000
set arraysize 8
set feedback off
set heading off
set long 5000
col view_name for a20
col text for a80
select 'CREATE OR REPLACE VIEW ' || VIEW_NAME || 'AS', TEXT
FROM USER_VIEWS
ORDER BY VIEW_NAME;
2.把创建用户的SQL语句(楼上)保存成*.sql文件就行,
SQLPLUS 下 执行 @filename.sql即可创建用户(默认路径是C盘根目录)
如果不保存成脚本文件,直接执行就行。
3.同上
[A]第三方工具就不说了主要说一下9i以上版本的dbms_metadata
1、获得单个对象的DDL语句
set heading off
set echo off
set feedback off
set pages off
set long 90000
select dbms_metadata.get_ddl(‘TABLE’,’TABLENAME’,’SCAME’) from dual;
如果获取整个用户的脚本,可以用如下语句
select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u;
当然,如果是索引,则需要修改相关table到index
获取视图脚本:
SEELCT DBMS_METADATA.GET_DDL('VIEW','YOURVIEWNAME') FROM DUAL;