Transportable tablespace and Cross-Platform Tablespace Conversion

From Oracle 10g Win64 to  Oracle 12c AIX64 

create user test identified by test;
grant connect,resource to test;
create tablespace test datafile 'G:\ORADATA\NETBANK\test01.dbf' size 128M ;
conn test/test

CREATE TABLE test_tab (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
)
tablespace test;
INSERT /*+ APPEND */ INTO test_tab (id, description)
SELECT level,
       'Description for ' || level
FROM   dual
CONNECT BY level <= 10000;
COMMIT;
================================================================================================
EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST', incl_constraints => TRUE);
SELECT * FROM transport_set_violations;

set NLS_LANG=AMERICAN_AMERICA.UTF8
set ORACLE_SID=netdb
expdp "'/ as sysdba '"   directory=BACK_DIR dumpfile=test.dmp schemas=test CONTENT=metadata_only  EXCLUDE=STATISTICS

set ORACLE_SID=netdb
sql "alter tablespace test read only";
rman target /
CONVERT TABLESPACE "TEST"  TO PLATFORM 'AIX-Based Systems (64-bit)'   FORMAT='C:\AIX_DATAFILES\test01.dbf';
expdp "'/ as sysdba '" directory=BACK_DIR transport_tablespaces=test dumpfile=TEST_METADATA.dmp


sql "alter tablespace test read write";

DEST SERVER  ( AIX)
===========
create directory BACK_DIR as '/db12c/exports';
export NLS_LANG=AMERICAN_AMERICA.UTF8

create user test identified by test;grant connect,resource to test;

export ORACLE_SID=netdb
impdp "'/ as sysdba '"  directory=BACK_DIR dumpfile=TEST.DMP schemas=test
 mv TEST01.DBF /db12c/databases/H/.
impdp "'/ as sysdba '"  directory=BACK_DIR dumpfile=TEST_METADATA.DMP transport_datafiles='/db12c/databases/H/TEST01.DBF';

ALTER TABLESPACE test READ WRITE;

SELECT tablespace_name, plugged_in, status
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST';

Introduction to git

cd ..
git init
git add .
git commit
git remote add origin http://theseas/FileMover.git
git clone http://theseas/FileMover.git
git pull http://theseas/FileMover.git

 

Sys_guid()

The sys_guid() can used to genated unique varchar2 columns

Select sys_guid() from dual

Also, you can use it as default value in a table

Using sqlldr you can use  as 

Column expression "sys_guid()",

fibonacci in PL/SQL

create or replace  package  as

    procedure print_fibonacci ( n number);

end fibonacci;

create or replace package body fibonacci as

  function calculate_fibonacci ( n number) return number

  as

   begin

      if (n <= 1) then

      return n;

      else

          return calculate_fibonacci(n-1) + calculate_fibonacci(n-2);

      end if;

   end calculate_fibonacci;

 

  procedure print_fibonacci ( n number) as

   counter pls_integer := 1;

  begin

      loop

          dbms_output.put_line(counter-1||':'||calculate_fibonacci(counter-1));

          exit when counter > n;

          counter := counter +1;

      end loop;

  end print_fibonacci;

end fibonacci;

Create Oracle 11g Database Manually on AIX5L

db_name='GATEWAY'

memory_target=1G

processes = 150

audit_file_dest='/oradata/db11g/audit'

audit_trail ='db'

db_block_size=8192

db_domain='nbg.com.cy'

db_recovery_file_dest='/oradata/db11g/flash_recovery_area'

db_recovery_file_dest_size=100G

diagnostic_dest='/oradata/db11g/diag'

dispatchers='(PROTOCOL=TCP) (SERVICE=GATEWAYXDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

control_files = ('/oradata/db11g/databases/GATEWAY/control1.ctl','/oradata/db11g/databases/GATEWAY/control1.ctl')

compatible ='11.2.0'

aq_tm_processes=1

sessions=400

db_file_multiblock_read_count=16

----------------------------------------------------------------------------------------------------------------------------

orapwd FILE=/oradata/db11g/product/11.2.0/dbhome_1/dbs/orapwGATEWAY ENTRIES=10

----------------------------------------------------------------------------------------------------------------------------

export ORACLE_SID=GATEWAY

----------------------------------------------------------------------------------------------------------------------------

CREATE DATABASE GATEWAY

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 ('/oradata/db11g/databases/GATEWAY/redo01.log') SIZE 50M,

GROUP 2 ('/oradata/db11g/databases/GATEWAY/redo02.log') SIZE 50M,

GROUP 3 ('/oradata/db11g/databases/GATEWAY/redo03.log') SIZE 50M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

CHARACTER SET EL8ISO8859P7

NATIONAL CHARACTER SET UTF8

EXTENT MANAGEMENT LOCAL

DATAFILE '/oradata/db11g/databases/GATEWAY/system01.dbf' SIZE 325M REUSE

SYSAUX DATAFILE '/oradata/db11g/databases/GATEWAY/sysaux01.dbf' SIZE 325M REUSE

DEFAULT TABLESPACE users

DATAFILE '/oradata/db11g/databases/GATEWAY/users01.dbf'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/oradata/db11g/databases/GATEWAY/temp01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE 4096M

UNDO TABLESPACE UNDOTBS1

DATAFILE '/oradata/db11g/databases/GATEWAY/undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE 4096M;

----------------------------------------------------------------------------------------------------------------------------

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?/sqlplus/admin/pupbld.sql

-----------------------------------------------------------------------------------------------------------------------------

CREATE TABLESPACE CHASER_TBS LOGGING

DATAFILE '/oradata/db11g/databases/GATEWAY/chaser_tbs01.dbf'

SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE 4096M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

-----------------------------------------------------------------------------------------------------------------------------

Using DBMS_PROFILER Oracle 11g

Using DBMS_PROFILER

exec DBMS_PROFILER.start_profiler;

ERROR at line 1:

ORA-06528: Error executing PL/SQL profiler

ORA-06512: at "SYS.DBMS_PROFILER", line 123

ORA-06512: at "SYS.DBMS_PROFILER", line 132

ORA-06512: at line 1

@?\rdbms\admin\proftab.sql

create or replace function tablecounter

return number

as

   c pls_integer;

begin

  select count(*) into c from lineitem;

  return c;

end tablecounter;

exec DBMS_PROFILER.start_profiler('tablecounter');

variable a number

exec :a:= tablecounter;

PL/SQL procedure successfully completed.

exec DBMS_PROFILER.stop_profiler;

BREAK ON  runid ON run_owner ON  run_comment ON run_secs

SELECT a.runid, a.run_owner, a.run_comment,

       a.run_total_time / 1000000000 run_secs, c.total_occur,

       c.total_time / 1000000000 line_total_secs, c.line#, u.text

  FROM plsql_profiler_runs a,

       plsql_profiler_units b,

       plsql_profiler_data c,

       user_source u

 WHERE a.runid = b.runid

   AND a.runid = c.runid

   AND b.unit_name = u.NAME

   AND c.line# = u.line

/

RUNID	RUN_OWNER	RUN_COMMENT	RUN_SECS	TOTAL_OCCUR	LINE_TOTAL_SECS	LINE#	TEXT
1	TPCH2	tablecounter	99.864557	2	0.000073	1	function tablecounter
1	TPCH2	tablecounter	99.864557	0	0.000007	1	function tablecounter
1	TPCH2	tablecounter	99.864557	2	0.000059	1	function tablecounter
1	TPCH2	tablecounter	99.864557	1	0.000002	1	function tablecounter
1	TPCH2	tablecounter	99.864557	1	0.309489	6	select count(*) into c from lineitem;
1	TPCH2	tablecounter	99.864557	1	0.000001	7	return c;
1	TPCH2	tablecounter	99.864557	1	0.000006	8	end tablecounter;

Handling the error ORA-01438

Handling the error ORA-01438: value larger than specified precision allowed for this column.



This error happens when inserting or updating records into Oracle tables. The numeric value that user / process tries to place is exceeding the precision defined for the column.



For example:



Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

Connected as someschema



SQL> create table SOMETABLE (a number(5,2), b number(3,2), c number(5));



Table created



SQL> insert into SOMETABLE values (2.0, 53.34, 123456);



insert into SOMETABLE values (2.0, 53.34, 123456)



ORA-01438: value larger than specified precision allowed for this column



SQL>



When a user runs some stand alone PL/SQL queries it’s not difficult to identify the value that causes the error.



The issue seems more complex when the error comes from a process that inserts and/or updates a few hundreds / thousands of records in a table. How to know the exact record and/or value that leads to the problem?



The first approach is to create a copy of the original table, but with increased precision(s) for numeric columns. Trying to insert / update values from the “improved” changed table to the original one, combining the records could point to the problematic record(s) and/or value(s).



For example:



SQL> create table SOMETABLEPLUS as select * from SOMETABLE where 1 = 2;



Table created



SQL> alter table SOMETABLEPLUS modify b number(4, 2);



Table altered



SQL> alter table SOMETABLEPLUS modify c number(6);



Table altered



SQL> insert into SOMETABLEPLUS values (2.0, 53.34, 123456);



1 row inserted



SQL> commit;



Commit complete



This way is easy enough, transparent and useful sometimes. It can help to find out the reason for the ORA-01438 error. From the other hand, it’s a sort of some manual activity.



The second approach that usually comes to mind is to debug the application side, to place some prints, to check some values that come from application to understand the behavior leading to the error. The downside of this method is that you have to access and manipulate the application code. I think you’ll agree with me, it’s a sort of manual activity too. After all, we’re hi-tech .



And the Oracle has something more powerful to suggest.



The idea is to get trace of insert(s)/update(s) and to catch the error precisely. It’s possible to turn on the trace on the level of session or on the level of system.



Assuming we have a process with no any access inside. It runs kind of isolated thread that we can’t “incapsulate” or “inject” something internally. In addition, the thread runs so quickly that we can’t even catch its session number in Oracle. All we have – it fails with the Oracle error ORA-01438.



In such case DBA can assist to find the problematic record(s).

Here are the steps to do. (I’ll illustrate it based on Oracle 11g release 1. You can do the same with other Oracle versions).



1. Connect to the Oracle instance when the problem happens as sysdba.



2. Turn on the following trace.



ALTER SYSTEM SET EVENTS=’1438 TRACE NAME ERRORSTACK FOREVER, LEVEL 12?;



It should be done for a short period of time, when reconstructing the problem, because invoked trace files will occupy more and more disk space while this event trace is turned on.



3. Re-run the process that causes the Oracle error ORA-01438.



4. Be sure to turn the activated trace off. Failure to perform this step will cause Oracle server file system to be overflowed with huge trace files.



ALTER SYSTEM SET EVENTS=’1438 TRACE NAME ERRORSTACK OFF’;



5. Find out where the trace files for the Oracle server are placed on:



SQL> show parameter user_dump_dest



NAME TYPE VALUE

———————————— ———– ——————————

user_dump_dest string /u01/app/oracle/diag/rdbms/dev

11g/DEV11G/trace

SQL>



6. Get to the dump destination directory and search the specific trace that has the error logged:



[oracle@localhost trace]$ cd /u01/app/oracle/diag/rdbms/dev11g/DEV11G/trace/



[oracle@localhost trace]$ grep -l 1438 *

alert_DEV11G.log

DEV11G_ora_8771.trc

[oracle@localhost trace]$



7. Open the trace file(s) to get the brief information about the error.



Here we can find the exact timestamp the problem happens, exact session, service name, module name, action name that caused the error. More important you can see the exact SQL statement that caused the problem and its exact value(s). In addition, for more advanced Oracle users the trace contains call stack trace.



…

*** 2010-10-04 09:19:35.131

*** SESSION ID:(2077.13457) 2010-10-04 09:19:35.132

*** CLIENT ID:() 2010-10-04 09:19:35.132

*** SERVICE NAME:(DEV11G) 2010-10-04 09:19:35.132

*** MODULE NAME:(PL/SQL Developer) 2010-10-04 09:19:35.132

*** ACTION NAME:(SQL Window – New) 2010-10-04 09:19:35.132



—– Error Stack Dump —–

ORA-01438: value larger than specified precision allowed for this column

—– Current SQL Statement for this session (sql_id=6w3w8fyhg39a4) —–

insert into SOMETABLEPLUS values (2.0, 153.34, 12345)



—– Call Stack Trace —–

calling call entry argument values in hex

location type point (? means dubious value)

——————– ——– ——————– —————————-

skdstdst()+41 call kgdsdst() 000000000 ? 000000001 ?

7FFF488E3488 ? 7FFF488E1F70 ?

…



REFERENCE : http://dba-star.blogspot.com/2010/12/handling-error-ora-01438.html

Session Trace and Bind Variables

AS SYS


----------

execute dbms_system.set_sql_trace_in_session(144,951, true);



SQL_ID

-------------

SQL_TEXT

--------------------------------------------------------------------------------

4nsz84fyk5wf5

select ANNOUNCEMENTS_TITLE from ann where ANNOUNCEMENTS_ID= :ann_id







SQL> SELECT

2 a.sql_text,

3 b.name,

4 b.position,

5 b.datatype_string,

6 b.value_string

7 FROM

8 v$sql_bind_capture b,

9 v$sqlarea a

10 WHERE

11 b.sql_id = '4nsz84fyk5wf5'

12 AND

13 b.sql_id = a.sql_id;



SQL_TEXT

---------------------------------------------------------------------------

NAME POSITION DATATYPE_STRING

------------------------------ ---------- ---------------

VALUE_STRING

---------------------------------------------------------------------------

select ANNOUNCEMENTS_TITLE from ann where ANNOUNCEMENTS_ID= :ann_id

:ANN_ID 1 NUMBER

39





AS USER

---------

variable ann_id number

exec :ann_id := 39

select ANNOUNCEMENTS_TITLE from ann where ANNOUNCEMENTS_ID= :ann_id;

Create Explain Plan Automatically

create or replace
function createplan
(
  sql_text in varchar2
)
return varchar2
as
     plan_stmt_id_ varchar2(4000);
begin
  select to_char(plan_stmt_id.nextval) into plan_stmt_id_ from dual;
  execute immediate  ( 'EXPLAIN PLAN SET STATEMENT_ID = '''||plan_stmt_id_||''' INTO plan_table FOR '||sql_text);
  return plan_stmt_id_;
end createplan;

Calculate top 10 database wait events per wait class

select * from (
select c.wait_class as wait_class,e.event as event
from V$system_event e ,V$SYSTEM_WAIT_CLASS c
where e.wait_class_id=c.wait_class_id
order by e.total_waits desc
) tab
where rownum <=10

Configure and send email from database using UTL_MAIL.SEND

---Tested on AIX 5 , Oracle 10.1 EE

create or replace
Procedure Sendemail
(
Receiver_1 Varchar2,
Subject Varchar2,
Message Varchar2,
exit_code OUT NUMBER
)
As
/*
start /oradata/db10g/rdbms/admin/utlmail.sql
start /oradata/db10g/rdbms/admin/prvtmail.plb
GRANT execute ON utl_mail TO cyag;
alter system set SMTP_OUT_SERVER='mymailServer' scope=spfile;
*/
Begin
Utl_Mail.Send('cyag@GATEWAY.nbg.com.cy',Receiver_1,Message => Message,Subject => Subject);
Exit_Code :=0;
Exception
When Others Then
exit_code :=1;
END SENDEMAIL;

Find logs for java application that is deployed on OC4J

tail -f $ORACLE_HOME /j2ee//application-deployments//_default_island_1/application.log

Find logs for java application that is deployed on OC4J

tail -f $ORACLE_HOME /j2ee//application-deployments//_default_island_1/application.log

Deploy ear application on Standalone oc4j

Deploy ear application on Standalone oc4j
1. Right click on “Project” and select “New”.
2. Select “Deployment Profiles” and on the right screen select “WAR file”. Click OK
3. Write the “Deployment Profile Name”: webapp1. Click OK.
4. Select under WEB-INF/classes, the Contributors and on the right screen select all the checkboxes. Click OK.
5. Right click on the webapp1 /weapp2 and select “Deploy to EAR file”.
6. Put ear under : C:\FileMover\oc4j_home\oc4j\j2ee\home\applications
7. Add the line below in the file C:\FileMover\oc4j_home\oc4j\j2ee\home\config\server.xml.
---application name="FileMoverMg" path="../../home/applications/FileMoverMg.ear" auto-start="true" />
8. Add the line below in the file C:\FileMover\oc4j_home\oc4j\j2ee\home\config\default-web-site.xml
---web-app application="FileMoverMg" name="FileMoverMg" root="/FileMoverMg" />

Revoke object granted to PUBLIC for a specific USER

SELECT ' revoke all on '||owner||'.'||table_name||' from public;'
FROM table_privileges
WHERE grantee = 'PUBLIC'
AND OWNER='UM'
ORDER BY owner, table_name;

When a new table is created , a SELECT priveleges on that TABLE is added to a ROLE.

When a new table is created , a SELECT priveleges on that TABLE is added to a ROLE.
====================================================================================
--Create the tigger and the hr_s role under sys.

CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL
ON DATABASE
DECLARE
main_sql_text varchar2(100);
job_name_ varchar2(30);
job_type_ varchar2(30);
job_action_ varchar2(4000);
sql_text varchar2(400);
job_id_ varchar2(30);
start_date_ timestamp;
BEGIN

if (ora_sysevent='CREATE' and ora_dict_obj_type='TABLE' and ora_dict_obj_owner='HR' )then
sql_text := 'grant select on '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' to hr_s';
select to_char(systimestamp,'DDMMYYYYHH24MISS') into job_id_ from dual;
select SYSTIMESTAMP + interval '1' minute into start_date_ from dual;
main_sql_text:=''''||sql_text||'''';
job_name_:='job'||job_id_;
job_type_:='PLSQL_BLOCK';
job_action_:='begin execute immediate('||main_sql_text||'); end;';
execute immediate ('begin DBMS_SCHEDULER.create_job (job_name=> :a,job_type=> :b,job_action => :c,start_date=> :d,repeat_interval => NULL,end_date=> NULL,enabled=>TRUE,comments=> NULL); end;') using job_name_,job_type_,job_action_,start_date_;
end if;
END ddl_trig;
/


--Grant the role on a privelges users.

Prepare Oracle Linux 6 for Installing Oracle 11gR2 (rpm packages))

Prepare Oracle Linux 6 for Installing Oracle 11gR2
by Michael Georgiou
============================================================
mpfr-2.4.1-6
cpp-4.4.4-13
ppl-0.10.2-11
gcc-4.4.4-13
cloog-ppl-0.15.7-1.2
binutils-2
glibc-2
nss-softokn-freebl-3
glibc-2
nss-softokn-freebl-3
compat-libstdc++-33
glibc-common-2
glibc-devel-2
glibc-headers-2
elfutils-libelf-0
elfutils-libelf-devel-0
gcc-4
gcc-c++-4
ksh-
libaio-0
libaio-devel-0
libgcc-4
libstdc++-4
libstdc++-devel-4
make-3.81mpfr-2.4.1-6
cpp-4.4.4-13
ppl-0.10.2-11
gcc-4.4.4-13
cloog-ppl-0.15.7-1.2
numactl-devel-2
sysstat-9
compat-libstdc++-33
compat-libcap


All packages are available :/media/OL6.0 i386 Disc 1 20110203/Server/Packages

SQL Performance

DECLARE
TYPE rc IS REF CURSOR;
l_rc rc;
l_dummy all_objects.object_name%TYPE;
l_start number DEFAULT dbms_utility.get_time;
BEGIN
FOR i IN 1 .. 1000
LOOP
OPEN l_rc FOR
'select object_name
from all_objects
where object_id = ' || i;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
dbms_output.put_line(l_dummy);
END LOOP;
dbms_output.put_line(ROUND((dbms_utility.get_time-l_start)/100, 2) ||' Seconds...' );
END;
/

Create schema statistics using DBMS_STATS

dbms_stats.gather_schema_stats
(
ownname=>'HR',
cascade=>TRUE, ---Indexes are included
method_opt=>'FOR ALL INDEXED COLUMNS',--Create Histograms
estimate_percent => dbms_stats.auto_sample_size, --Takes Sample size for the tables and Indexes
degree => 2 --Parallel degree 2
);

Create Service in a single Instance database

exec DBMS_SERVICE.CREATE_SERVICE('TPCH','TPCH');
exec DBMS_SERVICE.START_SERVICE('TPCH','ORCL');


TPCH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TPCH)
)
)


sqlplus system/xxxxxx@TPCH