DBA Blogs
Pluggable database save state -12C
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL2 READ WRITE
PDBORCL1 READ WRITE
SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
no rows selected
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL2 READ WRITE
PDBORCL1 READ WRITE
SQL> ALTER PLUGGABLE DATABASE pdborcl1 SAVE STATE;
Pluggable database altered.
SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------
PDBORCL1 orcl OPEN
SQL> SHUTDOWN IMMEDIATE;
STARTUP;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.
Total System Global Area 788529152 bytes
Fixed Size 2929352 bytes
Variable Size 541068600 bytes
Database Buffers 239075328 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL MOUNTED
PDBORCL2 MOUNTED
PDBORCL1 READ WRITE
SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------
PDBORCL1 orcl OPEN
***********************************************
DISCARD PDB SAVED STATE
***********************************************
SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------
PDBORCL1 orcl OPEN
SQL> ALTER PLUGGABLE DATABASE pdborcl1 discard state;
Pluggable database altered.
SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
no rows selected
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL2 READ WRITE
PDBORCL1 READ WRITE
SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
no rows selected
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL2 READ WRITE
PDBORCL1 READ WRITE
SQL> ALTER PLUGGABLE DATABASE pdborcl1 SAVE STATE;
Pluggable database altered.
SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------
PDBORCL1 orcl OPEN
SQL> SHUTDOWN IMMEDIATE;
STARTUP;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.
Total System Global Area 788529152 bytes
Fixed Size 2929352 bytes
Variable Size 541068600 bytes
Database Buffers 239075328 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL MOUNTED
PDBORCL2 MOUNTED
PDBORCL1 READ WRITE
SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------
PDBORCL1 orcl OPEN
***********************************************
DISCARD PDB SAVED STATE
***********************************************
SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------
PDBORCL1 orcl OPEN
SQL> ALTER PLUGGABLE DATABASE pdborcl1 discard state;
Pluggable database altered.
SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
no rows selected
Categories: DBA Blogs
how to create nested cursor in plsql
select * from empinfo;
"ID" "FNAME" "LNAME" "DEPTNO"
1 "arv" "red" 100
2 "sam" "kum" 200
3 "tom" "com" 100
4 "Hef" "Kom" 200
5 "fam" "ily" 300
select * from deptinfo;
"ID" "DEPTNO" "DEPTNAME" "MGR"
1 100 "HR" "Bill"
2 200 "IT" "Greg"
3 300 "SALES" "Phil"
-- sqlcode starts here
declare
CURSOR CUR1 is select * from empinfo ;
REC1 empinfo%rowtype;
CURSOR CUR2(DID number) is select * from deptinfo where deptno=DID;
REC2 deptinfo%rowtype;
begin
open CUR1 ;
loop
FETCH CUR1 into REC1;
EXIT WHEN CUR1%NOTFOUND;
OPEN CUR2(REC1.deptno);
loop
FETCH CUR2 into REC2;
EXIT WHEN CUR2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('REC1.id: '||REC1.ID||' belongs to dept ID : '||REC2.DEPTNO||' DEPT :'||REC2.DEPTNAME);
END LOOP;
CLOSE CUR2;
end loop;
close CUR1;
end;
Categories: DBA Blogs
Partitioning -- 10 : Virtual Column Based Partitioning
Oracle 11g supports specifying a Virtual Column as the Partition Key.
A Virtual Column is a column where the value is derived on the basis of an expression on other columns or sql/plsql functions. The actual value is not stored in the block holding the row but is computed when the row is retrieved.
For example :
The MY_SALES_TABLE can be List, Range or Hash Partitioned on SALE_YEAR.
Thus, the SALE_YEAR value is not actually stored on disk, yet each partition has rows based on the SALE_YEAR (derived) value.
A Virtual Column is a column where the value is derived on the basis of an expression on other columns or sql/plsql functions. The actual value is not stored in the block holding the row but is computed when the row is retrieved.
For example :
create table my_sales_table
(invoice_id number primary key,
invoice_date date,
sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
customer_id number,
sale_value number
)
/
insert into my_sales_table
(invoice_id, invoice_date, customer_id, sale_value)
values
(1,sysdate,100,10200)
/
select invoice_id, invoice_date, sale_year
from my_sales_table
/
INVOICE_ID INVOICE_DATE SALE_YEAR
1 28-NOV-18 2018
The MY_SALES_TABLE can be List, Range or Hash Partitioned on SALE_YEAR.
drop table my_sales_table;
create table my_sales_table
(invoice_id number primary key,
invoice_date date,
sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
customer_id number,
sale_value number
)
partition by list(sale_year)
(partition p_2018 values (2018),
partition p_2019 values (2019),
partition p_2020 values (2020)
)
/
insert into my_sales_table
(invoice_id, invoice_date, customer_id, sale_value)
values
(1,sysdate,100,10200)
/
select invoice_date, sale_year from my_sales_table partition (p_2018)
/
INVOICE_DATE SALE_YEAR
28-NOV-18 2018
Thus, the SALE_YEAR value is not actually stored on disk, yet each partition has rows based on the SALE_YEAR (derived) value.
Categories: DBA Blogs
How to limit the resources at pluggable level in database 12cR2
Some bad performance pdbs may result in disaster for the whole db machine, in order to avoid this situation we need to limit the pdb resource usage!
There's tens of pdbs in our 12cR2, each one requires DIFFERENT resources, we have to limit them with...
Categories: DBA Blogs
oracle 11g place limits on memory to session/user
Hello !
Here is my question : Is it possible to allow a minmum amount of memory to a specific session or a user on Oracle 11g? If it is, could anyone, please, explain it to me ? :)
My fundamental need is as follow :
I have two applications th...
Categories: DBA Blogs
expressions on pivot columns
On LiveSQL there is a tutorial, 'Converting Rows to Columns and Back Again: Databases for Developers'
https://livesql.oracle.com/apex/livesql/file/tutorial_GNZ3LQPJ0K6RTD1NEEPNRQT0R.html
It guides me through on how to pivot rows into columns an...
Categories: DBA Blogs
ORA-12154: TNS:could not resolve the connect identifier specified for ODBC
Greetings,
I received the following error while trying to connect to Oracle instantclient 10.1 in MS Access 2016:
ORA-12154: TNS:could not resolve the connect identifier specified [#12154) [Microsoft][ODBC Driver Manager] SQLSetConnectAttr fail...
Categories: DBA Blogs
ORA-12560 ERROR - do I have all the services I need ?
Hello,
I am using a Macbook and I downloaded the Parallels virtual machine to get Windows 10 in order to download SQL Plus,
I have done all of the minimum requirements and installed SQL Plus, but when it comes to entering my username and password...
Categories: DBA Blogs
Database Link across versions
Can a database link created on an Oracle 12 database access tables hosted on an Oracle 11 database?
We have an existing application Oracle Argus that we are upgrading to Argus 8.1.2.1 which will run on Oracle 12 Exadata server. We have an existing...
Categories: DBA Blogs
Insert datetime values from .NET apps
Hello, Team.
A .NET app is being developed by our team. We are using Oracle database 18.3.0.
In order to insert date and time in a date column developers use the following code:
<b>TO_DATE(TO_CHAR(:DATE_COLUMN,'DD/MM/RRRR' HH:MI:SS AM), 'DD/...
Categories: DBA Blogs
Unix Strings command and data not encrypted
Hello TOM,
I have a problem with the Unix command named STRINGS. If I do not use encryption on my datas, I can read my string datas via Unix, even if these datas were truncated under Oracle.
And dtranger, I have their history too...
Fir...
Categories: DBA Blogs
Table storage parameters
I've always created tables using the defaults for storage parameters.
<code>
create table blah (
a number;
b varchar2(100)
);
</code>
however I recently worked with an organization where the following storage parameters were always spe...
Categories: DBA Blogs
Default Value From A Sequence: Sequence Updates On Provided Value Entries With INSERT Through LOOP But Not on Individual INSERTs
I have two samples of code below that use the same table and sequence definitions.
The first one inserts using individual insert statements, with the first three inserts giving an override value to the sequenced column and the final insert relyi...
Categories: DBA Blogs
Compare all table content from one schema to another
Hi,
I need to compare full schema table contents with another schema. Both will have same tables, with exact the same DDL, just some records will be different in few columns.
Is there any way to execute a script or any way to do it in SQL Deve...
Categories: DBA Blogs
parsing recusirvely "#" from strings without PL/SQL procedure
Dear Tom,
First of all thanks for the site, the tips... always useful... I faced recently an sql issue and i designed a beginning of answears but i think it should be optimized.
I have a table containing posts from a social network platforms. Thos...
Categories: DBA Blogs
Sending mail using utl_mail
Dear Team,
I tried to install UTL_MAIL using the below command
sqlplus sys/<pwd>
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
it runs successfully and created the package and synonym but when i tri...
Categories: DBA Blogs
LOB compression
Hello,
I have a table with BLOBs and they are actually character logs generated by fax transmission software. They are about 4k-16k big and pretty redundant, they compress with zlib to just about 1k-2k. And I want to store them compressed in DB. N...
Categories: DBA Blogs
return multiple address to mail_pkg
Hi Tom. I'm trying to get your package mail_pkg (found it on this site) to accept multiple values for the p_to (recipient list).
begin
mail_pkg.send
( p_sender_email => 'me@acme.com',
p_from => 'Oracle Database Account <me@acme.co...
Categories: DBA Blogs
Merge vs Update
MERGE INTO pkt_prty_fncl_st ppst
USING tmp_pkt_prty_fstate_clnb_stgg tmp
on (tmp.fncl_ast_id = ppst.fncl_ast_id
AND tmp.prty_id = ppst.prty_id
AND tmp.pkt_pcsg_st_cd = ppst.pkt_pcsg_st_cd
AN...
Categories: DBA Blogs
Select first value if exists, otherwise select another value
Hello
I have a table like this
<code>ID NTYPE
1 0
2 0
3 1
4 2</code>
I need a select to get all IDs according of a list of NTYPE (1 to N), but if any of the NTYPE list does not exist then get where NTYPE = 0..
...
Categories: DBA Blogs
Pages
