DBA Blogs

Determining current database name

Tom Kyte - Fri, 2016-07-22 14:46
Which (if either) is preferable for determining the current database name: ORA_DATABASE_NAME or sys_context('USERENV', 'DB_NAME') ? Thanx, D
Categories: DBA Blogs

Inserting a decimal which has no decimal into the table with a decimal point

Tom Kyte - Fri, 2016-07-22 14:46
Hi Tom, I want to insert a decimal number which has no decimal point into a table with a decimal point. Is there a predefined function or a way to do this? Ex: (46926831654) should be inserted as (469268.31654) I dont want to use the string...
Categories: DBA Blogs

executing stored procedures after context change

Tom Kyte - Fri, 2016-07-22 14:46
Hi, I need to execute a stored procedure that exists in many schema, on demand, in any given schema. I am trying to change the current_schema then execute the procedure (GS_LOAD_FDB_PKG is the package in each of our schemas and gst_disease_dxid_u...
Categories: DBA Blogs

RMAN and Archivelog

Tom Kyte - Fri, 2016-07-22 14:46
Hi, Maybe this question was already answered somewhere but there so many question that I can not find the solution on this site. It is rather a simple one (I hope not a stupid one). Strictly speaking, when I make a "backup database" with Rman, do...
Categories: DBA Blogs

Restricting datafile addition to a filesystem (ASM)

Tom Kyte - Fri, 2016-07-22 14:46
Hi Tom The normal method of adding datafiles to a tablespace is to login to the database as "sqlplus / as sysdba" and then executing the "alter tablespace add datafile.." command. Is there a way to prevent / prompt the DBAs from accidentally adding ...
Categories: DBA Blogs

User defined Year-Month data type

Tom Kyte - Fri, 2016-07-22 14:46
Hi Answers Team. I keep seeing my maintenance / design / development teams abusing dates everywhere. One example is the use of YYYYMM as a way to extract a month from some piece of data what starts out as a date. 1. database design will cre...
Categories: DBA Blogs

Links for 2016-07-21 [del.icio.us]

Categories: DBA Blogs

Selectively purge the SQLAREA... terrible idea?

Tom Kyte - Thu, 2016-07-21 20:26
Hi "Tom"- We have an outside vendor's product hitting our database and never using bind variables. Every statement is unique. We can't change it, we're stuck with it. This outside program is a very high volume. These constantly unique statem...
Categories: DBA Blogs

parallel statement queueing - how to synchronize sessions?

Tom Kyte - Thu, 2016-07-21 20:26
Hi Tom-Team, I want to process statements in parallel by setting "parallel_degree_policy=auto". They are a mixture of DML and DDL statements. In this context, the order in which statements execute, is undefined. I know that I can amend that (parti...
Categories: DBA Blogs

Identifying ASM "orphans"

Tom Kyte - Thu, 2016-07-21 20:26
We've been migrating several DBs from their old Oracle 10G platforms to our new Oracle SuperClusters (SC). The older DBs were flatfile based, but the new SC-based DBs are using ASM. Several of the migrations initially failed, and some of the older DB...
Categories: DBA Blogs

PMON

Tom Kyte - Thu, 2016-07-21 20:26
what is PMON?
Categories: DBA Blogs

DBMS_SCHEDULER jobs - How Job_Priority attribute works?

Tom Kyte - Thu, 2016-07-21 20:26
Hi, Request your help to understand working of Job_Priority attribute that can be set for jobs created via DBMS_SCHEDULER. From my tests it seems that the attribute behavior is somehow linked to the number of jobs scheduled. However, looks like...
Categories: DBA Blogs

database corrupted - ORA-09925: Unable to create audit trail file

Tom Kyte - Thu, 2016-07-21 20:26
Actually when my database is running at that time I change my parameter file.after changing I shutdown my database.And again I tried to connect to my database as sysdba. But I cannnot connect to that database.It shows some errors. Then what is the s...
Categories: DBA Blogs

Design question

Tom Kyte - Thu, 2016-07-21 20:26
Hello AskTom Team, Hope you could help me with this. Business case: 1) We have system of records which we provide to customers. We refresh this data on demand (could be push or pull) on customer's on-premise instance 2) Cusotmer can overrid...
Categories: DBA Blogs

Implicit vs explicit cursors

Tom Kyte - Thu, 2016-07-21 20:26
Hi sir how are you....here i got a small doubt.i hope u can reply me.The doubt was....select * from tb...when i was select the data it gave me total records(100),so in this case what we will call it.I mean it is an implicit cursor or an explicit curs...
Categories: DBA Blogs

TRIM function in 12c

Tom Kyte - Thu, 2016-07-21 20:26
Hello, Tom! I've a lack of undestanding of 'trim' fuction's IN parameters. It's not explicitly said in the docs, that only a single character can be used as trim_character. However, an attempt to use a syntax like this: SELECT TRIM(BOTH 'A...
Categories: DBA Blogs

Using special characters in ORACLE DB passwords

Learn DB Concepts with me... - Thu, 2016-07-21 11:37

Using special characters in ORACLE DB passwords  
Using special characters in ORACLE DB passwords. Most times we restrict our selves from using complicated special characters as we think that we can't use them in passwords, but in oracle DB world we are allowed to use most special characters as passwords. See this simple example to see how to use special char's in passwords and login.




To log into DB using cmdline we need to use single quote ''. else oracle some times doesn't recognize the password. See below example :

I created a user with password using special characters.

SQL> alter user C##atest identified by "atest113..";

User C##ATEST altered.

[oracle@Linux03 admin]$ sqlplus C##atest/atest113..

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:39:56 2016

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

ERROR:
ORA-01017: invalid username/password; logon denied


Solution to this is to use single quote:

[oracle@Linux03 admin]$ sqlplus C##atest/"atest113$$"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:41:44 2016

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C

press ctrl+c to escape from this line


[oracle@Linux03 admin]$ sqlplus C##atest/'atest113$$&&'

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:43:20 2016

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

Last Successful login time: Wed Jul 20 2016 16:42:54 -05: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

SQL> show user
USER is "C##ATEST"

SQL> exit
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
[oracle@Linux03 admin]$ sqlplus C##atest/atest113$$&&

Categories: DBA Blogs

Keep big integers in Oracle RDBMS

Tom Kyte - Thu, 2016-07-21 02:06
Oracle has several datatypes that can keep numbers. LONGINTEGER can keep the biggest whole number - number in the range of (-2**63) to (2**63)-1 I need to keep bigger numbers so I can't use it. Keeping the numbers in decimals may affect precision. ...
Categories: DBA Blogs

Unable to open file even though given correct file path wile reading certificate from wallet

Tom Kyte - Thu, 2016-07-21 02:06
Hi Tom , We have requirement to communicate payment gateway through https from plsql. so we used SSL through wallets. But in 11.2.0.1.0 we are unable to read file from wallet even though correct file path provided. I gone through below link al...
Categories: DBA Blogs

Life cycle of a query

Tom Kyte - Thu, 2016-07-21 02:06
Can you please explain the full life cycle of a query like "select * from employees" ..Parsing and memory utilization..?
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs