ITPub博客

首页 > 数据库 > Oracle > 关于多用户创建相同表实现shell

关于多用户创建相同表实现shell

原创 Oracle 作者:shawnloong 时间:2015-07-17 23:53:35 0 删除 编辑
关于多用户批量创建同样表shell解决方法
开发给的脚本事例
[oracle@OLEASM1 scripts]$ cat createaalltable.sql
--create table all
create table showlist
(id int primary key,name varchar2(50),url varchar2(50));
create table userlist
(id int primary key,uid varchar2(20),user_name varchar(50));
create table sales
(id int primary key,unit_price float not null,amount float not null,amounts float);

user.list 文件我取dba_user几个用户
SQL> select * from (select username from dba_users order by created desc) m where rownum<6;

USERNAME
------------------------------
ZNJKTEST
ZNJKTEST01
ZNJK
TEST1
TEST

[oracle@OLEASM1 scripts]$ cat user.list
ZNJKTEST
ZNJKTEST01
ZNJK
TEST1
TEST


[oracle@OLEASM1 scripts]$ for user in `cat user.list`; do cat createaalltable.sql|sed 's/table /&'$user'./'; done;
--create table ZNJKTEST.all
create table ZNJKTEST.showlist
(id int primary key,name varchar2(50),url varchar2(50));
create table ZNJKTEST.userlist
(id int primary key,uid varchar2(20),user_name varchar(50));
create table ZNJKTEST.sales
(id int primary key,unit_price float not null,amount float not null,amounts float);
--create table ZNJKTEST01.all
create table ZNJKTEST01.showlist
(id int primary key,name varchar2(50),url varchar2(50));
create table ZNJKTEST01.userlist
(id int primary key,uid varchar2(20),user_name varchar(50));
create table ZNJKTEST01.sales
(id int primary key,unit_price float not null,amount float not null,amounts float);
--create table ZNJK.all
create table ZNJK.showlist
(id int primary key,name varchar2(50),url varchar2(50));
create table ZNJK.userlist
(id int primary key,uid varchar2(20),user_name varchar(50));
create table ZNJK.sales
(id int primary key,unit_price float not null,amount float not null,amounts float);
--create table TEST1.all
create table TEST1.showlist
(id int primary key,name varchar2(50),url varchar2(50));
create table TEST1.userlist
(id int primary key,uids varchar2(20),user_name varchar(50));
create table TEST1.sales
(id int primary key,unit_price float not null,amount float not null,amounts float);
--create table TEST.all
create table TEST.showlist
(id int primary key,name varchar2(50),url varchar2(50));
create table TEST.userlist
(id int primary key,uids varchar2(20),user_name varchar(50));
create table TEST.sales
(id int primary key,unit_price float not null,amount float not null,amounts float);

这样就生成了我们要的语句了可以将这些语句保存到一个sql文件
for user in `cat user.list`; do cat createaalltable.sql|sed 's/table /&'$user'./'>>createtable.sql; done;

其实还可以用awk实现

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

请登录后发表评论 登录
全部评论

注册时间:2011-08-29

  • 博文量
    111
  • 访问量
    214753