Oracle Rman Show Backups

select operation as "OPERACAO",
object_type as "TIPO",
status,
output_device_type as "MEDIA",
to_char(end_time,'DD-MM-RRRR HH24:MI:SS') as "DATA",
round(MBYTES_PROCESSED/1024,2) as "TAMANHO(MB)"
from
v$rman_status
where
operation = 'BACKUP'
and trunc(end_time)>=trunc(sysdate-1)
order by
end_time
SQL> /

OPERACAO   TIPO          STATUS     MEDIA      DATA                TAMANHO(MB)
---------- ------------- ---------- ---------- ------------------- -----------
BACKUP     ARCHIVELOG    COMPLETED  SBT_TAPE   04-02-2013 09:17:55       19.87
BACKUP     ARCHIVELOG    COMPLETED  SBT_TAPE   04-02-2013 12:05:11        6.48
BACKUP     ARCHIVELOG    COMPLETED  SBT_TAPE   05-02-2013 00:06:20        3.87
BACKUP     ARCHIVELOG    COMPLETED  SBT_TAPE   05-02-2013 12:05:38        8.61

Read More

ORACLE / RMAN - Status

set pages 120
set lines 120
column MEDIA format a10
column STATUS format a10
column OPERACAO format a10

select operation as "OPERACAO",
object_type as "TIPO",
status,
output_device_type as "MEDIA",
to_char(end_time,'DD-MM-RRRR HH24:MI:SS') as "DATA",
round(MBYTES_PROCESSED/1024,2) as "TAMANHO(MB)"
from
v$rman_status
where
operation <> 'CATALOG'
and trunc(end_time)>=trunc(sysdate-1)
order by
end_time;



OPERACAO   TIPO          STATUS     MEDIA DATA                TAMANHO(MB)
---------- ------------- ---------- ----- ------------------- -----------
BACKUP     ARCHIVELOG    COMPLETED  SBT_T 04-02-2013 09:17:55       19.87
APE
RMAN                     COMPLETED        04-02-2013 09:17:56       19.87
BACKUP     ARCHIVELOG    COMPLETED  SBT_T 04-02-2013 12:05:11        6.48
APE
RMAN                     COMPLETED        04-02-2013 12:05:11        6.48
LIST       DB FULL       COMPLETED        04-02-2013 18:15:50           0
RMAN                     COMPLETED        04-02-2013 18:22:16           0
LIST       DB FULL       COMPLETED        04-02-2013 18:22:48           0
RMAN                     COMPLETED        04-02-2013 18:27:21           0

---------------------------------------------------////---------------------------------------------------------------


[oracle@Linux ~]$ export NLS_DATE_FORMAT="DD-MM-YYYY HH24:MI:SS"
[oracle@Linux ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Feb 5 07:47:28 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: RM (DBID=2664802127)

RMAN> list backup ;

Read More

Oracle - [ Alter Database Datafile ]

 1* select file_id,file_name,bytes/1024/1024,maxbytes/1024/1024 from dba_data_files order by file_id
SQL> /

   FILE_ID FILE_NAME  BYTES/1024/1024 MAXBYTES/1024/1024
---------- ------------------------------ --------------- ------------------
1 +DSKDATA/oralee/datafile/syste      740  32767.9844
  m.334.783430381

2 +DSKDATA/oralee/datafile/sysau      810  32767.9844
  x.335.783430383

3 +DSKDATA/oralee/datafile/undot      815  32767.9844
  bs1.336.783430385

4 +DSKDATA/oralee/datafile/algor     4000 8000
  .338.783430389

5 +DSKDATA/oralee/datafile/algor     4000 8000
  _indices.339.783430395

6 +DSKDATA/oralee/datafile/undot      200  32767.9844
  bs2.340.783430401

7 +DSKDATA/oralee/datafile/users   161.25  32767.9844
  .341.783430401

SQL> alter database datafile 1 autoextend on next 500m maxsize 8000m;

Database altered.

SQL> alter database datafile 2 autoextend on next 500m maxsize 8000m;

Database altered.

SQL> l

  1* alter database datafile 2 autoextend on next 500m maxsize 8000m

SQL> c/2/3/

  1* alter database datafile 3 autoextend on next 500m maxsize 8000m

SQL> /

Database altered.

SQL> c/3/4/

  1* alter database datafile 4 autoextend on next 500m maxsize 8000m

SQL> c/4/6/

  1* alter database datafile 6 autoextend on next 500m maxsize 8000m

SQL> /

Database altered.

SQL> c/6/7/

  1* alter database datafile 7 autoextend on next 500m maxsize 8000m

SQL> /

Database altered.

SQL> set pages 120

SQL> set lines 100

SQL> column file_name format a30

SQL> /

   FILE_ID FILE_NAME  BYTES/1024/1024 MAXBYTES/1024/1024

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

1 +DSKDATA/oralee/datafile/syste      740 8000

  m.334.783430381

2 +DSKDATA/oralee/datafile/sysau      810 8000

  x.335.783430383

3 +DSKDATA/oralee/datafile/undot      815 8000

  bs1.336.783430385

4 +DSKDATA/oralee/datafile/algor     4000 8000

  .338.783430389

5 +DSKDATA/oralee/datafile/algor     4000 8000

  _indices.339.783430395

6 +DSKDATA/oralee/datafile/undot      200 8000

  bs2.340.783430401

7 +DSKDATA/oralee/datafile/users   161.25 8000

  .341.783430401

7 rows selected.

Read More

Oracle [ Size by Partitions ]

How do I know if an object is partitioned or not?


SELECT * FROM dba_tab_partitions WHERE table_name = '<table_name>';

SELECT * FROM dba_ind_partitions WHERE index_name = '<index_name>';

1 -Partitions = NO

select owner, segment_type, segment_name, bytes/1024/1024 mbytes
from dba_segments
where segment_name = 'TABLE_NAME';

2 - Partitions = YES

select seg.OWNER , seg.PARTITION_NAME, seg.SEGMENT_NAME , seg.BYTES/1024/1024 mbytes from dba_segments seg
inner join dba_tab_partitions part
on seg.SEGMENT_NAME=part.TABLE_NAME
and seg.owner = part.table_owner
where part.TABLE_NAME='TABLE_NAME';

Read More

Perl with SQLServer -> =)

use DBI;

my $dsn = 'DBI:ODBC:Driver={SQL Server}';
my $host = 'WINSRV';
my $database = 'Relatorios';
my $user = 'sa';
my $auth = 'm1f1g1f4$';


my $dbh = DBI -> connect( "dbi:ODBC:driver={SQL Server};server={$host};Trusted Connection=yes, $user, $auth { PrintError => 0,RaiseError => 0,}") or die "\n\nthe mssql connection died with the following error: \n\n$DBI::errstr\n\n";

my $sth = $dbh->prepare('SELECT *  FROM sys.tables');

$sth->execute();

while(my @result = $sth->fetchrow_array() ){
        print join ("\t",@result),"\n";
}

$dbh->disconnect();

Read More

Perl Date Time [ yyyymmdd ]

#!/usr/bin/perl

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);

my $yyyy_mm_dd = sprintf( "%4d%02d%02d", $year+1900,$mon+1,$mday );

print "$yyyy_mm_dd\n";

use POSIX;

my $DateTime = strftime "%F", localtime $^T;

$DateTime =~ s/-//g;

print "$DateTime";

Read More

Rman / ORACLE

$rman target / nocatalog

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Dec 11 16:05:05 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: SFT (DBID=2981456125)
using target database control file instead of recovery catalog

RMAN> delete noprompt archivelog until time 'sysdate-2/24';

Read More