Monday, September 29, 2014

Oracle: ORA-38301: can not perform DDL/DML over objects in Recycle Bin

As of 10.2g, Oracle introduced a recycle bin for dropped objects, in order to recover them with flashback.
So, if for example, you drop a table like in
DROP TABLE RADIUS_DB.RADACCT3;
Then you won't be able to drop related contraints that were renamed following the table drop, like BIN$7Cd8os82uGngQAB/AQANPQ==$0

To see objects in recycle bin:
select * from dba_recyclebin order by droptime desc;
To remove the BIN* constraints, you need to remove the dropped table from the recycle bin:
purge table radius_db.radacct3;

Oracle: schemas/tables measurements

Size of schemas:
SELECT owner, sum(bytes)/1024/1024 "Size in MiB"
FROM dba_segments
WHERE owner not in ('GPRS_TEST_DB', 'GPRS_TRIAL_DB', 'MSG_DB', 'PARAM_DB', 'PERF_DB', 'TASK_DB', 'VAS_INDICATOR_DB', 'VAS_LOG_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'XDB')
and owner like '%_DB'
group by owner
order by owner;
Size of tables
SELECT owner, segment_type, segment_name, sum(bytes)/1024/1024 "Size in MiB"
FROM dba_segments
WHERE segment_type like 'TABLE%'
and segment_name not like 'BIN%'
group by owner, segment_type, segment_name;
Num_rows of table
select owner, table_name, num_rows
from dba_tables
WHERE owner not in ('GPRS_TEST_DB', 'GPRS_TRIAL_DB', 'MSG_DB', 'PARAM_DB', 'PERF_DB', 'TASK_DB', 'VAS_INDICATOR_DB', 'VAS_LOG_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'XDB')
and owner like '%_DB'
and table_name ='HT_CO_OFFER'
order by owner;
Count indexes per table
SELECT owner, table_name, count(index_name)
FROM all_indexes
WHERE owner not in ('GPRS_TEST_DB', 'GPRS_TRIAL_DB', 'MSG_DB', 'PARAM_DB', 'PERF_DB', 'TASK_DB', 'VAS_INDICATOR_DB', 'VAS_LOG_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'XDB')
and owner like '%_DB'
group by owner, table_name
order by owner;

Saturday, September 27, 2014

OSX: convert Windows text file into OSX format

A text file created under Windows and using accents shows bad accents under OSX, e.g. amitiÈ instead or amitié or opËre instead of opère.
This is due to the encoding, which is Latin1 under Windows, and UTF8 under OSX.

Fortunately, OSX has a built in conversion tool to fix this:
Launch Terminal
> iconv -f latin1 -t utf8 BD_anais.txt > BD_anais2.txt

Thursday, September 25, 2014

Google drive: Host some CSS/JS files

This is how to store some web files in what Google calls the new Drive as of today, 25-09-2014.
Say we want to fetch some CSS file from a folder on Google Drive.
  1. Right click on the folder and select "Share..."
  2. Select "More..." in the popup dialog
  3. Check the "Public on the web" box
  4. A public URL is proposed in the following dialog
  5. The folder URL is of the form:

    https://drive.google.com/folderview?id=0B7uYPyj_NiVxYVltWjZ0VUVULTQ&usp=sharing.

    In order to use it in a web page, pick the id from the proposed URL and re-use it in the following new URL:
    https://googledrive.com/host/0B7uYPyj_NiVxYVltWjZ0VUVULTQ
  6. Then add the filename to be used, e.g.

    https://googledrive.com/host/0B7uYPyj_NiVxYVltWjZ0VUVULTQ/toto.css
Et voila.

Oracle: Dump data into CSV file

Tom Kyte gives details on how to do that here:
http://tkyte.blogspot.fr/2009/10/httpasktomoraclecomtkyteflat.html

NB: See the flat script as a base and use chr(59) if you want to have ';' instead of tabs in the output.

Oracle: Handle error codes with sqlplus

Tom Kyte explains it here:
http://tkyte.blogspot.fr/2010/04/new-thing-about-sqlplus.html
  1. Start with
    set errorlogging on identifier toto
    delete from sperrorlog where identifier = 'toto'
    /
    
  2. Then use some sqlplus commands
  3. And terminate with
    col nb_errors new_value nb_errors
    select count(*) as nb_errors from sperrorlog where identifier = 'toto'
    /
    exit nb_errors
This allows for testings the return code of sqlplus in a bash / perl script.

NB: See http://ss64.com/ora/syntax-sqlplus.html for the sqlplus syntax

Friday, September 19, 2014

Oracle: ORA-00600: internal error code, arguments: [kqd-objerror$]

If this error occurs when trying to drop a trigger, e.g.
DROP TRIGGER RADIUS_DB.RADACCT_RADACCTID;

  1. Find the object_id of the trigger
    select object_id from dba_objects where object_name='RADACCT_RADACCTID';
  2. Insert the object_id into objerror$
    insert into sys.objerror$ values(126915);
    commit;
  3. shutdown abort
  4. startup
  5. Retry to drop the trigger, this should be OK now.
    DROP TRIGGER RADIUS_DB.RADACCT_RADACCTID;

Bash: delete last n lines of a file

head -n -100000 FILE > NEWFILE

Thursday, September 18, 2014

Oracle: using statspack

Use the steps as described in:
http://www.oracledistilled.com/oracle-database/performance/installing-and-configuring-statspack/
which are:
1) Create the tablespace to hold the Statspack tables.
CREATE TABLESPACE statspack_db_data DATAFILE 
  '/home/oracle/oracle/oradata/CRMMBQT/statspack_db_data_01.dbf' SIZE 500M AUTOEXTEND ON NEXT 10M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL uniform size 1M
SEGMENT SPACE MANAGEMENT AUTO;
2) Run the ORACLE_HOME/rdbms/admin/spcreate.sql script to create the PERFSTAT schema.
cd /home/oracle/oracle/product/11.2.0/dbhome_1/rdbms/admin
> sqlplus / as sysdba
SQL> @spcreate
A list of available table spaces will be presented. Type in the name of the table space created earlier and hit the enter key.
When selecting a temporary table space for the PERFSTAT user, keep the default tablespace as proposed.

3) Execute a snapshot w/ level 7:
exec statspack.snap(i_snap_level => 7);
4) Run a report:
ORACLE_HOME\rdbms\admin\spreport.sql is used to generate an instance level report.
You will then pick a beginning snapshot and an ending snapshot, as well as a file name to save results.
cd /home/oracle/oracle/product/11.2.0/dbhome_1/rdbms/admin
> sqlplus / as sysdba
SQL> @spreport

Wednesday, September 10, 2014

Oracle: drop interval partitions

source: http://prutser.wordpress.com/2010/01/11/dropping-interval-partitions/
-- Count number of partitions: 905
select count(*) from dba_tab_partitions where table_name = 'AT_PROCESS_LOG';

-- List of partitions to drop: keep 30 latest partitions
select * from (
select partition_name, interval, high_value, partition_position
from dba_tab_partitions where table_name = 'AT_PROCESS_LOG'
order by partition_position desc
) where 1 < partition_position and partition_position < 905 - (30-1);
Drop syntax examples:
-- Drop partition w/ high_value = 16-03-2012
alter table monitor_db.at_process_log drop partition for (to_date('15-03-2012','DD-MM-YYYY')); 

-- Drop partition from name
alter table monitor_db.at_process_log drop partition SYS_P61;

Monday, September 8, 2014

Oracle: Random UUID

v_uuid RAW(16) := sys.dbms_crypto.randombytes(16);

Friday, September 5, 2014

Delete files older than

E.g. Delete files in current directory older than 90 days :
find . -mtime +90 -delete