To return a list of all records where the decimal place is greater than 2, use the code below.
You can replace the 2 to any number of the desired decimal place.

```sql
SELECT * FROM TABLE_A
WHERE COL_A != ROUND (COL_A, 2);
```

Query to get record where a column is greater than 2 decimal place

### Query to check if the database is CDB or non-CDB

Suppose you want to know if the target database is CDB (container database ) or Non-CDB ( Normal Non-container database). You can log in to the DB Box and source the Oracle DB and then find using the below query

```
sqlplus / as sysdba
SELECT NAME, CDB, CON_ID FROM V$DATABASE;

NAME CDB CON_ID
--------------------------- --------- ----------
CDB01 YES 0
```

### how to check container database in oracle 12c

Query to check containers in the CDB database. Each PDB and root is a container

```
SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- -------------------- ------------------------------ ----------
1 CDB$ROOT READ WRITE 0
2 PDB$SEED READ ONLY 891289600
3 SEED READ WRITE 1248961280
4 TEST READ WRITE  1248961280
5 TEST2 READ WRITE 1248961280
```

If you are connected to a PDB, it will show that container only

```
SQL> alter session set container=PDB$SEED;
SQL> select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- -------------------- ------------------------------ ----------
2 PDB$SEED READ ONLY 891289600
```

### Query to check PDB in the CDB database.

Connected to root container

```
SQL> alter session set container=CDB$ROOT;

Session altered.

select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs;

CON_ID DBID NAME OPEN_MODE
---------- ---------- -------------------- ------------------------------
2 5604597646 PDB$SEED READ ONLY
3 823307170 SEED READ WRITE
4 458722011 TEST READ WRITE

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SEED READ WRITE NO
4 TEST READ WRITE NO
```

Connected to a particular PDB

```
SQL> alter session set container=SEED;

Session altered.

SQL> select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs
2 ;

CON_ID DBID NAME OPEN_MODE
---------- ---------- -------------------- ------------------------------
3 823307170 SEED READ WRITE

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SEED READ WRITE NO
SQL>
```

### Startup and shutdown of PDB

Connected to root container

```
alter pluggable database test1_tech close;

alter pluggable database test1_tech close immediate;

alter pluggable database test1_tech open;

alter pluggable database test1_tech open read only ;

alter pluggable database test1_tech open force;
```

If the container is set, we can use the below command

```
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN READ ONLY;
ALTER PLUGGABLE DATABASE OPEN FORCE;

ALTER PLUGGABLE DATABASE NOLOGGING;

ALTER PLUGGABLE DATABASE ENABLE FORCE LOGGING;

STARTUP OPEN
STARTUP OPEN READ ONLY
STARTUP RESTRICT OPEN READ ONLY
```

### How to add service in CDB

```
srvctl add service -db CDB01 -service TEST_NEW -pdb TEST
srvctl modify service -db CDB01 -service TEST_NEW -pdb TEST
srvctl remove service -db CDB01 -service TEST_NEW

BEGIN
DBMS_SERVICE.CREATE_SERVICE(
service_name => 'TEST_NEW',
network_name => 'TEST_NEW.com');
END;
/

BEGIN
DBMS_SERVICE.DELETE_SERVICE(
service_name => 'TEST_NEW');
END;
/

SQL> select name, con_id from v$active_services order by 1;

NAME CON_ID
-------------------- ----------
CDB01 1
CDB01XDB 1
SYS$BACKGROUND 1
SYS$USERS 1
TEST 4
seed 3

6 rows selected.
```

### Show command with 12c database

```
show con_name

CON_NAME
------------------------------
CDB$ROOT

show con_id

CON_ID
------------------------------
1
```

### How to close and open all the PDB in a single command

```
alter pluggable database all close immediate;

alter pluggable database all open;
```

### How to drop the pluggable database

```
drop pluggable database pdb3_test including datafiles;
```

### Container storage

With the root container, we have cdb_tablespaces,cdb_data_files shows the tablespace and data files of all the containers in the CDB, and dba_tablespaces,dba_data_files will show the root tablespace and datafiles only

```
alter session set container=CDB$ROOT;

SQL> select tablespace_name, con_id from cdb_tablespaces;

TABLESPACE_NAME CON_ID
------------------------------------------------------------------------------------------ ----------
SYSTEM 1
SYSAUX 1
UNDOTBS1 1
TEMP 1
USERS 1

SYSTEM 2
SYSAUX 2

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------------------------------------------------------------------ ----------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
```

When connected to a PDB, cdb_tablespaces,cdb_data_files shows the same information as dba_tablespaces,dba_data_files for the PDB

### How to rename the pluggable database

Here is the sql command to rename the pluggable database

```sql
alter pluggable database TEST rename global_name to TEST1;
```

### How to check for the Local Undo feature in 12.2

Here is the query to check for the Local Undo feature in Oracle database 12.2. This feature enables local undo tablespace for each of the pdb’s

```
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED TRUE
```

### Viewing the History of PDBs

```
COLUMN DB_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN PDB_NAME FORMAT A15
COLUMN OPERATION FORMAT A16
COLUMN OP_TIMESTAMP FORMAT A10
COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;
```

I hope you like the compilation of these useful Oracle 12c pluggable database commands. Please provide feedback on it and tell me what else can be added to this list

Useful Oracle 12c Pluggable database commands

fdgfsfasadfdgffsasassdgfvcvc c c c

# bold
_GHJS_

Lucky football

To recover deleted data from an Oracle database after using the `delete from table` command to remove the data, you have to take note of the timestamp (date and time) when the data was deleted.

The timestamp is used as a flashpoint to recover back the deleted data.

Modify the below script to see/retrieve the deleted data.

```sql
select * from MY_TABLE 
as of timestamp to_timestamp('04-MAY-2017 13:30:00', 'DD-MON-YYYY HH24:MI:SS') 
where 'some condition';
```

In the above script, the time specified in the `to_timestamp` would return the dataset is as at the specified time (before the data was deleted). You can also use the `where` clause to filter the result set.

The timestamp above is in 24HRS format, for a 12HRS format use the below timestamp format.

```sql
select * from MY_TABLE 
as of timestamp to_timestamp('04-MAY-2017 12:30:00', 'DD-MON-YYYY HH:MI:SS') 
where 'some condition';
```

Happy coding!!!

sql

Posts

Query to get record where a column is greater than 2 decimal place

Useful Oracle 12c Pluggable database commands

we

Lucky football

How to recover deleted Data from an Oracle database Table