The easy way to Get table and index DDL script

The easy way to Get table and index DDL script:

The dbms_metadata utility helps to display DDL directly from the data dictionary. We see the small example.
Initial setup:SQL> conn scott/tiger;
Connected.
SQL> create table GET_TAB_SCRIPT
2 (
3 c1 varchar2(10),
4 c2 number
5 );

Table created.

SQL> insert into get_tab_script values ('A',10);

1 row created.

SQL> commit;

Commit complete.

SQL> create index idx_1 on GET_TAB_SCRIPT (c1);

Index created.
To get table script:

SQL> select dbms_metadata.get_ddl('TABLE','GET_TAB_SCRIPT') from dual;
DBMS_METADATA.GET_DDL('TABLE','GET_TAB_SCRIPT')
------------------------------------------------

CREATE TABLE "SCOTT"."GET_TAB_SCRIPT"
( "C1" VARCHAR2(10),
"C2" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"
To get index script:

SQL> select dbms_metadata.get_ddl('INDEX','IDX_1') from dual;

DBMS_METADATA.GET_DDL('INDEX','IDX_1')
----------------------------------------

CREATE INDEX "SCOTT"."IDX_1" ON "SCOTT"."GET_TAB_SCRIPT" ("C1")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"

Read More

Autotrace from SYS - Some things appear to work but don't really

Autotrace from SYS - Some things appear to work but don't really

We use autotrace to get the Execution Plan and Statistics. It appear to work but don't really from SYS user. We see that.

SQL> create table t ( num number(2), name varchar2(10));

Table created.



SQL> insert into t values(1,'A');

1 row created.

SQL> insert into t values(2,'A');

1 row created.

SQL> select * from t;

NUM NAME
---------- ----------
1 A
2 A



SQL> set autotrace on;
SQL> select * from t;

NUM NAME
---------- ----------
1 A
2 A
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
463 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
We see the same thing from the SYS user.

SQL> conn sys/password@oraprc as sysdba
Connected.
SQL> set autotrace on;
SQL> select * from system.t;

NUM NAME
---------- ----------
1 A
2 A


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SYSDBA, SYSOPER, "internal" and sys in general shouldn't be used for anything other then admin.

Read More

Array Size Effects

The array size is the number of rows fetched (or sent, in the case of inserts, updates, and deletes) by the server at a time. It can have a dramatic effect on performance.
SQL> drop table t;

Table dropped.

SQL> create table t as select * from all_objects;

Table created.

SQL> select count(*) from t;
COUNT (*)
----------
29120
SQL> set autotrace traceonly statistics;
SQL> set arraysize 2
SQL> select * from t;

29120 rows selected.

Statistics
----------------------------------------------------------
14779 consistent gets

Note how one half of 29120 (rows fetched) is very close to 14779 , the number of consistent gets. Every row we fetched from the server actually caused it to send two rows back. So, for every two rows of data, we needed to do a logical I/O to get the data. Oracle got a block, took two rows from it, and sent it to SQL*Plus. Then SQL*Plus asked for the next two rows, and Oracle got thatblock again or got the next block, if we had already fetched the data, and returned the next two rows, and so on.
Next, let’s increase the array size:
SQL> set arraysize 5
SQL> select * from t;

29120 rows selected.

Statistics
----------------------
6152 consistent gets

Now, 29120 divided by 5 is about 5824, and that would be the least amount of consistent gets we would be able to achieve (the actual observed number of consistent gets is slightly higher).

All that means is sometimes in order to get two rows, we needed to get two blocks: we got the last row from one block and the first row from the next block.
Let’s increase the array size again:

SQL> set arraysize 10
SQL> select * from t;

29120 rows selected.

Statistics
----------------------
3271 consistent gets

SQL> set arraysize 25
SQL> select * from t;

29120 rows selected.

Statistics
----------------------
1551 consistent gets

SQL> set arraysize 100
SQL> select * from t;

29120 rows selected.


Statistics
----------------------
688 consistent gets

SQL> set arraysize 500
SQL> select * from t;

29120 rows selected.


Statistics
------------------
460 consistent gets
............
As you can see, as the array size goes up, the number of consistent gets goes down. So, does that mean you should set your array size to 5,000, as in this last test? Absolutely not. If you notice, the overall number of consistent gets has not dropped dramatically between array sizes of 100 and 5,000.

It would be better to have more of a stream of information flowing: Ask for 100 rows, get 100 rows, ask for 100, process 100, and so on. That way, both the client and server are more or less continuously processing data, rather than the processing occurring in small bursts.

Read More

Script to Create a Plan table

create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000)
);

Read More

PGA and UGA Memory Usage Script

SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
,

(select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory') session_pga_memory
,

(select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory max') session_pga_memory_max
,

(select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
,

(select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory max') session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC

Read More

Autotrace Setup

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 29 14:46:26 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
/* Connect as a SYSDBA */

SQL> conn sys/password@oraprc as sysdba
Connected.



SQL> @E:\TestDB\sqlplus\admin\plustrce.sql

SQL> drop role plustrace;

SQL> create role plustrace;
Role created.

SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.

SQL> grant select on v_$statname to plustrace;
Grant succeeded.

SQL> grant select on v_$session to plustrace;
Grant succeeded.

SQL> grant plustrace to dba with admin option;
Grant succeeded.

SQL> set echo off
/* Grant the plustrace to pulic */

SQL> grant plustrace to public;
Grant succeeded.

/* Connect as a Demo */

SQL> conn demo/demo@oraprc
Connected.

SQL> set autotrace traceonly
SQL> select * from t where num = 2;
no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
219 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>

Read More

Explain Plan Setup

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 29 14:46:26 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
1. Creation of the plan table.
SQL> @E:\TestDB\rdbms\admin\utlxplan.sql
Table created.
SQL> create table t( num number(2));
Table created.
SQL> delete from plan_table;
0 rows deleted.
2. Collect the plan for SQL script.
SQL> explain plan for 2 select * from t where num = 2;
Explained.
3. View the Explain plan.

SQL> @E:\TestDB\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT
* 1 TABLE ACCESS FULL T
--------------------------------------------------------------------

Predicate Information (identified by operation id):---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------1 - filter("T"."NUM"=2)
Note: rule based optimization
14 rows selected.
SQL>

Read More

Oracle streams implementation at schema level

conn sys/password@dba1 as sysdba

create tablespace demo_TS datafile 'E:\TestDB\oradata\DBA1\demo_DAT.dbf' size 50M autoextend off extent management local;
create user demo IDENTIFIED BY demo DEFAULT TABLESPACE demo_TS TEMPORARY TABLESPACE TEMP;
ALTER USER demo QUOTA UNLIMITED on demo_TS;
grant connect to demo;


conn sys/password@dba2 as sysdba

create tablespace demo_TS datafile 'E:\TestDB\oradata\DBA2\demo_DAT.dbf' size 50M autoextend off extent management local;
create user demo IDENTIFIED BY demo DEFAULT TABLESPACE demo_TS TEMPORARY TABLESPACE TEMP;
ALTER USER demo QUOTA UNLIMITED on demo_TS;
grant connect to demo;

conn demo/demo@dba1

create table department
(departmentNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13));

create table emp
(EMPNO NUMBER(2),
DNO VARCHAR2(14),
LOC VARCHAR2(13));

insert into department values ( 10, 'ACCOUNTING', 'NEW YORK');
insert into department values ( 20, 'RESEARCH', 'DALLAS');
insert into department values ( 30, 'SALES' , 'CHICAGO');
insert into department values ( 40, 'OPERATIONS', 'BOSTON');


conn sys/password@dba1 as sysdba

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=1;
ALTER SYSTEM SET AQ_TM_PROCESSES=1;
ALTER SYSTEM SET GLOBAL_NAMES=TRUE;
ALTER SYSTEM SET COMPATIBLE='9.2.0' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;


CONN sys/password@DBA1 AS SYSDBA

CREATE USER strmadmin_dept IDENTIFIED BY strmadminpw
DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin_dept;

GRANT EXECUTE ON DBMS_AQADM TO strmadmin_dept;

GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin_dept;

GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin_dept;

GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin_dept;

GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin_dept;

GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin_dept;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'strmadmin_dept',
grant_option => FALSE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'strmadmin_dept',
grant_option => FALSE);
END;
/

CONNECT strmadmin_dept/strmadminpw@DBA1
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

CREATE DATABASE LINK dba2 CONNECT TO strmadmin_dept IDENTIFIED BY strmadminpw USING 'DBA2';

conn sys/password@dba2 as sysdba

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=1;
ALTER SYSTEM SET AQ_TM_PROCESSES=1;
ALTER SYSTEM SET GLOBAL_NAMES=TRUE;
ALTER SYSTEM SET COMPATIBLE='9.2.0' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;


CONN sys/password@DBA2 AS SYSDBA

CREATE USER strmadmin_dept IDENTIFIED BY strmadminpw
DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin_dept;

GRANT EXECUTE ON DBMS_AQADM TO strmadmin_dept;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin_dept;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin_dept;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin_dept;
GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin_dept;
GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin_dept;

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'strmadmin_dept',
grant_option => FALSE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'strmadmin_dept',
grant_option => FALSE);
END;
/

CONNECT strmadmin_dept/strmadminpw@DBA2
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();


CONN sys/password@DBA1 AS SYSDBA
GRANT ALL ON demo.department TO strmadmin_dept;
GRANT ALL ON demo.emp TO strmadmin_dept;

CONN sys/password@DBA1 AS SYSDBA

CREATE TABLESPACE logmnr_ts1 DATAFILE 'E:\TestDB\ORADATA\DBA1\logmnr02.dbf'
SIZE 10 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnr_ts1');

CONN sys/password@DBA1 AS SYSDBA

ALTER TABLE demo.department ADD SUPPLEMENTAL LOG GROUP log_group_department_pk1 (departmentno) ALWAYS;
ALTER TABLE demo.emp ADD SUPPLEMENTAL LOG GROUP log_group_emp_pk1 (empno) ALWAYS;

CONNECT strmadmin_dept/strmadminpw@DBA1


BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'demo',
streams_name => 'dba1_to_dba2',
source_queue_name => 'strmadmin_dept.streams_queue',
destination_queue_name => 'strmadmin_dept.streams_queue@dba2',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'dba1');
END;
/

CONNECT strmadmin_dept/strmadminpw@DBA1

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'demo',
streams_type => 'capture',
streams_name => 'capture_demo',
queue_name => 'strmadmin_dept.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'dba1');
END;
/



exp userid=demo/demo@dba1 FILE=E:\TestDB\testDump\demo_instant.dmp OBJECT_CONSISTENT=y ROWS=n

imp userid=demo/demo@dba2 FILE=E:\TestDB\testDump\demo_instant.dmp IGNORE=y COMMIT=y LOG=import.log STREAMS_INSTANTIATION=y


CONN sys/password@DBA2 AS SYSDBA
ALTER TABLE demo.department DROP SUPPLEMENTAL LOG GROUP log_group_department_pk1;
ALTER TABLE demo.emp DROP SUPPLEMENTAL LOG GROUP log_group_emp_pk1;



CONNECT strmadmin_dept/strmadminpw@dba1
DECLARE
v_scn NUMBER;
BEGIN
v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@DBA2(
source_schema_name => 'demo',
source_database_name => 'dba1',
instantiation_scn => v_scn);
END;
/


CONNECT strmadmin_dept/strmadminpw@DBA2

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'demo',
streams_type => 'apply',
streams_name => 'apply_demo',
queue_name => 'strmadmin_dept.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'dba1');
END;
/

CONNECT strmadmin_dept/strmadminpw@DBA2
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_demo',
parameter => 'disable_on_error',
value => 'n');

DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_demo');
END;
/


CONNECT strmadmin_dept/strmadminpw@DBA1
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_demo');
END;
/

Read More

Table Space Status Script

Script to identify the table space status:
SELECT Substr(ddf.tablespace_name,1,20) "Tablespace Name",
Round(ddf.bytes/1024/1024,2) "Allocated Bytes(MB)",
Round(de.used_bytes/1024/1024,2) "Used Bytes(MB)",
Round(dfs.free_bytes/1024/1024,2) "Free Bytes(MB)",
Round((de.used_bytes/ddf.bytes)*100,2) "% Used Bytes ",
Round((dfs.free_bytes/ddf.bytes)*100,2) "% Free Bytes"
FROM DBA_DATA_FILES DDF,
(SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) DE,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) dfs
WHERE de.file_id = ddf.file_id
AND ddf.file_id = dfs.file_id
ORDER BY ddf.tablespace_name;

Read More

Identify the redo size of the current session

select name,a.value from v$sesstat a, v$sysstat b where b.statistic#=a.statistic# and b.name = 'redo size' and sid = 16;

Read More

Query to identify the SID of current session

select sid from V$mystat where rownum =1;

Read More