oracle导出用户的所有表结构,perl 导出用户oracle的表结构

最新推荐文章于 2024-05-08 12:36:38 发布

weixin_39779032

最新推荐文章于 2024-05-08 12:36:38 发布

阅读量169

点赞数

文章标签： oracle导出用户的所有表结构

#!/usr/bin/perl

# dbusers - manage MySQL user table

use ExtUtils::testlib;

use Getopt::Long;

use Config;

use Getopt::Std;

use Oraperl;

format HEADER =

hbjxzl.cublog.cn auxiliary Data Utility Version 1.0.0

This script will auxiliary data and struct.

-h - Give help screen

-C clear - truncate owner's all tables

-E export - export owner's data or struct

-D drop - for drop all data or struct

Examples:

auxiliary.pl -C/c

auxiliary.pl -E/e

auxiliary.pl -D/d

sub do_help {

# Give help screen and exit

$~ = "HEADER";

write;

exit();

}

sub replace_null_to_{

$str=shift;

if($str){

$str =~ s/ /_/g;

}

return $str;

}

sub replace_enter_to_{

$str=shift;

if($str){

$str =~ s/\n//g;

}

return $str;

}

sub write_file{

$put_name=shift;

$put_trunc_name=shift;

$put_drop_name=shift;

open(OUTPUT, "> $put_name ")

or die "Couldn't open $put_name for write: $!\n";

if ($put_trunc_name){

open(TRUNCATE_OUTPUT, "> $put_trunc_name ")

or die "Couldn't open $put_trunc_name for write: $!\n";

}

if ($put_drop_name){

open(DROP_OUTPUT, "> $put_drop_name ")

or die "Couldn't open $put_drop_name for write: $!\n";

}

sub close_write_file{

close(OUTPUT);

close(TRUNCATE_OUTPUT);

close(DROP_OUTPUT);

}

sub read_file{

$get_name=shift;

open(INPUT, "< $get_name ")

or die "Couldn't open $put_name for read: $!\n";

}

sub close_read_file{

close(INPUT);

}

sub connect_db{

my $dbname=shift;

my $dbuser=shift;

my $dbpasswd=shift;

#print " test dbname is ",$dbname," user is ", $dbuser," passwd is ", $dbpasswd,"\n";

$dbh = DBI->connect("dbi:Oracle:$dbname", $dbuser,$dbpasswd,{ RaiseError=>1 });

return $dbh;

}

sub dis_connect_db{

$dbh->disconnect;

}

sub print_array{

my @cfg_lines=split(" ",$_);

foreach (@cfg_lines){

print $_,"\t";

}

print "\n";

return @cfg_lines;

}

sub obj_sql{

$sql_code=shift;

if($sql_code ==1){

$get_sql="select dbms_metadata.get_ddl('TABLE',TABLE_NAME,OWNER) from dba_tables where owner = ? and table_name = ? "; #for all table's

}elsif($sql_code==2){

$get_sql="select dbms_metadata.get_ddl('INDEX',INDEX_NAME,OWNER) from dba_indexes where owner = ? and index_name = ? "; #for all index's

}elsif($sql_code==3){

$get_sql="select dbms_metadata.get_ddl('CONSTRAINT',CONSTRAINT_NAME,OWNER) from dba_constraints where owner = ? and constraint_name = ? "; #for all constraint's

}elsif($sql_code==4){

$get_sql="select dbms_metadata.get_ddl('REF_CONSTRAINT',CONSTRAINT_NAME,OWNER) from dba_constraints where owner = ? and constraint_name = ? "; #for all foreign constraint's

}elsif($sql_code==5){

$get_sql="select distinct owner,index_name from dba_indexes di where owner like ? and table_name like ? and (owner,index_name) not in (select owner,constraint_name from dba_constraints dc where dc.owner=di.owner and dc.constraint_name=di.index_name) "; #for get table's indexes

}elsif($sql_code==6){#for get table's constraints

$get_sql="select distinct owner,constraint_name,constraint_type from dba_constraints where owner like ? and table_name like ? and constraint_name not like 'SYS_%'";

}elsif($sql_code==7){#for get all function

$get_sql="select dbms_metadata.get_ddl('FUNCTION',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'FUNCTION'";

}elsif($sql_code==8){#for gete all procedure

$get_sql="select dbms_metadata.get_ddl('PROCEDURE',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'PROCEDURE'";

}elsif($sql_code==9){#for get all package head

$get_sql="select dbms_metadata.get_ddl('PACKAGE_SPEC',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'PACKAGE'";

}elsif($sql_code==10){#for get all package body

$get_sql="select dbms_metadata.get_ddl('PACKAGE_BODY',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'PACKAGE BODY'";

}elsif($sql_code==11){#for get all triggers

$get_sql="select dbms_metadata.get_ddl('TRIGGER',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'TRIGGER'";

}elsif($sql_code==12){#for get all synonym

$get_sql="select dbms_metadata.get_ddl('SYNONYM',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'SYNONYM'";

}elsif($sql_code==13){#for get all sequence

$get_sql="select dbms_metadata.get_ddl('SEQUENCE',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'SEQUENCE'";

}elsif($sql_code==14){#for get all sequence

$get_sql="select dbms_metadata.get_ddl('TYPE_SPEC',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'TYPE'";

}elsif($sql_code==15){#for get all sequence

$get_sql="select dbms_metadata.get_ddl('TYPE_BODY',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'TYPE BODY'";

}elsif($sql_code==16){#for get all sequence

$get_sql="select dbms_metadata.get_ddl('VIEW',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'VIEW'";

}elsif($sql_code==17){#for get all sequence

$get_sql="select 'drop table '||owner||'.'||table_name as dro_tab from dba_tables where owner like ? and table_name like ? ";

}elsif($sql_code==19){#for get all tables

$get_sql="select owner,table_name from dba_tables where owner = ? and cluster_name is null";

}elsif($sql_code==20){#for get all object expried table

$get_sql="select owner,object_name,object_type from dba_objects where owner = ? and object_type in ('SEQUENCE','SYNONYM','FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY','TRIGGER','TYPE','TYPE BODY','VIEW')";

}else{

$get_sql="Err!";

}

return $get_sql;

}

sub obj_type{

my $dbh = shift;

my $obj_owner= shift;

my $obj_name = shift;

my $obj_type= shift;

my $get_obj_sql=&obj_sql($obj_type);

#print "test ",$get_obj_sql,"\n\t{dt is $obj_owner\.$obj_name\[$obj_type\]}\n";

my $sth = $dbh->prepare($get_obj_sql);

$sth->execute($obj_owner,$obj_name);

my @row = $sth->fetchrow;

return $row[0];

}

sub tab_ind_sql{

my $dbh = shift;

my $obj_owner= shift;

my $obj_name = shift;

my $data_text;

my $get_obj_sql=&obj_sql(5);

my $sth = $dbh->prepare($get_obj_sql);

$sth->execute($obj_owner,$obj_name);

while (@row= $sth->fetchrow){

$ind_sql=&obj_type($dbh,$row[0],$row[1],2);

$data_text= join("\n",$data_text,$ind_sql,"/") ;

}

$get_obj_sql="";

@row="";

my $get_obj_sql=&obj_sql(6);

my $sth = $dbh->prepare($get_obj_sql);

$sth->execute($obj_owner,$obj_name);

while (@row= $sth->fetchrow){

if($row[2] =~ m/R/){

$ind_sql=&obj_type($dbh,$row[0],$row[1],4);

}else{

$ind_sql=&obj_type($dbh,$row[0],$row[1],3);

}

$data_text= join("\n",$data_text,$ind_sql,"/") ;

}

return $data_text;

}

sub tab_struct{ # also used by test_leak()

my $dbh = shift;

my $tab_owner= shift;

my $tab_name = shift;

my $tmp_text;

$tmp_tab_text=&obj_type($dbh,$tab_owner,$tab_name,1);

$tmp_ind_text=&tab_ind_sql($dbh,$tab_owner,$tab_name);

$tmp_text= join "","--","="x30,$tab_owner,".",$tab_name,"="x30;

$tmp_text = join ("\n",$tmp_text,$tmp_tab_text,"/\n");

$tmp_text = join ("\n",$tmp_text,$tmp_ind_text,"\n");

return $tmp_text;

}

sub get_all_tabs_str{

my $dbh = shift;

my $exp_owner= shift;

my $get_obj_sql=&obj_sql(19);

my $sth = $dbh->prepare($get_obj_sql);

#print $get_obj_sql,"\t",$exp_owner,"\n";

$sth->execute($exp_owner);

while (@row= $sth->fetchrow){

$tab_owner=$row[0];

$tab_name=$row[1];

my $tab_str_data=&tab_struct($dbh,$tab_owner,$tab_name);

print OUTPUT $tab_str_data;

my $trunc_tab="TRUNCATE TABLE ";

$trunc_tab=join(" ",$trunc_tab,$tab_owner);

$trunc_tab=join(".",$trunc_tab,$tab_name);

print TRUNCATE_OUTPUT $trunc_tab,"\n";

#print $trunc_tab,$tab_owner,$tab_name;

my $drop_tab="DROP TABLE ";

$drop_tab=join(" ",$drop_tab,$tab_owner);

$drop_tab=join(".",$drop_tab,$tab_name);

print DROP_OUTPUT $drop_tab,"\n";

#print "test ",$drop_tab,"\n";;

}

sub get_all_obj_str{

my $dbh = shift;

my $exp_owner= shift;

my $p_orders = shift;

my $get_obj_sql=&obj_sql(20);

my $sth = $dbh->prepare($get_obj_sql);

#print $get_obj_sql,"\t",$exp_owner,"\n";

$sth->execute($exp_owner);

while (@row= $sth->fetchrow){

my $tmp_text= join "","--","="x30,$row[0],".",$row[1],"{",$row[2],"}","="x30;

$obj_str_data="";

#print @row[0],"\t",@row[1],"\t",$row[2],"\n";

if($row[2] =~ m/FUNCTION/){

$obj_str_data=&obj_type($dbh,$row[0],$row[1],7);