Enable or Disable in Archive log mode in Oracle Database

 Enable or Disable in Archive log mode in Oracle Database

Introduction

Two types of logging mode in oracle database

1.Archivelog mode: In this Archivelog mode after the online redo logs are filled , it will move to the archive location,archivelog mode you can put the database in for creating a backup of all transactions occured in the database so that you can recover at any point of time 

2.Noarchivelog mode:In this Noarchivelog mode Filled  online redo logs wont be accepted,archives are insted they will be overwritten, In this mode absence of archivelog and database not be recoverd at any point of time 

Enable archive log mode

SQL > select name,log_mode from v$database;

NAME      LOG_MODE

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

PROD      NOARCHIVELOG

 

SQL > archive log list

Database log mode              No Archive Mode

Automatic archival             Disbled

Archive destination            /chaitanya/arch/PROD

Oldest online log sequence     206506

Next log sequence to archive   206512

Current log sequence           206512

 

 make sure db is running in spfile

SQL > alter system set log_archive_dest_1='LOCATION=/chaitanya/arch/PROD' scope=spfile;

database altered.

 

SQL >shutdown  immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL > startup mount

ORACLE instance started.

Total System Global Area 5415597568 bytes

Fixed Size                  2170304 bytes

Variable Size             805970240 bytes

Database Buffers         6502926848 bytes

Redo Buffers                3530176 bytes

Database mounted.

 

SQL >alter database archivelog;

 

database altered.

 

SQL >alter database open;

 

database altered.

 

SQL >select name,log_mode from v$database;

 

NAME      LOG_MODE

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

PROD      ARCHIVELOG

 

SQL >archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /chaitanya/arch/PROD

Oldest online log sequence     206506

Next log sequence to archive   206512

Current log sequence           206512

 

Disable archivelog mode

SQL >select name,log_mode from v$database;

 

NAME      LOG_MODE

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

PROD      ARCHIVELOG

 

SQL > archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /chaitanya/arch/PROD

Oldest online log sequence     206506

Next log sequence to archive   206512

Current log sequence           206512

 

 

SQL > shutdown  immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL > startup mount

ORACLE instance started.

Total System Global Area 5415597568 bytes

Fixed Size                  2170304 bytes

Variable Size             805970240 bytes

Database Buffers         6502926848 bytes

Redo Buffers                3530176 bytes

Database mounted.

 

SQL >alter database noarchivelog;

 

database altered.

 

SQL >alter database open;

 

database altered.

 

 

SQL > select name,log_mode from v$database;

 

NAME      LOG_MODE

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

PROD      NOARCHIVELOG

 

SQL > archive log list

Database log mode              No Archive Mode

Automatic archival             Disbled

Archive destination            /chaitanya/arch/PROD

Oldest online log sequence     206506

Next log sequence to archive   206512

Current log sequence           206512

Info: Info on enable or disable archivelog mode it may be differ in your environment like production,testing,develoment etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Enable or Disable Flashback Technology in Oracle

 Enable or Disable Flashback Technology in Oracle

Introduction

Using Flashback Technology  we can restore the database and dropped users,and tables,schemas  in oracle we will flashback the database to past when the database ,user,table,schema is available at the time of dropped database before.

Here in this blog i am going to expalin how to enable or disable by using Flashback technology in oracle

Let us start the process

Enable Flashback

The Database must be in archive log mode

Here i am showing how to enable archive log mode

SQL > select name,log_mode from v$database;

 

NAME      LOG_MODE

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

PROD      NOARCHIVELOG

 

SQL > archive log list

Database log mode              No Archive Mode

Automatic archival             Disbled

Archive destination            /chaitanya/arch/PROD

Oldest online log sequence     206506

Next log sequence to archive   206512

Current log sequence           206512

 

 make sure db is running in spfile

SQL > alter system set log_archive_dest_1='LOCATION=/chaitanya/arch/PROD' scope=spfile;

database altered.

 

SQL >shutdown  immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL > startup mount

ORACLE instance started.

Total System Global Area 5415597568 bytes

Fixed Size                  2170304 bytes

Variable Size             805970240 bytes

Database Buffers         4502926848 bytes

Redo Buffers                3530176 bytes

Database mounted.

 

SQL >alter database archivelog;

 

database altered.

 

SQL >alter database open;

 

database altered.

 

SQL >select name,log_mode from v$database;

 

NAME      LOG_MODE

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

PROD      ARCHIVELOG

 

SQL >archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /chaitanya/arch/PROD

Oldest online log sequence     206506

Next log sequence to archive   206512

Current log sequence           206512

 

To enable flashback we need to set two parameters 

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

 

SQL> alter system set db_recovery_file_dest='/home/oracle/prod';

 

System altered.

 

SQL> alter system set db_recovery_file_dest_size=12g;

 

System altered.

 

SQL> show parameter db_recovery_file

 

NAME TYPE VALUE

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

db_recovery_file_dest string /home/oracle/prod

db_recovery_file_dest_size big integer 10G

 

Turn on Flashback

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

NO

 

SQL> alter database flashback on;

 

Database altered.

 

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

YES

Disable Flashback

 

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

YES

 

SQL> alter database flashback off;

 

Database altered.

 

 

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

NO

 

Note : if you are using 10g or above versions then we need to enable or disable in flashback mode in mount stage

shutdown immediate

startup mount

alter database flashback off;

alter database open

Note : Info on enable or disable flashback technology it may be differ in your environment like production,development,testing and directories etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Oracle Database Security Assessment Tool (DBSAT)

 Oracle Database Security Assessment Tool (DBSAT) 

Introduction:

The oracle Database security assesment tool it is known as DBSAT tool is used to scan the complete database scan and provide report security configuration and vulnerability list

DBSAT has two components

The Collector: Collector is to collect all information from the database by running SQL and OS aginst database

The Reporter : Reporter it will Analyze the database and gives the complete report for the database

Let us Start the Process

Step 1.Download the DBSAT TOOL from oracle support website in category Oracle Database security Asssement tool

Step 2.Copy the DBSAT tool and unzip it

unzip dbsat.zip

Archive:  dbsat.zip

  inflating: dbsat

  inflating: dbsat.bat

  inflating: sat_reporter.py

  inflating: sat_analysis.py

  inflating: sat_collector.sql

  inflating: xlsxwriter/app.py

  inflating: xlsxwriter/chart_area.py

  inflating: xlsxwriter/chart_bar.py

  inflating: xlsxwriter/chart_column.py

  inflating: xlsxwriter/chart_doughnut.py

  inflating: xlsxwriter/chart_line.py

  inflating: xlsxwriter/chart_pie.py

  inflating: xlsxwriter/chart.py

  inflating: xlsxwriter/chart_radar.py

  inflating: xlsxwriter/chart_scatter.py

  inflating: xlsxwriter/chartsheet.py

  inflating: xlsxwriter/chart_stock.py

  inflating: xlsxwriter/comments.py

  inflating: xlsxwriter/compat_collections.py

  inflating: xlsxwriter/compatibility.py

  inflating: xlsxwriter/contenttypes.py

  inflating: xlsxwriter/core.py

  inflating: xlsxwriter/drawing.py

  inflating: xlsxwriter/format.py

  inflating: xlsxwriter/__init__.py

  inflating: xlsxwriter/packager.py

  inflating: xlsxwriter/relationships.py

  inflating: xlsxwriter/shape.py

  inflating: xlsxwriter/sharedstrings.py

  inflating: xlsxwriter/styles.py

  inflating: xlsxwriter/table.py

  inflating: xlsxwriter/theme.py

  inflating: xlsxwriter/utility.py

  inflating: xlsxwriter/vml.py

  inflating: xlsxwriter/workbook.py

  inflating: xlsxwriter/worksheet.py

  inflating: xlsxwriter/xmlwriter.py

  inflating: xlsxwriter/LICENSE.txt

Step 3. Now use the Collect Command before that make sure to set proper ORACLE_HOME , ORACLE_SID and PATH before running this command

             ./dbsat collect {username/password} {DESTINATION_PATH}

 

./dbsat collect system/oracle /export/home/oracle/chaitanya

 

This tool is intended to assist in you in identifying potential

vulnerabilities in your system, but you are solely responsible for

your system and the effect and results of the execution of this tool

(including, without limitation, any damage or data loss). Further,

the output generated by this tool may include potentially sensitive

system configuration data and information that could be used by a

skilled attacker to penetrate your system. You are solely responsible

for ensuring that the output of this tool, including any generated

reports, is handled in accordance with your company's policies.

 

Connecting to the target Oracle database...

 

 

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 25 15:30:03 2020

 

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

 

Last Successful login time: Tue Aug 10 2020 13:16:12 +03:00

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

Database Security Assessment Tool version 1.0.2 (October 2016)

Setup complete.

SQL queries complete.

/oracle/app/oracle/product/12.1.0/dbhome/bin/osdbagrp -r

Usage: /oracle/app/oracle/product/12.1.0/dbhome/bin/osdbagrp -a | -d | -o | -b | -g | -k

Warning: Exit status 256 from OS rule: sysrac_group

OS commands complete.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

DBSAT Collector completed successfully.

 

Calling /oracle/app/oracle/product/12.1.0/dbhome/bin/zip to encrypt chaitanya.json...

 

Enter password:

Verify password:

  adding: chaitanya.json (deflated 86%)

zip completed successfully.

This will generate file called chaitanya.zip

 

Step 4. Generate  the Report

           ./dbsat report {DESTINATION_FILE}

./dbsat  report /export/home/oracle/audit_sec

This tool is intended to assist in you in identifying potential

vulnerabilities in your system, but you are solely responsible for

your system and the effect and results of the execution of this tool

(including, without limitation, any damage or data loss). Further,

the output generated by this tool may include potentially sensitive

system configuration data and information that could be used by a

skilled attacker to penetrate your system. You are solely responsible

for ensuring that the output of this tool, including any generated

reports, is handled in accordance with your company's policies.

 

Archive:  bsstdba.zip

[bsstdba.zip] bsstdba.json password:

  inflating: bsstdba.json

Database Security Assessment Tool version 1.0.2 (October 2016)

DBSAT Reporter ran successfully.

 

Calling /usr/bin/zip to encrypt the generated reports...

 

Enter password:

Verify password:

  adding: chaitanya.txt (deflated 78%)

  adding: chaitanya.html (deflated 84%)

  adding: chaitanya.xlsx (deflated 3%)

zip completed successfully.

 

audit_sec_report.zip file will be generated

Step 5.  The report will  looks like:

While unzipping the file, it will ask for the password, (pass the same which we used while generating the report)

/export/home/oracle# unzip audit_sec_report.zip

Archive:  bsstdba_report.zip

[bsstdba_report.zip] chaitanya.txt password:

  inflating:chaitanya.txt

  inflating: chaitanya.html

  inflating: chaitanya.xlsx

Note: Info on DBSAT tool it may be differ on your environment like production,testing ,development etc and naming conventions and directory structure

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Oracle Dba Online Test (MCQ) Advanced Level ASM

Oracle Database 19c Features

 Oracle Database 19c Features

Introduction

Oracle Database 19c is the long term release of the Oracle Database 12c and 18c family of products,it is available on all platformsWindows,Linux,Solaris,HP/UX and AIX as well as the Oracle cloud. Oracle Database 19c offers customers the best performance,scalibility,reliability, and security for all their operational and analytical workloads

Installation

Rpm based Installation install oracle 19c datbase using RPM method

Simlified image based installation of client as well

Upgrades

Auto Upgrade Utility for oracle Database

Docker Container for oracle 19c

Dryrun mode for Gridsetup in clusterware installation

General

Clear Flashback logs from time to time

Passwords removed from user accounts (default accounts)

Flush Metadata Cache for passwords

Multi-model partitioning with hybrid partioning allowing some partitions in database and some as external partitions even in HDFS

New ALTER SYSTEM  statement clause FLUSH PASSWORDFILE_METADATA_CACHE

Hybrid  Partitioned tables - to integerate internal partitions and external partitions  into a single partition table. partitions to reside in both oracle database segments and in external files and sources

Schema-only accounts -Passwords Removed from oracle database accounts  

Database Performance

SQL Quarantine - using Oracle's Resource manager tool is a great way to make sure SQL statements dont become resource hogs and slow down database performance everyone,if a system asks for more system resorces than the DBA allows ,Resources manager kills it,However in existing versions of oracle database,nothing stops users from executing problematic SQL statements again. In oracle 19C ,Resource manager can automatically quarantine the statements, user try to issue once again it wont be run at all

Automatic Indexing

This new feature puts oarcale automation capabilities to work.if oracle 19c thinks a database table would be benifit from an index,the system will automatically create the index and initially mark it as invisible so it cant be used .oracle 19c will then run SQL statements  from your application to see if the index improves query execution you can control this feature  with DBMS_AUTO_INDEX< a new PL?SQL package that's included in 19c

SQL Statement Diagnosability 

SQL Statement Diagnosability with SQL Advisor repair and SQL Test case for procedures

Automatic Database Diagnostic Monitor(ADDM)

ADDM supports for pluggable Database (PDB'S)

Realtime  Statastics For DML Operations

Oracle database 19c intoduces real time statastics which extend online support to conventional DML statements

Automatic Flashback of Standby Database

in prior versions DBA's wanted touse oracle flashback features to return  aprimary database to previous state,In oracle 19c ,a DBA can put the standby database in MOUNT mode with no managed recovery and then flashback the primary one ,the standby will aslo be reverted,thus keeping it in sync with the primary Statistics Collection on custom frequency automatically From 19c database onwards ,High frequency automatic optimizer statastics collection complements the standard statasticscollection job

DataPump

Oracle data pump test mode for transportable tablespace(TTS)

Oracle data pump allows tablespace to stay read -only during TTS import

Oracle data pump import supports more object store credentials

Oracle data pump ability to exclude ENCRYPTION clause on import-new transform  parameter OMIT_ENCRYPTION_CLAUSE

Oracle data pump support for resource usage limitations_new parameter MAX_DATAPUMP_PARALLEL_PER_JOB

Oracle data pump prevents inadvertent use of protected roles-new ENABLE_SECURE_ROLES parameter is available

Oracle data pump loads partitioned table data one operation-GROUP_PARTITION_TABLE_DATA,  new value for the import DATA_OPTIONS Command line paramaeter

Pluggable Databases

Create Duplicate of an oracle database create duplicatedb command , in DBCA silent mode

Ability to relocate a PDB to another CDB using DBCA in silent mode

Create a PDB by cloning a remote PDB using DBCA in silent mode

ADDM Analysis at PDB level

Data Guard

Replicate Restore points from primary to standby

Dynamically change  fast-start- failover (FSFO) target standby database to another standby database in the target list without disabling FSFO

Re-creation of broker configuration

Propagate restore points from primary to standby site

DML redirect to standby/ADG for read mostly applications

Simplified Dataguard broker parameter configurations

Observe only mode for data guard broker fast-satrt failover (FSFO)

Oracle dataguard multi-instance redo apply works with the in-memory column store

Finer granularity supplemental logging for logical standby databases

New Initialization Parameters in Oracle Database 19c 

"-optimizer_gather_stats_on_conventional_dml" and " _optimizer_use_stats_on_conventional_dml" which are true by default

-optimer_stats_on_conventional_dml_sample_rate(at 100%)

DATA_GUARD_MAX_IO_TIME

DATA_GUARD_MAX_LONGIO_TIME

MAX_DATAPUMP_JOBS_PER_PDB

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

 

Flashback technology Recover a Dropped User in Oracle

Flashback technology  Recover a dropped user in oracle

Introduction

Using Flashback Technology  we can restore the dropped user in oracle we will flashback the database to past when the user is available at the time of dropped before,

Then take the export dump of the schema and restore the database to same current state once database is up we can import the dump file  

Prerequisites

1. Database must be Archivelog mode

2.Flash back must be enable for the database

3.All the flashback log and  Archive log should be available from the time the user is dropped 

Let us start the process

1.Make sure flashback and archive mode is enable.

SQL> select flashback_on,log_mode from v$database;

 

FLASHBACK_ON       LOG_MODE

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

YES                                 ARCHIVELOG

2. lets drop the user and test the scenarios

04:47:15 SQL> select table_name from Chaitu_table where owner='CHAITANYA';

 

TABLE_NAME

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

ACCTTABLE1

MASTERTABLE2

 

 

04:47:33 SQL> drop user CHAITANYA cascade;

 

User dropped.

3.Flashback the database past when the user was available at that time

04:52:15 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

04:52:50 SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 1.1107E+10 bytes

Fixed Size                  7644464 bytes

Variable Size            9294584528 bytes

Database Buffers         1711276032 bytes

Redo Buffers               93011968 bytes

Database mounted.

 

04:53:08 SQL> flashback database to timestamp to_date('20-AUG-2020 04:47:33','DD-MON-YYYY HH24:MI:SS');

 

Flashback complete.

4. Open the database in readonly mode

04:55:13 SQL> ALTER DATABASE OPEN READ ONLY;

 

Database altered.

 

04:55:31 SQL>  select table_name from chaitu_tables where owner='CHAITANYA';

 

TABLE_NAME

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

ACCTTABLE1

MASTERTABLE2

we can see the tables are available now

5. Take export backup of the schema CHAITANYA

# exp owner=CHAITANYA file=chaitanya.dmp

 

Export: Release 12.1.0.2.0 - Production on Tue Aug 20 05:17:45 2020

 

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

 

 

Username: / as sysdba

 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

 

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user CHAITANYA

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user CHAITANYA

About to export CHAITANYA's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export CHAITANYA's tables via Conventional Path ...

. . exporting table                           ACCTTABLE1     75341 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table                          MASTERTABLE2        44 rows exported

EXP-00091: Exporting questionable statistics.

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully with warnings.

6. Now restore the database to current state

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 1.1107E+10 bytes

Fixed Size                  7644464 bytes

Variable Size            9294584528 bytes

Database Buffers         1711276032 bytes

Redo Buffers               93011968 bytes

Database mounted.

 

SQL> recover database;

Media recovery complete.

 

SQL> alter database open;

 

Database altered.

7. create the empty user and import the dumpfile

SQL> create user chaitanya identified by chaitanya;

 

User created.

 

SQL> grant connect,resource to chaitanya;

 

Grant succeeded.

 

# imp file=chaitanya.dmp fromuser=CHAITANYA TOUSER=CHAITANYA

 

Import: Release 12.1.0.2.0 - Production on Tue Aug 20 05:23:59 2020

 

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

 

Username: / as sysdba

 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

Export file created by EXPORT:V12.01.00 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing DBACLASS's objects into DBACLASS

. . importing table                        " ACCTTABLE1 "      75341 rows  imported

. . importing table                        "MASTERTABLE2"          44 rows imported

 we can restore the schema user chaitanya by using flashback technology

Note : Info on Flashback technology it may be differ in your environment like production,testing ,development and naming conventions 

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME 

 

Recycle Bin in Oracle Database

 Recycle Bin in Oracle Database

Introduction

In windows  have a recycle bin to all deleted files will be store like wise in oracle database also 

provided recycle bin which keeps all the dropped objects.

When we drop a table (DROP TABLE TABLE_NAME) in the database , The tables will logically be

 removed but it still exists in the same tablespace 

but with a prefix BIN$$ .And it will not release the space also

Note : The Recycle bin which will not work sys owned objects it will worked on user objects only

If we drop a table using purge command, tables willbe removed completely (even from recycle bin also)

How to check the recycle bin is on or off

1.SQL> show parameter recyclebin;

 

NAME                                 TYPE        VALUE

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

recyclebin                           string      on

 

SQL> select name,value from v$parameter where name like '%recyclebin%';

 

NAME         VALUE

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

recyclebin   on

2.Drop a table and check the table is there in the recycle bin or not  

SQL> drop table chaitanya.CHAITUTABLE;

 

Table purged.

 

SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,DROPTIME,CAN_UNDROP from dba_recyclebin where ORIGINAL_NAME='CHAITUTABLE';

 

OWNER              OBJECT_NAME                                   ORIGINAL_NAME      DROPTIME            CAN

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

CHAITANYA           BIN$fxhnqWVcPLTgVAAQ4B8y7Q==$0                CHAITUTABLE         2020-01-10:12:45:03 YES

Now the table is in recycle bin we can recover the table if required

3. For purging the table for recyclebin

  in order to remove the table from recyclebin also

SQL> purge table chaitanya.CHAITUTABLE;

 

Table purged.

 

SQL>  select owner,OBJECT_NAME,ORIGINAL_NAME,DROPTIME,CAN_UNDROP from dba_recyclebin where ORIGINAL_NAME='CHAITUTABLE';

 

no rows selected

4. To purge complete recyclebin

SQL> select count(*) from dba_recyclebin;

 

  COUNT(*)

----------

       102

 

SQL> purge recyclebin;

 

Recyclebin purged.

 

SQL>  select count(*) from dba_recyclebin;

 

  COUNT(*)

----------

         0

5. To drop a table without keeping in recyclebin

SQL> select count(*) from CHAITANYA.chaitusample;

 

  COUNT(*)

----------

     82269

 

SQL> drop table CHAITANYA.CHAITUSAMPLE purge;

 

Table dropped.

 

SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,DROPTIME,CAN_UNDROP from dba_recyclebin where ORIGINAL_NAME='CHAITUSAMPLE';

 

no rows selected

Note : Info on Recycle bin in oracle it may be differ in your encironment like production, testing,development and naming conventions 

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME