sqldeveloper 4.2 is there : http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
Until 11.2, audit_file_dest used to remain small with default settings and reasonably sized and active database. Suddenly, in 12c, you will sooned or later get ORA-09925: Unable to create audit trail file.
At that point, no more connection is possible to the database, it is a complete loss of service.
Why suddenly in 12c ? This is because the default for audit_sys_operations changed to true. In 11g, you used to get an 1K file each time you connect as sysdba. So a few tousands sysdba connections a weeks, a few mega, no worries.
Mon Mar 27 14:08:01 2017 +02:00 LENGTH : '155' ACTION : 'CONNECT' DATABASE USER: '/' PRIVILEGE : 'SYSDBA' CLIENT USER: 'oracle' CLIENT TERMINAL: '' STATUS: '0'
Suddenly in 12c, you get plenty files that are many Mb. For instance for AUTOTASK jobs, every single select is dumped to the filesystem. A single week-end of an quiet database may generate 1Gb of *.aud files of DBMS_SCHEDULER.
Those DB001_j000_12345_20170327140802123456789.aud files are highly useless and annoying.
LENGTH : '641' ACTION : 'select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */ substrb(dump("PERIOD_END_TIME",16,0 ,64),1,240) val, rowidtochar(rowid) rwid from "SYS"."WRP$_REPORTS_TIME_BANDS" t where rowid in (chartorowid('AABJ58AADAAAMsrAAA'),chartorowid('AABJ58AADAAAMsrAAB'),chartorowid('AABJ58AADAAAMsrAAC'),chartorowid('AABJ58A ADAAAMssAAA')) order by "PERIOD_END_TIME"' DATABASE USER: 'SYS' PRIVILEGE : 'NONE' CLIENT USER: '' CLIENT TERMINAL: 'UNKNOWN' STATUS: '0'
Once your audit_file_dest is getting full, your database stops, so better delete those *_j00*_* and *_m00*_* quickly enough!
@laurentsch Or perhaps just use scheduler to run them, job_type = SQL_SCRIPT
— Connor McDonald (@connor_mc_d) December 21, 2016
If you are still considering the 11g approach with sqlplus, you should read about restricted sqlplus
If you run
sqlplus -L -s -R 3 scott/tiger@db01
lot’s of OS-command are disabled
SQL> get /etc/passwd SP2-0738: Restricted command "get" not available SQL> spool /etc/oratab SP2-0738: Restricted command "spool" not available SQL> host reboot SP2-0738: Restricted command "host" not available
There is also a Product User Profile. I never saw any customer using this. I doubt it is very safe.
You could disable some commands
SQL> insert into system.PRODUCT_USER_PROFILE (PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE) values ('SQL*Plus','SCOTT','ROLLBACK','DISABLED');
Which prevents SCOTT from doing rollabck
SQL> rollback; SP2-0544: Command "rollback" disabled in Product User Profile
but it doesn’t resist Rock & Roll
SQL> roll & rock Rollback complete.
If you have 12c, go have a look at the doc for SQL_SCRIPT jobs
The doc docs.oracle.com/database/122 is available. Check new features and stay tuned.
Okay, just 12 for today : HIGH compressed index, case insensitive database with bound collation, partitioned external tables, AL32UTF8 default for new database, listagg overflow, VALIDATE_CONVERSION, approx_percentile, json_exists, flashback pluggable database, the SHARING clause, RAC streched clusters site definition and INACTIVE_ACCOUNT_TIME in profile.
Some I like more than others 🙂
Okay, one more : SQLPLUS HISTORY !
If you delete most of the rows in one table, you probably want to regain that space, to minimize IO and fragmentation.
If you delete most of the rows in most of the tables, you probably also want to resize the datafile to reduce storage and backup space.
In this case, you could move your data in a new tablespace
alter table t move tablespace newts;
I wrote about this here : 2006/08/tablespace-maintenance-tasks.html
There is also an option to SHRINK. In oldish (pre-12cR2) releases, this had the advantage of being an online operation. In 12.2, include the online keyword.
SHRINK requires ROW MOVEMENT. I don’t like the idea of having the rowid’s changing. You have also a bunch of restrictions, amoung others on materialized view fast refreshes and index organized tables.
I am impatient to get this 12cR2. According to the doc, during an alter table move, transactions run uninterrupted on the table
No, I am not talking about DBMS_REDEFINITION, where you get a kind of online feeling.
No, I don’t want to rename my table, rename my foreign keys, my primary key, my not-null-constraints, recreate my referential integrity, recompile my triggers.
I just want to partition a non-partitioned table.
ALTER TABLE EMP MODIFY PARTITION BY RANGE(HIREDATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) (PARTITION P1 VALUES LESS THAN (DATE '1980-01-01')) ONLINE;
This is going to save me a lot of Saturday work 🙂
You need 12.2 to run this.
In the past, most of my customers skipped R1 releases. That is, 8.1.7 -> 9.2 -> 10.2 -> 11.2. SAP does the same. For the very first time SAP plans to go to 22.214.171.124 + some PSU in spring 2015. But only to avoid running out of support and without any fancy feature like Multitenant or in Memory.
#oracle 126.96.36.199 is the last patch set for Release 12.1.
— laurentsch (@laurentsch) October 9, 2014
188.8.131.52, which is not available on AIX yet, will be the last patchset of 12cR1. It is the first and only patchset for that release. It is actually more than a patchset, as it introduced in memory database and JSON in the database.
The next release is expected beginning of 2016 on Linux. 184.108.40.206 patching ends January 2018.
Should I I go to an already aborted release or should I buy extended support for 220.127.116.11 until 2018 ?
Probably I will go both ways, depending on the applications.
@mkratoch is speaking at 10am UGF2633: Managing Oracle 12c on Windows
Again, 12cR1ps1 came before AIX and HPUX.
SQL> select dbms_utility.port_string, version from v$instance; PORT_STRING VERSION -------------------- ----------------- IBMPC/WIN_NT64-9.1.0 18.104.22.168.0