Tag Info

Hot answers tagged

4

From the 12c docs: The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as a SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. Oracle Enterprise Manager Database Control does not permit you ...


3

You have essentially no guarantees about the sequence of ROWIDs. From the ROWID Pseudocolumn docs: If you delete a row, then Oracle may reassign its rowid to a new row inserted later. So the delete scenario has a potential for not being sequential. The ROWID encodes a relative file number and block number. There is no guarantee that these will ...


3

The correct answer for licensing questions is: contact your oracle sales representative. With Oracle you license cpu's. If your test and prod run on the same cpu's, they don't need a new license. As long as it all runs on the same cpu's, it does not matter if you have multiple databases (and instances) or run it all in one single database. Normally we don't ...


3

Short version: no. Active Data Guard is an optional feature that allows you to open the standby database in read-only mode while applying logs from the primary. If you need the standby database only in case of disaster, then you don't need to buy this option.


3

I guess, you can use parallel operations: Parallel DML Let your table support parallel operations: ALTER TABLE EFF parallel; Then you perform all your steps as you describe in the question. Parallel DDL + CTAS CREATE TABLE temp_table_EFF PARALLEL AS SELECT MOD, MP, PROG, TO_NUMBER(RNK), EFF_PART FROM EFF Then you drop your table: DROP TABLE EFF; ...


3

You can try the dump function to get the actual byte values stored. For example, I just created a table "OOUPS ", with a space at the end. > select table_name, dump(table_name) from user_tables; ... OOUPS Typ=1 Len=6: 79,79,85,80,83,32 ... You can see 32 (0x20 hex) as the last char - that's an ASCII space. Once you've found what the extra character ...


3

This sounds like the usual problem with stored procedures: when they are compiled by developer A in his schema, the compiler only considers the privileges DIRECTLY granted to A - as if the roles were all stripped off before compiling. This is often addressed by writing your procedure as AUTHID CURRENT_USER, but if the procedure calls another procedure ...


3

I suggest you don't set the environment variables in .profile. Instead use the oraenv utility to change the environment to suite the instance you want to connect to.


2

Use this on Windows (three nested double quotes): exp system/password_for_system@dev file=c:\fooadmin.dmp full=yes imp """sys/password12345@dev as sysdba""" FROMUSER=FOOADMIN TOUSER=FOOADMIN file=c:\fooadmin.dmp In general imp would work as intended with sys account, it is just not recommended. But exp with sys account could possibly produce inconsistent ...


2

It means that possibly some other DBA issued the command below (alter system with optional comment clause). Then the comment is put into spfile this way. But as I read metalink note: 257643.1 it might some outcome of some changes in automatic memory management. See alter system command: alter system set sga_max_size=2G scope=spfile sid=* ...


2

The Oracle docs for 11.2 ASM migration are here: http://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_rman.htm#OSTMG12000 These explain the process that you need to go through to migrate to ASM from filesystem storage.


2

No need to de-install Oracle rdbms software. Just install and configure ASM. If you have enough storage available you can copy your database to ASM using rman.


2

Technically, you could create a Java stored procedure or a dbms_scheduler external job that you invoke from your APEX process. Doing so, however, would almost certainly be a mistake. First, you'd have the issue of concurrency. If you did invoke your Python script from within your APEX process, for example, that process would start immediately and would ...


2

I certainly wouldn't want to include the server name in the TNS alias. It would seem highly probably that this would change over time as databases move from one server to another and as organizations move to things like RAC where there would be multiple servers. Assuming that your service names are chosen meaningfully, I would expect that the service name ...


2

What is the most costly thing for you? Prioritize. Usually the most costly thing is confusion of users. Ordinary users very rarely use a TNS name. They usually use the data through a dedicated application, and know just the name of application, like CRM. Power users connect to TNS names (think SQLdeveloper), but they also connect to the ordinary ...


2

Seems like there are some Database Resource Manager (DRM) constraints put on user sessions when the database switches to maintenance plan via Scheduler window each night (as evident by your log portion). There are at least two predefined DRM plans when a database is created. DEFAULT_PLAN DEFAULT_MAINTENANCE_PLAN (See the complete list.) When a window ...


2

WHERE SYSDATE < to_date( LD.DEADLINE, 'Month DD, YYYY' ); would work assuming all your data is perfect and that all your session are configured to use English month names. I would strongly recommend, however, that you fix the data model. Storing strings that represent dates rather than actual dates is a terrible practice. Inevitably, you end up with ...


2

Views starting with VW_DTP showing up in the execution plan is the result of one of the cost-based query transformations available since 11.2, called distinct placement. It happens with statements using the DISTINCT clause, just like yours. It is an absolutely acceptable an quite obvious execution plan to join the tables and filter the rows, and after that, ...


2

It's hard to guess what's going on on your system without code snipets to evaluate, but it seems you're not using the fully qualified table name [schema name].[table name]. If you don't want to use the fully qualified table name, you may create PUBLIC SYNONYMS. Ex from Oracle documentation: CREATE PUBLIC SYNONYM offices FOR hr.offices; Having said ...


1

My sequence of steps for troubleshooting Is the listener resolvable? C:\>tnsping MYDB (Confirms: the database listener is running on serverside) Is there a firewall issue? C:\>telnet hostname 1521 Can I login? C:\>sqlplus system/pass@MYDB or C:\>sqlplus system/pass@hostname:1521/MYDB (Confirms: The database is open) Finally, chances of having ...


1

You can create an AFTER SERVERERROR trigger and log the relevant information to a table. Here is an example: http://www.adp-gmbh.ch/ora/sql/trigger/servererror.html


1

No. It would be true if all the rows were of exact the same size in single user mode. Imagine situation when row wont' fit into particular block because it does not fit in it. So this row will be put into the "next" block. Then you insert some smaller row, this one will be put into the previous block. In most cases when doing full table scans the rows are ...


1

Assuming that we are talking about dbms_scheduler jobs (rather than, say, jobs scheduled using the dbms_job package), you can use the dbms_metadata package to generate the DDL. Specifically, the dbms_metadata.get_ddl function. Something like SELECT dbms_metadata.get_ddl( 'PROCOBJ', <<job name>>, <<schema>> ) FROM dual should ...


1

Store the tenant_id first. When you do this you can enable index key compression. See http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1106790 for the syntax and http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#i14618 for the concepts. In your case, you can do it like this: create unique index mytable_idx on ...


1

When you use the values clause in Oracle, you can only provide comma-separated values. If you want to select one or more rows from another table, you have to use this syntax: insert into <table>(<col1>,<col2>,...,<coln>) select <col1>,<col2>,...,<coln> from ...; In your case: insert into MEMBERS(GR_id, username) ...


1

One thing you need to keep in mind is what you are using PK for. At the logical level (sometimes called the conceptual level), the primary key is simply one of the candidate keys, chosen somewhat arbitrarily. The purpose is to guarantee that each row is unique, that each row has an identifier, and that no part of an identifier is left out (NULL). For ...


1

I will elaborate on the @Jeffrey Kemp's comment. Since the information about authors of modifications is not collected in the objects' metadata, you will want to use database auditing mechanisms. As per Verifying Security Access with Auditing chapter of Database Security Guide, there are several types of auditing, but Standard Auditing is probably the ...


1

I couldn't find any clause to the REPORT or LIST commands which would allow you to specify the incarnation of interest. You could however correlate your latest incarnation reset time with backup completion time to identify the backups completed before your current incarnation began. First, let's set the environment variables so that RMAN output also ...


1

You will need to change the SID on Oracle. At my job I solved this for Linux/UNIX OS, but it's very similar on Windows machines too. Try doing this: SQL> create pfile from spfile; SQL> shutdown immediate; [oracle@server ~]$ lsnrctl stop [oracle@server ~]$ cp ORACLE_HOME/dbs/spfileXE.ora ORACLE_HOME/dbs/spfileTEST.ora [oracle@server ~]$ orapwd ...


1

Generally, aborting database queries and DML is not a good idea, and as @Raj already pointed out, killing sessions is also bad idea – actually it's even worse. Your sessions may be running some transactions which consume undo space (in the undo tablespace of the user), hold locks on tables and occupy space (e.g. for row sorting) in the temporary tablespace. ...



Only top voted, non community-wiki answers of a minimum length are eligible