When we see this error, the first thing is to control the flash recovery area In the event that indeed the FRA was full, you can delete old archive logs from the filesystem then on rman use the following run { crosscheck archivelog all; delete noprompt expired archivelog all; } The CROSSCHECK command is used…
Leggi tutto
The recycle bin is a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. Enabling and Disabling the Recycle Bin ALTER SESSION SET recyclebin = OFF; ALTER SYSTEM SET recyclebin = OFF; ALTER SESSION…
Leggi tutto
With the previous Oracle database release, long characters strings of more then 4000 bytes had to be handled using CLOB or LONG data types. Starting from Oracle 12c was introduced a new system parameter: MAX_STRING_SIZE. This parameter allows string data types to be much larger when the parameter is changed from its default value STANDARD…
Leggi tutto
Starting from release 12C there is a new option to pass in silent mode encryption password . The ENCRYPTION_PWD_PROMPT parameter enables encryption without requiring the password to be entered as a command line parameter. The user is prompted for the password during export utility. expdp … ENCRYPTION_PWD_PROMPT=Y Password: <user is asked for encryption password> In…
Leggi tutto
“Scope” is a parameter used for the alter system command when you are changing any initialization parameter of an spfile. It is vital to understand how to use this parameter to achieve the desired effect. The SCOPE parameter can take this three values MEMORY, SPFILE and BOTH. Alter system set PARAMETER=VALUE SCOPE=SPFILE; If you want…
Leggi tutto
The redo logs record all changes that occurred to the data. Every DB has at least two redo log files because Oracle writes these files in a circular fashion: when a redo log file is full then Oracle writes in the next, when the last redo log file is full then Oracle restarts from the…
Leggi tutto
Schema changes, reorganizations or other administrative tasks are difficult to make while users area conducting live transactions in the database. You have to perform these activity during a schedulated maintenance or you have to shut down the database and reopen in “restricted mode”. For obviate this problem , “quescing database” gives the possibility to put…
Leggi tutto
With previous releases PMON (Process Monitor) was responsible for registering database services to the Listener. Starting from Oracle 12C there is a change in architecture with respect to the service registration in Oracle Database Release 12c. Oracle has introduced a new mandatory background process dedicated for “database service registration to the Listener” and named LREG (Listener…
Leggi tutto
The APPROX_COUNT_DISTINCT function was added, but not documented, in Oracle 11g to improve the speed of calculating the number of distinct values when gathering statistics using the DBMS_STATS package. Oracle database 12c (12.1.0.2) now includes the function in the documentation, so we are free to use it in our applications as a supported SQL function.…
Leggi tutto
In Oracle 12c when we create a table we can populate a column automatically via a system generated sequence by using the GENERATED AS ++ clause in the CREATE TABLE statement. We can use GENERATED AS IDENTITY with the ALWAYS, DEFAULT or DEFAULT ON NULL keywords and that will affect the way or when the…
Leggi tutto
Commenti recenti