Home › Category Archives › Oracle

GlassFish common tasks

GlassFish is an open source java application server. here are some hint about common tasks:

Defaults:

password: changeit

admin port: 4848

HTTP port: 8080

HTTPS Port: 8181

Message Queue port: 7676

 

To Start/stop the default domain:

asadmin  start-domain

asadmin stop-domain

To list domains

asadmin list-domain

 

To Start javadb:

asadmin start-database -dbhome  install-dir/javadb.

 

Oracle Jobs

SELECT s.SID, s.serial#, s.ownerid,
s.status, s.machine, s.terminal,
s.program, s.username, p.addr,
s.LOGON_TIME
FROM v_$process p, v_$session s
WHERE ((p.addr = s.paddr ))

To check datapump jobs
==============================
Select * from dba_datapump_jobs;

To check Running Jobs
==============================
select * from dba_jobs_running;

select object_name from dba_objects where object_name like ‘%JOB%’ and object_type in (‘TABLE’,’VIEW’) ;

To check Scheduled Jobs
==============================
DBA_SCHEDULER_JOBS
DBA_SCHEDULER_JOB_ARGS
DBA_SCHEDULER_JOB_CLASSES
DBA_SCHEDULER_JOB_LOG
DBA_SCHEDULER_JOB_RUN_DETAILS
DBA_SCHEDULER_RUNNING_JOBS

Datapump commands
===============================
help –> to
KILL_JOB
ATTACH

Oracle Statistics

Statistics can be computed or estimated for a sample

ANALYZE TABLE/Index  Object_Name COMPUTE/ESTIMATE STATISTICS SAMPLE no ROWS/PERCENT;

EXEC DBMS_UTILITY.analyze_schema(‘SCHEMA_NAME’,’COMPUTE/ESTIMATE’, estimate_percent/estimate_rows =>);
Oracle recommends using the DBMS_STATS package for collecting the statistics rather than using Analyze directly.

 

EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats(‘SCHEMA_NAME’);
EXEC DBMS_STATS.gather_schema_stats(‘SCHEMA_NAME’, estimate_percent => 15);

 

To Compute Statistics for one partition of a table:
Analyze Table_Name Partition(Partition_Name) compute statistics;

DBA_OPTSTAT_OPERATIONS history of statistics operations performed at the schema and database level using the DBMS_STATS package.
DBA_USTATS Statistics collected on either table or index level
DBA_TAB_STATS_HISTORY history of table statistics modifications for all tables in the database. (saved only for 31 day)

Oracle Table Partition

Very Large tables and indexes can be partitioned in order to decompose the big object into smaller pieces.

This can have major impact on performance as well as maintenance and management activities.

There are mainly 3 data distribution methods

  • List Partition
  • Range Partition
  • Hash Partition

Each table can be partitioned using

  • Single Level Partition
  • Composite Partition

Each partition can be stored in different tablespace.

 

Reference Partition: This is supported in Oracle 11g, where the partitions will be based on foreign key  values.

Below are some examples on creating and administrating partitions

 

drop  TABLE TWOPARTITION;

CREATE TABLE TWOPARTITION
(
ID1    NUMBER,
Name1  VARCHAR2(100),
ID2    NUMBER,
Name2  VARCHAR2(100)
)
PARTITION BY RANGE (id1)
SUBPARTITION BY LIST (id2)
SUBPARTITION TEMPLATE
(SUBPARTITION sub_p_1 VALUES (1,2,3,4,5,6,7,8,9),
SUBPARTITION Sub_p_2 VALUES (DEFAULT)
)
(
PARTITION P1_1 VALUES LESS THAN (1000),
PARTITION P1_2 VALUES LESS THAN (MAXVALUE)
);

 

To check which tables are partitioned

Select * from user_part_tables;

To Get information about table partitions

select * from user_tab_partitions;

 

select * from user_tab_Subpartition;

User_Part_Key_Columns

User_SubPart_Key_Columns

User_Part_Col_Statistics

User_subPart_Col_Statistics

User_subpartition_templates

 

To Split one partition

alter table twopartition split partition p1_2 at (6000 ) into (partition p1_3, partition p1_rest);
alter table twopartition split partition p1_rest at (7000 ) into (partition p1_4, partition p1_rest);
alter table twopartition split partition p1_rest at (8000 ) into (partition p1_6, partition p1_rest);

To Merge 2 partitions

alter table twopartition merge partitions P1_3, P1_4 into  Partition P1_3N;
alter table twopartition merge partitions P1_6, P1_REST into  Partition P1_REST;
alter table twopartition merge partitions P1_3N, P1_REST into  Partition P1_2;

 

select * from user_tab_partitions;

you might find some tables named like BIN$SAVC these are from the recyclebin

select * from recyclebin;

to remove them use the Purge command

purge Table Partition BIN$SehH2WsDSUOowSrN47+PWw==$0;

or purge the complete recyclebin

PURGE RECYCLEBIN;

Truncate partition

ALTER TABLE twopartition
TRUNCATE PARTITION P_REST;

Select * from twopartition partition(P_REST);

Using Oracle Data Pump for backup and restore

Examples

expdp username/password full=n schemas=Schema_Name directory=Data_Pump_Dir dumpfile=dumpfilename logfile=logfilename job_name=jobname

During the import, we can import into another tablespace and schema useing REMAP_TABLESPACE and REMAP_SCHEMA

impdp username/password REMAP_TABLESPACE=Source_TS:Target_TS REMAP_SCHEMA=Source_Schema:Target_Schema full=n  schemas=Schemaname directory=Data_Pump_Dir dumpfile=dumpfilename logfile=logfilename job_name=jobname

Time stamp

Some People like to store the time stamp as a millisecond time value, to convert it to date in Oracle:

select to_date(’01-01-1970′,’dd-mm-yyyy’)+ column_name/(1000*60*60*24)

from Table_Name;

SQL result as XML

select dbms_xmlgen.getxml(‘select sysdate from dual’) from dual;

Oracle, resize the undo tablespace

Connect to Oracle as sysdba

  • sqlplus /nolog
  • connect sys/xxx@ORCL as sysdba

Query the V$Parameter to Find out which undo tablespace is in use

    select name,value from v$parameter where name in (‘undo_management’,’undo_tablespace’);NAME VALUE
    ——————- ——————-
    undo_management AUTO
    undo_tablespace UNDOTBS1

Temporally, Create a new undo tablespace UNDOTBS_temp

  • create undo tablespace UNDOTBS_temp datafile D:\oradata\ORCL\UNDOTBS_temp_01.DBF’ size 1000m reuse;

Alter the system to use the new undo tablespace.

    alter system set undo_tablespace=UNDOTBS_temp;

Drop and recreate the UNDOTBS1 tablespace with a smaller size.

    drop tablespace UNDOTBS1 including contents;
    Tablespace dropped.
    create undo tablespace UNDOTBS1 datafile ‘D:\oradata\ORCL\UNDOTBS1_01.DBF’ size 2000m reuse;
    Tablespace created.

Alter the system to use the new undo tablespace

    alter system set undo_tablespace=UNDOTBS1 ;

Drop the temporaly created tablespace UNDOTBS_temp.

    drop tablespace UNDOTBS1 including contents;