vi aoratab
#!/bin/ksh
# To view / change current Oracle SID and ORALCE_HOME, PATH
#
# Biju Thomas - 10/15/97
# Modified - 13/02/98 - Add PATH feature!
#
echo ' '
echo '********************************************************************************'
echo ' The current ORACLE_SID value is ' $ORACLE_SID
echo '********************************************************************************'
echo ' The following are valid instance names along with the ORACLE_HOME value:'
echo ' '
wcntr=0
while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
# Proceed only if third field is 'Y'.
#if [ "`echo $LINE | awk -F: '{print $3}' -`" = "Y" ] ; then
wcntr=`expr $wcntr + 1`
OSID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
echo ' *** Unknown Value ***'
fi
OHOME=`echo $LINE | awk -F: '{print $2}' -`
echo ' Slno:' $wcntr ' SID Name:' $OSID ' Oracle Home:' $OHOME
#fi
esac
done < /etc/oratab
echo ' '
echo '********************************************************************************'
echo ' '
echo ' Do you want to change ORACLE_SID [Y/N]? \c'
read choice
case $choice in
Y|y) continue;;
*) return 0;;
esac
echo ' Enter Slno of the SID from above list : \c'
read choice
if test $choice -lt 0 || test $choice -gt $wcntr
then
echo ' Invalid choice.... Sorry!!!'
return 1
fi
wcntr=0
while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
# Proceed only if third field is 'Y'.
#if [ "`echo $LINE | awk -F: '{print $3}' -`" = "Y" ] ; then
wcntr=`expr $wcntr + 1`
if test $wcntr -eq $choice
then
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
ORACLE_SID=''
fi
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
export ORACLE_HOME ORACLE_SID
break;
fi
#fi
esac
done < /etc/oratab
#
# Change PATH with new oracle home
#
path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH
export ORA_NLS=
#
echo ' '
echo '********************************************************************************'
echo ' New value of ORACLE_SID is ' $ORACLE_SID
echo ' New value of ORACLE_HOME is ' $ORACLE_HOME
echo ' New value of PATH is ' $PATH
echo '********************************************************************************'
echo ' '
#
====
[root@rac01 ~]# . aoratab
********************************************************************************
The current ORACLE_SID value is
********************************************************************************
The following are valid instance names along with the ORACLE_HOME value:
Slno: 1 SID Name: Oracle Home:
Slno: 2 SID Name: Oracle Home:
Slno: 3 SID Name: Oracle Home:
Slno: 4 SID Name: Oracle Home:
Slno: 5 SID Name: ora11g Oracle Home: /u01/app/oracle/product/11.2.0/dbhome_1
Slno: 6 SID Name: ibrac1 Oracle Home: /u01/app/oracle/product/12.2.0/dbhome_1
Slno: 7 SID Name: -MGMTDB Oracle Home: /u01/app/18.0.0/grid
Slno: 8 SID Name: LRS Oracle Home: /u01/app/oracle/product/12.2.0/dbhome_1
Slno: 9 SID Name: ugarb Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 10 SID Name: ugarbx Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 11 SID Name: ugary Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 12 SID Name: ugarx Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 13 SID Name: -MGMTDB Oracle Home: /u01/app/19.0.0/grid
Slno: 14 SID Name: ugari Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 15 SID Name: +ASM1 Oracle Home: /u01/app/19.0.0/grid
Slno: 16 SID Name: garbage Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 17 SID Name: ugaryd Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 18 SID Name: Oracle Home:
********************************************************************************
Do you want to change ORACLE_SID [Y/N]? \c
Y
-bash: continue: only meaningful in a `for', `while', or `until' loop
Enter Slno of the SID from above list : \c
5
********************************************************************************
New value of ORACLE_SID is ora11g
New value of ORACLE_HOME is /u01/app/oracle/product/11.2.0/dbhome_1
New value of PATH is /u01/app/oracle/product/11.2.0/dbhome_1/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
********************************************************************************
Saturday, 22 June 2024
Oracle new oratab --
From web
Wednesday, 5 June 2024
Oracle partition export and import using datapump
Oracle partition export and import using datapump via parfile ....
SQL> create user user1 identified by user1;
SQL> create user user2 identified by user2;
grant connect, resource to user1, user2;
2.Create datapump directory.
CREATE OR REPLACE DIRECTORY EXPDP_DIR AS '/dumps/expdp';
GRANT READ, WRITE ON DIRECTORY EXPDP_DIR to user1,user2;
3.Create partition table SALES.
alter session set current_schema=user1;
or
conn user1/user1
Create table
CREATE TABLE sales
( name varchar2(5),
time_id DATE)
PARTITION BY RANGE (time_id)
(PARTITION sales_AUG VALUES LESS THAN (TO_DATE('01-AUG-2014','dd-MON-yyyy'))
TABLESPACE users,
PARTITION sales_SEP VALUES LESS THAN (TO_DATE('01-SEP-2014','dd-MON-yyyy'))
TABLESPACE users,
PARTITION sales_OCT VALUES LESS THAN (TO_DATE('01-OCT-2014','dd-MON-yyyy'))
TABLESPACE users,
PARTITION sales_NOV VALUES LESS THAN (TO_DATE('01-NOV-2014','dd-MON-yyyy'))
TABLESPACE users,
PARTITION sales_DEV VALUES LESS THAN (TO_DATE('01-DEC-2014','dd-MON-yyyy'))
TABLESPACE users );
Table created.
insert into USER1.sales values('prod1','01-AUG-2014');
insert into USER1.sales values('prod2','01-SEP-2014');
insert into USER1.sales values('prod3','01-OCT-2014');
SELECT partition_name FROM dba_tab_partitions WHERE table_name = 'SALES' and TABLE_OWNER='USER1';
PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
SALES_AUG
SALES_DEV
SALES_NOV
SALES_OCT
SALES_SEP
col owner for a20
SELECT owner,partitioned FROM dba_tables WHERE table_name = 'SALES';
OWNER PAR
-------------------- ---
USER1 YES
SELECT partition_name FROM dba_tab_partitions WHERE table_name = 'SALES';
PARTITION_NAME
------------------------------
SALES_AUG
SALES_DEV
SALES_NOV
SALES_OCT
SALES_SEP
to create par file
set linesize 400
col OBJECT_NAME for a20
col SUBOBJECT_NAME for a20
col created for a25
col owner for a20
col list for a40
col list for a40
select 'tables='||owner||'.'|| object_name||':'||subobject_name list
--||',' ||'--'||created list
from dba_objects
where object_type='TABLE PARTITION'
and owner='USER1'
--and trunc(created) = trunc(sysdate)
order by CREATED
;
tables=USER1.SALES:SALES_DEV
tables=USER1.SALES:SALES_NOV
tables=USER1.SALES:SALES_AUG
tables=USER1.SALES:SALES_SEP
tables=USER1.SALES:SALES_OCT
userid="/ as sysdba"
directory=EXPDP_DIR
logfile=sales_table_partition.log
dumpfile=sales_table_partition.dmp
tables=USER1.SALES:SALES_DEV
tables=USER1.SALES:SALES_NOV
tables=USER1.SALES:SALES_AUG
tables=USER1.SALES:SALES_SEP
tables=USER1.SALES:SALES_OCT
expdp parfile=expdp_table.par
expdp parfile=expdp_table.par
Export: Release 12.2.0.1.0 - Production on Wed Jun 5 01:55:56 2024
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA parfile=expdp_table.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "USER1"."SALES":"SALES_NOV" 5.492 KB 1 rows
. . exported "USER1"."SALES":"SALES_OCT" 5.492 KB 1 rows
. . exported "USER1"."SALES":"SALES_SEP" 5.492 KB 1 rows
. . exported "USER1"."SALES":"SALES_AUG" 0 KB 0 rows
. . exported "USER1"."SALES":"SALES_DEV" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/dumps/expdp/sales_table_partition.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jun 5 01:58:35 2024 elapsed 0 00:02:38
for imp
cat impdp_table.par
userid="/ as sysdba"
directory=EXPDP_DIR
remap_schema=USER1:USER2
dumpfile=sales_table_partition.dmp
tables=USER1.SALES:SALES_DEV
tables=USER1.SALES:SALES_NOV
tables=USER1.SALES:SALES_AUG
tables=USER1.SALES:SALES_SEP
tables=USER1.SALES:SALES_OCT
impdp parfile=impdp_table.par
Import: Release 12.2.0.1.0 - Production on Wed Jun 5 02:06:57 2024
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": /******** AS SYSDBA parfile=impdp_table.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER2"."SALES":"SALES_NOV" 5.492 KB 1 rows
. . imported "USER2"."SALES":"SALES_OCT" 5.492 KB 1 rows
. . imported "USER2"."SALES":"SALES_SEP" 5.492 KB 1 rows
. . imported "USER2"."SALES":"SALES_AUG" 0 KB 0 rows
. . imported "USER2"."SALES":"SALES_DEV" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jun 5 02:07:07 2024 elapsed 0 00:00:08
===========================================================
to check row count !!!
define TABLE_OWNER='USER2'
define TABLE_NAME='SALES'
set serverout on size 1000000 verify off
declare
sql_stmt varchar2(1024);
row_count number;
cursor get_tab is
select table_name,partition_name from dba_tab_partitions where table_owner=upper('&TABLE_OWNER') and table_name='&TABLE_NAME'
--and rownum<20
;
begin
dbms_output.put_line('Record Counts for table_name..... ');
dbms_output.put_line('Log file to numrows_part_&TABLE_OWNER.lst ....');
dbms_output.put_line('....');
for get_tab_rec in get_tab loop
BEGIN
sql_stmt := 'select count(*) from &TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';
EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||rpad(get_tab_rec.table_name||'('||get_tab_rec.partition_name||')',50)||' '||TO_CHAR(row_count)||' rows.');
exception when others then dbms_output.put_line ('Error counting rows for table '||get_tab_rec.table_name);
END;
end loop;
end;
/
Table SALES(SALES_AUG) 0 rows.
Table SALES(SALES_DEV) 0 rows.
Table SALES(SALES_NOV) 1 rows.
Table SALES(SALES_OCT) 1 rows.
Table SALES(SALES_SEP) 1 rows.
PL/SQL procedure successfully completed.
or
define 1 ='USER1';
set serveroutput on
declare
p_count number;
begin
for i in (select * from DBA_tab_partitions where TABLE_OWNER='&1') loop
execute immediate 'select count(*) from '||i.TABLE_OWNER||'.'||i.TABLE_NAME|| ' partition('||i.partition_name||')' into p_count;
dbms_output.put_line(i.partition_name||' '||to_char(p_count));
end loop;
end ;
SALES_AUG 0
SALES_SEP 1
SALES_OCT 1
SALES_NOV 1
SALES_DEV 0
Subscribe to:
Posts (Atom)
