DBA Blogs

Reading rows ahead of a particular value

Tom Kyte - Mon, 2017-06-05 03:06
Hello, I have data in a single column of a table. create table t (ctext varchar2(255)); insert into t values ('2017-06-04 17:17-4qxzmh15zwv35-GSAAPP'); insert into t values (' PUBLICATION_CELLS '); insert into t values ('2017-06-04 1...
Categories: DBA Blogs

cursor within cursor using forall is possible?

Tom Kyte - Mon, 2017-06-05 03:06
HI TOM, GOOD DAY! cursor within cursor using forall is possible? need to fetch records from all_objects in first cursor and then need to delete records using object name in where clause in FORALL
Categories: DBA Blogs

Convert a regular table to system partitioned table ....

Tom Kyte - Mon, 2017-06-05 03:06
Hi, Is there a way to convert a regular table to a system partitioned table? Since there is a limitation to system partitioning wherein we cannot use CTAS, How can we replicate a regular table to system partitioned table? Regards, Manu
Categories: DBA Blogs

Migration of HTF packages from Oracle 8i to Oracle 12c

Tom Kyte - Fri, 2017-06-02 20:26
Hi All, Currently the application exposes HTML pages to users. This is done using HTF packages in Oracle 8.1. The setup is in such away that the application web server(apache http) , DB all reside in the same server host. The mod_plsql is configur...
Categories: DBA Blogs

High SQL ordered by Parse Calls

Tom Kyte - Fri, 2017-06-02 20:26
Database has 22 schema which are similar. Some of the queries in PL.SQL code remains active and in AWR we Observe that there is a very very high Parse to execution ratio for these queries (Example :Parse calls 92,899,371, Executions 3,875 in a p...
Categories: DBA Blogs

Join tables on LIKE condition very slow

Tom Kyte - Fri, 2017-06-02 20:26
Hi Oracle Gurus! I have two tables: 1) BIG: accounts, 200+ mln rows, contains account numbers like these: 123467896546 698746516874 685497964116 748794251631 ... 2) small: patterns, 2000 rows, contains account patterns like these: 12__46...
Categories: DBA Blogs

Pushed out new version of PythonDBAGraphs

Bobby Durrett's DBA Blog - Fri, 2017-06-02 17:03

I pushed out a new version of PythonDBAGraphs. I got a new laptop at work so I am setting it up with the software that I need to do my job. I decided that instead of reinstalling Enthought Canopy I would go with the current version of Python 3 and install the packages that I need one at a time. This gives me a cleaner install and I’m on the most current version of Python and the packages. I had installed Canopy for the two edX classes that I took so I wasn’t motivated to uninstall it on my old laptop. I use PythonDBAGraphs just about every day for my work and I didn’t want to whack it by uninstalling Canopy on my old laptop and trying to install something new. But, now I have my old laptop as a backup so I was able to take the time to install Python 3 and the current packages. I have updated the README with details of my new configuration.

I had to make a number of changes in the code. I had to change <> to !=. I had to change print xyz to print(xyz). Also, I think I ran across a bug in the new version of cx_Oracle. Here is an example. Assume you have an open connection and cursor. The cursor is cur.

cur.execute('select to_char(blocks/7) from user_tables')
row = cur.fetchone()
print(row)

cur.execute('select blocks/7 from user_tables')
row = cur.fetchone()
print(row)

Here is the output:

('2.28571428571428571428571428571428571429',)
Traceback (most recent call last):
  File "bug.py", line 12, in 
    row = cur.fetchone()
ValueError: invalid literal for int() with base 10: '2.28571428571428571428571428571428571429'

Strange. The workaround is to take any numbers with fractional values and run to_char() on them. I made a number of changes in the code to work around this bug/feature.

Anyway, I’m glad that I moved to Python 3 and that it enables me to use pip to install just the packages I want. Hopefully this update will make PythonDBAGraphs more usable for others since not everyone has Canopy.

Bobby

Categories: DBA Blogs

How to Secure our Oracle Databases

Learn DB Concepts with me... - Fri, 2017-06-02 13:27

How Secure can we make our Oracle Databases??

This is a routine question that runs in minds of most database administrators.  
HOW SECURE ARE OUR DATABASES. CAN WE MAKE IT ANYMORE SECURE.

I am writing this post to share my experience and knowledge on securing databases. I personally follow below tips to secure my databases:


 1. Make sure we only grant access to those users that really need to access database.
2. Remove all the unnecessary grants/privileges from users/roles.
3. Frequently audit database users Failed Logins in order to verify who is trying to login and their actions.
4. If a user is requesting elevated privileges, make sure you talk to them and understand their requirements.
5. Grant no more access than what needed.
6. At times users might need access temporarily. Make sure these temporary access are revoked after tasks are completed.
7. Define a fine boundary on who can access what??
8. Use User profiles / Audit to ensure all activities are tracked.
9.  Enforce complex password. Here is the Link on how to do it 
10 Use Triggers to track user activity.
11. Make sure passwords are encrypted in applications, this can be potential threat if you application code has been compromised.
12. Add password to your listener.
13. Allow access only from needed/known servers/clients. Use Valid_node_checking Link on how to restrict access to servers/clients.







Categories: DBA Blogs

Limit to size of csv file download from Interactive Report

Tom Kyte - Fri, 2017-06-02 02:06
Hello Tom, Firstly, I tried to google it a lot to check on if there is a limit to number of rows that can be exported to csv file from APEX Interactive Report but couldn't find one. So here is my issue. I have an interactive report with around ...
Categories: DBA Blogs

Select row count with multiple conditions and having 10 million rows

Tom Kyte - Fri, 2017-06-02 02:06
Hi Tom, I have a table and having 10+ million rows. I am getting the count of the table with specific conditions and execution takes 10 secs. I have used "SELECT COUNT(1)" statement and "MAX(ROWNUM)" statement. both statements are taking time. could ...
Categories: DBA Blogs

alert log

Tom Kyte - Fri, 2017-06-02 02:06
As i have seen everyday in alert.log of database it is showing like this - online index (re)build cleanup: objn=128483 maxretry=2000 forever=0 Can you please let me know why it is showing ? Thanks
Categories: DBA Blogs

update query in oracle when sga size is small and table data is huge

Tom Kyte - Fri, 2017-06-02 02:06
Hi , My sga size is 5 gb and my table data is 20 gb , when i run update statement on table how it will fit in sga and how oracle work . Please explain ? Thanks, RP.
Categories: DBA Blogs

Read CSV from APEX Server and insert into Staging table

Tom Kyte - Fri, 2017-06-02 02:06
Hi Tom, I Have csv file on APEX server.I need to access file from server and read the records one by one and load into staging table through PLSQL coding. Could you please provide the solution. Thanks.
Categories: DBA Blogs

Restore and Recover with no backup noarchivelog mode

Tom Kyte - Fri, 2017-06-02 02:06
Hi team, I have a database which is in no archivelog mode and NO Backup like rman and cold backup are available. Only logical Backup (export schema) is available. And suppose one datafile is corrupted or dropped like rm then how can i restore and...
Categories: DBA Blogs

Is there any mechanism to Store list of values in an apex global variable/ or can we declare some global pl/sql variables.

Tom Kyte - Fri, 2017-06-02 02:06
Hi Tom, I have a requirement where i am using some control parameters in my application (pair of key values) which needs to be stored in an apex global variable so that I can access this values from any where in the application. Is there any...
Categories: DBA Blogs

Debugging Oracle JET Applications

Oracle JET applications are pure client side Javascript and HTML5 applications. So for debugging you can use your favorite browser's capabilities. Oracle JET libraries come in two favors. One...

We share our skills to maximize your revenue!
Categories: DBA Blogs

ROLLUP OR CUBE

Tom Kyte - Thu, 2017-06-01 07:46
Hi Tom Thanks for your continuous help to Oracle WORLD. I have table dept like SQL> Select * from test_dept; DEPTCODE DEPTNAME AMOUNT --------- --------- -------- 100 SALES 100 100 SALES 120 ...
Categories: DBA Blogs

How to Update Columns from Other Tables

Tom Kyte - Thu, 2017-06-01 07:46
Hi, I have a requirement to update a table with new values where are available in other tables. Table 1 contain Old_name New_name Columns Table 2 contain New_Name column Table 3 contain Name Column my requirement is check the Name columns value...
Categories: DBA Blogs

EXECUTE IMMEDIATE with multiple bind variables and SELECT INTO returning a single record but multiple columns

Tom Kyte - Thu, 2017-06-01 07:46
Hello, I'm building a function that always receives 3 input values. These incoming values are used as bind variables in assembling a SQL query string that is later executed using an EXECUTE IMMEDIATE statement. The SQL query string contains a S...
Categories: DBA Blogs

It is possible to declare/create an UDT type inside of a package?

Tom Kyte - Thu, 2017-06-01 07:46
Hello everyone. I came here with one simple question. It is possible create an UDT inside of a package? For example, I create a new package CREATE OR REPLACE PACKAGE MyPackage AS TYPE myType AS OBJECT( NAME VARCHAR(30), SURNAME VARCHAR(...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs