Hot answers tagged

8

You need to terminate the SQL statement with a semicolon (;) or a put a slash (/) in the new line instead of hitting Enter. For example: select * from dual;. Otherwise SQLPlus will believe you have not finished your SQL statement, and it starts counting the lines. 2 is actually a line number, it is the 2nd line of your SQL statement. If you type nothing here ...


6

If you have a simple b-tree index on mycolumn, then yes, you would need to avoid calling functions on that column in order to be able to use the index to filter rows. In this case, it would seem to make much more sense to convert your numeric literals to timestamps than to do the reverse SELECT * FROM MYTABLE WHERE my_column > to_timestamp( ...


5

When you add a column that is nullable and don't give a default value, the database doesn't have to do anything on the table data itself. The rows are unchanged, only the table metadata is altered. (This is due to the on-disk row format used. Null columns at the end of a row are not stored in the row pieces, in general/under normal circumstances/for ...


5

Fullname etc. are not declared as unique in the parent table, and therefore you can't reference them from the child table. Why do you need to duplicate these columns in the child table? EDIT: Locking at you child table you say that: create table bit_2015_sep_cit4114_fyp_G_ ( ... , Full_Name VARCHAR2 (50) NOT NULL REFERENCES ...


4

Use quotation marks: CREATE DATABASE LINK my_link CONNECT TO daniel identified by "*password" USING 'hostname:port/servicename';


4

Based on your description of this behaviour and your other comment, I guess your database runs on Windows. The command you used should suffice and delete the datafiles. Except on Windows. On Windows, the datafiles are not always deleted, it is a known limitation. Before dropping tablespaces or datafiles, take them offline: alter tablespace ... offline; ...


4

First of all, NOT IN is not the same as NOT EXISTS. create table customer (customerid number); create table purchasebill (customerid number, retailoutletid number); create table retailoutlet (retailoutletid number); insert into customer values (1); insert into customer values (2); insert into purchasebill values (1, 1); insert into purchasebill values ...


4

ORA-00913 too many values Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT. Action: ...


3

All the comments above are appropriate. If you provide the details on the data types and any not null constraints of the columns in question, then we can better determine whether your function-based approach is appropriate. I would also gather statistics on the indexes and look at the unique values, etc. You can compress your baseline index too, if say ...


3

The problem is in the NLS_NCHAR_CHARACTERSET = UTF8. I simulated the same table in a different database with NLS_NCHAR_CHARACTERSET = AL16UTF16. The describe statement shows the correct length as defined while creating table. UTF8 can be 1, 2, 3 or 6 bytes / character, any AL32UTF8 character that is 4 bytes will be stored as 2 time 3 bytes in UTF8. If you ...


3

Indexing Expressions I believe that the solution you are looking for pertains to building an index on an expression, rather than on the original data itself. For outside references, you can consult Wikipedia or the Oracle documentation, where the link here contains more info and subsequent links in the 'Function-Based Indexes' section. There are associated ...


3

A DMP file is just a copy of the whole database contents. During the Import, it does not get modified, so you can use that file to "jump back" to the time when that dump file was created as often as you want.


3

You can simply do it in PL/SQL in a loop: begin for c in (select distinct profile from dba_profiles) loop execute immediate 'alter profile ' || c.profile || ' limit password_life_time unlimited'; end loop; end; /


3

As the documentation describes: The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2. So unless you pass the column as RAW, the result will be VARCHAR2, and that is not good enough, because that means data loss during conversion. LISTAGG works with VARCHAR2 or RAW, so to simply put it, you need to convert ...


3

Columns referenced by a foreign key constraint should have a PK or unique constraint in the referenced table. This is not true for Full_Name, Nationality, Fingerprint_Template. To be honest, those FK constraints are unnecessary, remove them, and keep the FK constraint on Student_ID. Also: "23/10/2015" VARCHAR2 (15) not null, This is not a valid column ...


3

Oracle SQL Developer ships with a report to find these unindexed columns. Here's the SQL behind this report: select a.owner "Owner", a.table_name "Table_Name", a.constraint_name "Constraint_Name", a.columns "Foreign_Key_Column_1", ...


3

You can just easily loop through the partitions in a PL/SQL loop, and add the subpartitions: begin for p in (select partition_name from user_tab_partitions where table_name = 'PART_TEST') loop execute immediate 'alter table part_test modify partition ' || p.partition_name || ' add subpartition ' || p.partition_name || 'AXCS values (''AXCS'')'; ...


3

The columns ABSENT, LATE and PRESENT in your output represent totals per student, whereas the date columns represent details per student, and details are the pivoted columns. In order to get such output with PIVOT, your source must provide totals on the same row with details. The source in your query is not providing that kind of information. It is grouping ...


2

If you change file's permissions during copy operation, tar will throw "file changes as we read it".


2

Bug 11872813 - V$PGA_TARGET_ADVICE VIEW IS EMPTY. After setting PGA_AGGREGATE_TARGET > approximately 12G the V$PGA_TARGET_ADVICE view is empty. This issue will be fixed in version 12.1 and a fix is expected to be included in patch set 11.2.0.4. Apply Patch 11872813


2

Try creating a temporary table to hold all of the IDs you want to search on then join in that temporary table. create table tmpIds(id int not null) insert into tmpIds (59156) insert into tmpIds (57045) etc... SELECT COUNT(*) FROM "VERSION" INNER JOIN tmpIds ON "VERSION"."VERSION_ID" = tmpIds.id drop table tmpIds


2

The problem is most likely in you not having sufficient privileges to create the index in question -- nothing to do with EXPLAIN PLAN itself. To quote the manual, "To create an index in another schema, you must have the CREATE ANY INDEX system privilege".


2

The nice clean solution is to: install Oracle 11.2.0.3 on a new machine with the same machine name depending on your network putting the new one a different sub-net or in a fire walled zone could prevent network issues create an instance with the same name set the parameters and table spaces to be the same as the original import the data test, test, test ...


2

I'm quite sure that this is a problem of SQL Developer... Retry to desc table after issuing: alter session set NLS_LENGTH_SEMANTICS='CHAR'; Is correct now? If not try to desc the table using sqlplus on the database server bypassing the Oracle Listener: >export NLS_LANG=AMERICAN_SWITZERLAND.AL32UTF8 >export ORACLE_SID=<YOURINSTANCESID> ...


2

Either create the table manually beforehand, or specify the column names an NULLability in the CTAS statement: create table blah2 ( ctascolumn1 not null, ctascolumn2 null ) as select col1, col2 from blah;


2

Thats is not what you should execute. sma is the procedure, c4 is simply a cursor in it. You do not execute the cursor local to a procedure, you can not even access it. Execute the below: set serveroutput on begin sma; end; /


2

According to the output of the command ps -ef|grep pmon which has shows that the instance name of the standby database is teststan. [oravis@standbysrv ~]$ ps -ef|grep pmon oravis 2749 1 0 16:02 ? 00:00:00 ora_pmon_teststan oravis 2825 2697 0 16:03 pts/0 00:00:00 grep pmon But in the description of listener on standby database, SID_NAME is defined as ...


2

It's not clear what you mean by "extended period of time" or why you associate deleting rows with "taking down" the system. If it's acceptable (as a matter of policy) to delete rows while the system is in production use, provided the deletions don't negatively affect performance, then the trick is to nibble away at the data. Take some unit of the data -- ...


2

Sure you can, you can duplicate a database using RMAN backups. For example: shut down DB2 delete all datafiles, redo logs, controlfiles of DB2 start DB2 in NOMOUNT with RMAN, connect to DB1 as target, DB2 as auxiliary: rman target sys/password@DB1 auxiliary / duplicate the database until the required time, and rename files on the fly (if required), for ...


2

The error occurred because you are referencing a column which is not unique. The good answers are already given by Lennart and Balazs Papp. I want to add why we need unique column in the parent table. As you said you want keep duplicate values in the column used for foreign key which is not possible while table creation. But you can create reference to the ...



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