Creating public database link with/without username

Create a public database link without specifying a username CREATE PUBLIC DATABASE LINK LINK_TEST01 USING ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.instabc.com)(PORT=1521))(CONNECT_DATA= (SERVER=dedicated)(SERVICE_NAME=inttest)))’; Create a public database link while specifying a username to use CREATE PUBLIC DATABASE LINK LINK_TEST01 CONNECT TO TESTUSER IDENTIFIED BY TESTPASS USING ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.instabc.com)(PORT=1521))(CONNECT_DATA= (SERVER=dedicated)(SERVICE_NAME=inttest)))’; When creating a database link you have the option of using a username […]

Oracle Database – Enterprise or Standard Edition

Here are some questions to ask yourself, your team or your company: How large is your company?What is the budget?How many databases on the server?How many cpus/cores on the server?DEV, TEST, QA or PROD?Does your company allow 3rd party applications to monitor your database? These are questions that need to be asked.  Oracle Enterprise Edition […]

Refresh Physical Standby Database from Primary

Simple method to refresh your physical standby database if apply lag is lengthy 1) Stop dataguard broker for the standby database dgmgrl connect sys/pswd disable database ‘inst_ps’ 2) Stop redo application using sqlplus ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SHUTDOWN IMMEDIATE; 3) Manually copy the logs from the primary to the standby filesystem *** […]

RMAN – ALLOCATE CHANNEL FOR MAINTENANCE (RMAN-01009)

Performing maintenance using RMAN is part of any dba’s admin duties.  Issuing the commands in RMAN manually is not as common as it used to be.  Most dba’s have scripts that perform the normal RMAN tasks.  Because of this automation dba’s tend to forget the actual commands or structure of the commands. This tutorial is […]

Difference between DDL and DML

DDL statements (Data Definition Language) are used to define the structure of a table or database as well as the schema of the database. DML statements (Data Manipulation Language) are used to manage the data of the objects within a schema, table or database. Below are some examples of DDL and DML statements: DDL CREATE […]

How to get sql_id and sql_text for a concurrent request

A colleague asked for a quick video and text example of how to get sql_id and sql_text for a concurrent program that is running. The text is below: col action format a20 col module form a30 select sid,sql_id,action,module from v$session where action like ‘%Conc%’; select sql_text from v$sqlarea where sql_id=’SQL_ID’; I hope this is helpful. […]

Querying indexes on table and build script

Often times we are asked to rebuild indexes on tables, but are unaware of what all indexes are on the table. We can query dba_ind_columns to get indexes  on the columns or for general indexes against the table we can query dba_indexes. A general query would look like this: select owner,table_name,index_name from dba_indexes where table […]

How to view database parameters

I have started a youtube channel which I will slowly be adding new video content too. You can click on the video below.  Here is the text that you can use as well. sqlplus ‘/as sysdba’show parameter processes; exit; sqlplus apps/$MYPASS desc v$parameter col name format a20 col value format a30 select name, value from […]

Creating ‘LIKE’ users in the Oracle Database

This post I’m providing allows you to create a new database user modeled after an existing database user. We recently hired numerous dbas and wanted to provide the same access as existing dbas here. Using this script is permitted. As with any script on my blog or others please take caution. This has been verified […]